Oracle SQL 'or' 的优化，最近的案例一则。

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的目标达成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10856805/viewspace-1007639/，如需转载，请注明出处，否则将追究法律责任。