数据库优化—Sort Operation

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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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/20945761/viewspace-558013/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20945761/viewspace-558013/