一个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:59 2 FROM SCARTON,ISN WHERE SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1 AND SCARTON.SCARNO=ISN.SCARNO;

192

Elapsed: 00:00:00.93

Execution Plan

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

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

1 0 SORT (GROUP BY)

2 1 NESTED LOOPS (Cost=67 Card=6 Bytes=204)

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

d=6 Bytes=174)

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

3 Card=6)

5 2 PARTITION RANGE (ALL)

6 5 PARTITION HASH (ALL)

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

Card=1 Bytes=5)

Statistics

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

0 recursive calls

0 db block gets

21593 consistent gets

99 physical reads

0 redo size

522 bytes sent via SQL*Net to client

656 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

优化后的SQL：

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

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

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

192

Elapsed: 00:00:00.08

Execution Plan

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

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

1 0 SORT (AGGREGATE)

2 1 FILTER

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

Card=6 Bytes=72)

4 2 NESTED LOOPS (Cost=27469280 Card=5505130 Bytes=1211128

60)

5 4 PARTITION RANGE (ALL)

6 5 PARTITION HASH (ALL)

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

4330 Card=27444950 Bytes=137224750)

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

ard=1 Bytes=17)

Statistics

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

0 recursive calls

0 db block gets

20 consistent gets

2 physical reads

0 redo size

532 bytes sent via SQL*Net to client

656 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

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

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

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