Oracle學習系列—資料庫優化—Sort Operation

bq_wang發表於2007-04-19
Oracle資料庫優化
雖然知道其適用場合,但是確實不太清楚對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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章