Oracle学习系列—数据库优化—Sort Operation

虽然知道其适用场合,但是确实不太清楚对COST的影响...

Ø SORT UNIQUE

假如用户指定Distinct语法或者下一步操作需要唯一值,将会导致Sort Unique

Ø SORT AGGREGATE

Sort Aggregate不一定涉及到排序,当聚合用来计算所有行值时,会使用到Sort Aggregates

Ø SORT GROUP BY

当聚合用来计算不同组的数据时将会使用Sort Group By,排序需要将把行值分成不同的组

Ø SORT JOIN

假如行按照连接键排序,在排序合并连接时将会发生Sort Join

Ø SORT ORDER BY

指定一个不能满足索引列的排序将需要一个Sort Order By

Example

drop table testindex; create table testindex as select * from dba_objects; alter table testindex modify owner not null; alter table testindex modify object_name not null; alter table testindex modify object_type not null; create index testfullindex on testindex(owner,object_name,object_type); analyze table testindex compute statistics; analyze index testfullindex compute statistics;

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Apr 18 20:37:41 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect wbq/wbq Connected. SQL> set autotrace ? Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] SQL> set autotrace trace explain; SQL> select distinct owner from testindex; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=28 Bytes=140) 1 0 SORT (UNIQUE) (Cost=65 Card=28 Bytes=140) 2 1 INDEX (FAST FULL SCAN) OF 'TESTFULLINDEX' (NON-UNIQUE) (Cost=21 Card=29530 Bytes=147650) SQL> select sum(object_id) from testindex; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1 Bytes=4) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=42 Card=29530 Bytes=118120) SQL> select owner,sum(object_id) from testindex 2 group by owner; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=98 Card=28 Bytes=252) 1 0 SORT (GROUP BY) (Cost=98 Card=28 Bytes=252) 2 1 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=42 Card=29530 Bytes=265770) SQL> select sum(a.object_id),b.created from testindex a,testindex b 2 where a.owner<>b.owner and a.object_name 3 group by b.created; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3891962 Card=2752 Bytes=184384) 1 0 SORT (GROUP BY) (Cost=3891962 Card=2752 Bytes=184384) 2 1 MERGE JOIN (Cost=516 Card=42043865 Bytes=2816938955) 3 2 SORT (JOIN) (Cost=251 Card=29530 Bytes=944960) 4 3 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=42 Card=29530 Bytes=944960) 5 2 FILTER 6 5 SORT (JOIN) 7 6 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=42 Card=29530 Bytes=1033550) SQL> select * from testindex 2 order by created; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=794 Card=29530 Bytes=2510050) 1 0 SORT (ORDER BY) (Cost=794 Card=29530 Bytes=2510050) 2 1 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=42 Card=29530 Bytes=2510050)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6517/viewspace-145524/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-145524/