oracle优化distinct,一个Distinct的优化

一个Distinct的优化。[@more@]

一个Distinct的优化。

ISN有3000万笔，SCARTON有600万笔

原SQL：

16:07:02 SQL> set autotrace on;

16:07:19 SQL> SELECT COUNT(DISTINCT(SCARTON.SCARNO)) MAX_SCARTON

16:07:592FROM SCARTON,ISN WHERE SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1 AND SCARTON.SCARNO=ISN.SCARNO;

192

Elapsed: 00:00:00.93

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=1 Bytes=34)

10SORT (GROUP BY)

21NESTED LOOPS (Cost=67 Card=6 Bytes=204)

32TABLE ACCESS (BY INDEX ROWID) OF 'SCARTON' (Cost=7 Car

d=6 Bytes=174)

43INDEX (RANGE SCAN) OF 'SCARTON2' (NON-UNIQUE) (Cost=

3 Card=6)

52PARTITION RANGE (ALL)

65PARTITION HASH (ALL)

76INDEX (RANGE SCAN) OF 'ISN7' (NON-UNIQUE) (Cost=10

Card=1 Bytes=5)

Statistics

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

0recursive calls

0db block gets

21593consistent gets

99physical reads

0redo size

522bytes sent via SQL*Net to client

656bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

1rows processed

优化后的SQL：

16:08:02 SQL> SELECT COUNT(SCARTON.SCARNO)

16:09:222FROM SCARTON WHERE exists (select 1 from ISN,SCARTON where SCARTON.SCARNO=ISN.SCARNO and SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1)

16:09:223and SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1;

192

Elapsed: 00:00:00.08

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=12)

10SORT (AGGREGATE)

21FILTER

32INDEX (RANGE SCAN) OF 'SCARTON2' (NON-UNIQUE) (Cost=3

Card=6 Bytes=72)

42NESTED LOOPS (Cost=27469280 Card=5505130 Bytes=1211128

60)

54PARTITION RANGE (ALL)

65PARTITION HASH (ALL)

76INDEX (FULL SCAN) OF 'ISN7' (NON-UNIQUE) (Cost=2

4330 Card=27444950 Bytes=137224750)

84INDEX (UNIQUE SCAN) OF 'SCARTON1' (UNIQUE) (Cost=1 C

ard=1 Bytes=17)

Statistics

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

0recursive calls

0db block gets

20consistent gets

2physical reads

0redo size

532bytes sent via SQL*Net to client

656bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

仅仅这个SQL的改动减少了Production DB约20%的总buffer gets.

而且发现CBO在估计partition table的cost颇有失水准…

改动通过多次不同值static sql和Bind Variable下的稳定性测试。