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'

带函数的执行计划DiscriptionCostCardinalityBytesCpu CostSELECT STATEMENT, GOAL = ALL_ROWS18148142972 SORT AGGREGATE148  CONCATENATION   NESTED LOOPS SEMI914869961    NESTED LOOPS613846819     TABLE ACCESS BY INDEX ROWID412929636      INDEX RANGE SCAN3121764     TABLE ACCESS BY INDEX ROWID21917183      INDEX UNIQUE SCAN119021    TABLE ACCESS BY INDEX ROWID3771549771549023142     INDEX RANGE SCAN2115493   NESTED LOOPS SEMI914873011    NESTED LOOPS613849869     TABLE ACCESS BY INDEX ROWID412932686      INDEX RANGE SCAN3124814     TABLE ACCESS BY INDEX ROWID21917183      INDEX UNIQUE SCAN119021    TABLE ACCESS BY INDEX ROWID3771549771549023142     INDEX RANGE SCAN2115493

优化思路：在程序中优先执行函数，将结果存储在另外一个变量中，然后将变量写入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'

不带函数时的执行计划DiscriptionCostCardinalityBytesCpu CostSELECT STATEMENT, GOAL = ALL_ROWS1114899047 SORT AGGREGATE148  NESTED LOOPS1114899047   NESTED LOOPS1114899047    NESTED LOOPS813875906     INLIST ITERATOR      TABLE ACCESS BY INDEX ROWID612958723       INDEX RANGE SCAN4243329     TABLE ACCESS BY INDEX ROWID21917183      INDEX UNIQUE SCAN119021    INDEX RANGE SCAN2115493   TABLE ACCESS BY INDEX ROWID311023142

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