Oracle的级联查询(CONCATENATION)

在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

通过以上两个计划对比，可见优化后各项指标均有下降，特别是数据获取量上大幅下降。