Oracle SQL 'or' 的最佳化，最近的案例一則。

Oracle 中or是可以用union/union all來作最佳化的[@more@]

SQL Tuning之 OR的最佳化。

今天公司某Production DB時常LOADING飈起來，Monitor下發現一個很high的SQL:

SELECT COUNT(A.ISN) FROM MO_ROUTE A,MO B WHERE (A.ROUTE = :B2 OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1' AND (A.MO LIKE 'NFPQ%' OR A.MO LIKE 'NF1Q%' OR A.MO LIKE 'NF6Q%') AND A.MO = B.MO(+) AND A.INTIME BETWEEN :B1 - 1 AND :B1 AND B.CDATE BETWEEN :B1 - 1 AND :B1

其PLAN如下：

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2195 Card=1 Bytes=49

)

1 0 SORT (AGGREGATE)

2 1 FILTER

3 2 NESTED LOOPS (Cost=2195 Card=1 Bytes=49)

4 3 PARTITION RANGE (ITERATOR)

5 4 PARTITION HASH (ALL)

6 5 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MO_ROUTE

' (Cost=2194 Card=1 Bytes=30)

7 6 INDEX (RANGE SCAN) OF 'MO_ROUTE11' (NON-UNIQUE

) (Cost=1242 Card=1741)

8 3 TABLE ACCESS (BY INDEX ROWID) OF 'MO' (Cost=1 Card=1

Bytes=19)

9 8 INDEX (UNIQUE SCAN) OF 'MO1' (UNIQUE)

Statistics

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

0 recursive calls

0 db block gets

39135 consistent gets

25654 physical reads

106180 redo size

521 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

很好，很強大的Gets，這可是OLTP環境。

MO_ROUTE是一個RANGE-HASH Partitioned Table，INTIME作為進行RANGE的Column.從PLAN可以看出，經過了Partition Pruning，再走MO_ROUTE11這個INDEX。 MO_ROUTE11是一個Complex Index，以Intime為首列（Intime,section,grp）。看起來選則的比較有道理。

然而這個Table即使是一天的資料量也很大。這個時侯注意到MO,MO的可選擇性應該也是比較強的。

+HINT看一下：

SELECT /*+INDEX(A MO_ROUTE2)*/COUNT(A.ISN) FROM MO_ROUTE A,MO B WHERE

(A.ROUTE = 'VNMB'OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1'

AND (A.MO LIKE 'NFPQ%' OR A.MO LIKE 'NF1Q%' OR A.MO LIKE 'NF6Q%')

AND A.MO = B.MO(+) AND A.INTIME BETWEEN sysdate - 1 AND sysdate

AND B.CDATE BETWEEN sysdate - 1 AND sysdate；

EXPLAN:

SELECT STATEMENT, GOAL = CHOOSE Cost=7414 Cardinality=1 Bytes=49

SORT AGGREGATE Cardinality=1 Bytes=49

CONCATENATION

FILTER

NESTED LOOPS Cost=85 Cardinality=1 Bytes=49

PARTITION RANGE ITERATOR

PARTITION HASH ALL

TABLE ACCESS BY LOCAL INDEX ROWID Object owner=TP Object name=MO_ROUTE Cost=84 Cardinality=1 Bytes=30

INDEX RANGE SCAN Object owner=TP Object name=MO_ROUTE2 Cost=66 Cardinality=17

TABLE ACCESS BY INDEX ROWID Object owner=TP Object name=MO Cost=1 Cardinality=1 Bytes=19

INDEX UNIQUE SCAN Object owner=TP Object name=MO1 Cardinality=1

FILTER

NESTED LOOPS Cost=85 Cardinality=1 Bytes=49

PARTITION RANGE ITERATOR

PARTITION HASH ALL

TABLE ACCESS BY LOCAL INDEX ROWID Object owner=TP Object name=MO_ROUTE Cost=84 Cardinality=1 Bytes=30

INDEX RANGE SCAN Object owner=TP Object name=MO_ROUTE2 Cost=66 Cardinality=17

TABLE ACCESS BY INDEX ROWID Object owner=TP Object name=MO Cost=1 Cardinality=1 Bytes=19

INDEX UNIQUE SCAN Object owner=TP Object name=MO1 Cardinality=1

FILTER

NESTED LOOPS Cost=85 Cardinality=1 Bytes=49

PARTITION RANGE ITERATOR

PARTITION HASH ALL

TABLE ACCESS BY LOCAL INDEX ROWID Object owner=TP Object name=MO_ROUTE Cost=84 Cardinality=1 Bytes=30

INDEX RANGE SCAN Object owner=TP Object name=MO_ROUTE2 Cost=66 Cardinality=17

TABLE ACCESS BY INDEX ROWID Object owner=TP Object name=MO Cost=1 Cardinality=1 Bytes=19

INDEX UNIQUE SCAN Object owner=TP Object name=MO1 Cardinality=1

COST變成7K多，而且實際過程中我只能把它CANCEL掉，否則Server Loading會一下子飆起來。

計劃裡只是到最後的總COST很高，單步的COST卻較小。

於是想到用Union All來替代OR:

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

select sum(c.n) from

(SELECT COUNT(A.ISN) n FROM MO_ROUTE A,MO B WHERE

(A.ROUTE = 'VNMB' OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1'

AND A.MO LIKE 'NFPQ%'

AND A.MO = B.MO(+) AND A.INTIME BETWEEN sysdate - 1 AND sysdate

AND B.CDATE BETWEEN sysdate - 1 AND sysdate

union all

SELECT COUNT(A.ISN) n FROM MO_ROUTE A,MO B WHERE

(A.ROUTE = 'VNMB' OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1'

AND A.MO LIKE 'NF1Q%'

AND A.MO = B.MO(+) AND A.INTIME BETWEEN sysdate - 1 AND sysdate

AND B.CDATE BETWEEN sysdate - 1 AND sysdate

union all

SELECT COUNT(A.ISN) n FROM MO_ROUTE A,MO B WHERE

(A.ROUTE = 'VNMB' OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1'

AND A.MO LIKE 'NF6Q%'

AND A.MO = B.MO(+) AND A.INTIME BETWEEN sysdate - 1 AND sysdate

AND B.CDATE BETWEEN sysdate - 1 AND sysdate) c

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=216 Card=1 Bytes=13)

1 0 SORT (AGGREGATE)

2 1 VIEW (Cost=216 Card=3 Bytes=39)

3 2 UNION-ALL

4 3 SORT (AGGREGATE)

5 4 FILTER

6 5 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MO_ROUTE

' (Cost=69 Card=1 Bytes=30)

7 6 NESTED LOOPS (Cost=72 Card=1 Bytes=49)

8 7 TABLE ACCESS (BY INDEX ROWID) OF 'MO' (Cost=

3 Card=1 Bytes=19)

9 8 INDEX (RANGE SCAN) OF 'MO1' (UNIQUE) (Cost

=2 Card=1)

10 7 PARTITION RANGE (ITERATOR)

11 10 PARTITION HASH (ALL)

12 11 INDEX (RANGE SCAN) OF 'MO_ROUTE2' (NON-U

NIQUE) (Cost=65 Card=3)

13 3 SORT (AGGREGATE)

14 13 FILTER

15 14 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MO_ROUTE

' (Cost=69 Card=1 Bytes=30)

16 15 NESTED LOOPS (Cost=72 Card=1 Bytes=49)

17 16 TABLE ACCESS (BY INDEX ROWID) OF 'MO' (Cost=

3 Card=1 Bytes=19)

18 17 INDEX (RANGE SCAN) OF 'MO1' (UNIQUE) (Cost

=2 Card=1)

19 16 PARTITION RANGE (ITERATOR)

20 19 PARTITION HASH (ALL)

21 20 INDEX (RANGE SCAN) OF 'MO_ROUTE2' (NON-U

NIQUE) (Cost=65 Card=3)

22 3 SORT (AGGREGATE)

23 22 FILTER

24 23 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MO_ROUTE

' (Cost=69 Card=1 Bytes=30)

25 24 NESTED LOOPS (Cost=72 Card=1 Bytes=49)

26 25 TABLE ACCESS (BY INDEX ROWID) OF 'MO' (Cost=

3 Card=1 Bytes=19)

27 26 INDEX (RANGE SCAN) OF 'MO1' (UNIQUE) (Cost

=2 Card=1)

28 25 PARTITION RANGE (ITERATOR)

29 28 PARTITION HASH (ALL)

30 29 INDEX (RANGE SCAN) OF 'MO_ROUTE2' (NON-U

NIQUE) (Cost=65 Card=3)

Statistics

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

0 recursive calls

0 db block gets

2592 consistent gets

49 physical reads

1336 redo size

517 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

COST降低到200多點，Buffer Gets和Physical reads大幅度減少。

Tuning的目標達成。