oracle数据库中sort,Oracle学习系列—数据库优化—Sort Operation

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

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=28 Bytes=140)

1 0SORT (UNIQUE) (Cost=65 Card=28 Bytes=140)

21INDEX (FAST FULL SCAN) OF

'TESTFULLINDEX' (NON-UNIQUE) (Cost=21 Card=29530 Bytes=147650)

SQL> select

sum(object_id) from testindex;

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1 Bytes=4)

10SORT (AGGREGATE)

21TABLE ACCESS (FULL) OF

'TESTINDEX' (Cost=42 Card=29530 Bytes=118120)

SQL> select

owner,sum(object_id) from testindex

2group by owner;

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=98 Card=28 Bytes=252)

10SORT (GROUP BY) (Cost=98

Card=28 Bytes=252)

21TABLE ACCESS (FULL) OF

'TESTINDEX' (Cost=42 Card=29530 Bytes=265770)

SQL> select

sum(a.object_id),b.created from testindex a,testindex b

2where a.owner<>b.owner and a.object_name

3group by b.created;

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=3891962 Card=2752

Bytes=184384)

10SORT (GROUP BY)

(Cost=3891962 Card=2752 Bytes=184384)

21MERGE JOIN (Cost=516

Card=42043865 Bytes=2816938955)

32SORT (JOIN) (Cost=251

Card=29530 Bytes=944960)

43TABLE ACCESS (FULL) OF

'TESTINDEX' (Cost=42 Card=29530 Bytes=944960)

52FILTER

65SORT (JOIN)

76TABLE ACCESS (FULL)

OF 'TESTINDEX' (Cost=42 Card=29530 Bytes=1033550)

SQL> select * from

testindex

2order by created;

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=794 Card=29530 Bytes=2510050)

1 0SORT (ORDER BY) (Cost=794 Card=29530 Bytes=2510050)

21TABLE ACCESS (FULL) OF

'TESTINDEX' (Cost=42 Card=29530 Bytes=2510050)