在where条件中使用OR或者函数,可能会导致Oracle选择CONCATENATION这种方式。
OR的分析在网上有一些文章,可以搜索查阅,本文不做讨论,仅针对使用函数的情况做分析。
SQL片段如下,涉及到保密机制,表名、字段名、函数名已替换,不影响阅读。
SELECT COUNT(1)
FROM T_A
WHERE EXISTS (SELECT 1
FROM T_B
WHERE B.C_1 = A.C_1
AND B.C_2 = :B4)
AND C_3 IN (:B2, F_1(:B2))
AND C_4 = :B1
AND C_5 = '1'
Discription | Cost | Cardinality | Bytes | Cpu Cost |
SELECT STATEMENT, GOAL = ALL_ROWS | 18 | 1 | 48 | 142972 |
SORT AGGREGATE | 1 | 48 | ||
CONCATENATION | ||||
NESTED LOOPS SEMI | 9 | 1 | 48 | 69961 |
NESTED LOOPS | 6 | 1 | 38 | 46819 |
TABLE ACCESS BY INDEX ROWID | 4 | 1 | 29 | 29636 |
INDEX RANGE SCAN | 3 | 1 | 21764 | |
TABLE ACCESS BY INDEX ROWID | 2 | 1 | 9 | 17183 |
INDEX UNIQUE SCAN | 1 | 1 | 9021 | |
TABLE ACCESS BY INDEX ROWID | 3 | 771549 | 7715490 | 23142 |
INDEX RANGE SCAN | 2 | 1 | 15493 | |
NESTED LOOPS SEMI | 9 | 1 | 48 | 73011 |
NESTED LOOPS | 6 | 1 | 38 | 49869 |
TABLE ACCESS BY INDEX ROWID | 4 | 1 | 29 | 32686 |
INDEX RANGE SCAN | 3 | 1 | 24814 | |
TABLE ACCESS BY INDEX ROWID | 2 | 1 | 9 | 17183 |
INDEX UNIQUE SCAN | 1 | 1 | 9021 | |
TABLE ACCESS BY INDEX ROWID | 3 | 771549 | 7715490 | 23142 |
INDEX RANGE SCAN | 2 | 1 | 15493 |
优化思路:在程序中优先执行函数,将结果存储在另外一个变量中,然后将变量写入SQL,则可避免执行计划走CONCATENATION
SELECT COUNT(1)
FROM T_A
WHERE EXISTS (SELECT 1
FROM T_B
WHERE B.C_1 = A.C_1
AND B.C_2 = :B4)
AND C_3 IN (:B2,:B3)
AND C_4 = :B1
AND C_5 = '1'
Discription | Cost | Cardinality | Bytes | Cpu Cost |
SELECT STATEMENT, GOAL = ALL_ROWS | 11 | 1 | 48 | 99047 |
SORT AGGREGATE | 1 | 48 | ||
NESTED LOOPS | 11 | 1 | 48 | 99047 |
NESTED LOOPS | 11 | 1 | 48 | 99047 |
NESTED LOOPS | 8 | 1 | 38 | 75906 |
INLIST ITERATOR | ||||
TABLE ACCESS BY INDEX ROWID | 6 | 1 | 29 | 58723 |
INDEX RANGE SCAN | 4 | 2 | 43329 | |
TABLE ACCESS BY INDEX ROWID | 2 | 1 | 9 | 17183 |
INDEX UNIQUE SCAN | 1 | 1 | 9021 | |
INDEX RANGE SCAN | 2 | 1 | 15493 | |
TABLE ACCESS BY INDEX ROWID | 3 | 1 | 10 | 23142 |
通过以上两个计划对比,可见优化后各项指标均有下降,特别是数据获取量上大幅下降。