第一章 适用范围

本案例中的问题现象发生于当前主流的ORACLE 11G环境。不排除随着后续版本的升级可能会有一定细微的差异表现。但问题的原因及解决方案，在所有版本中都是通用的。
数据库版本:ORACLE 11G

第二章 问题概述

问题SQL的主要现象是通过一个子查询，关联外部的两个或多个表。造成了异常低效的性能问题。

日常我们书写SQL，需要明确写出表之间或表/子查询间的关联条件。如果有关联子查询，代表子查询中的表与外部表有了一定的关联条件。数据库会对关联子查询应用一些查询转换技术，以提升SQL的查询表现。

而如果关联子查询除了子查询的关联条件外，又通过其他列与外部主查询的其他表还有连接条件。这是数据库就无法对这类特殊的关联子查询应用查询转换技术。这时就受到外部主查询的限制，限制了与子查询的连接访问方式。可能造成的现象就是低效的查询效率。

下面通过SQL案例进行说明：

SELECT AMOUNT.UNDER_ID,
       AMOUNT.INSUR_ID,
       SUM(AMOUNT.LIFE1_AMOUNT) AS LIFE1_AMOUNT,
       SUM(AMOUNT.LIFE2_AMOUNT) AS LIFE2_AMOUNT,
       SUM(AMOUNT.LIFE3_AMOUNT) AS LIFE3_AMOUNT,
       SUM(AMOUNT.A_AMOUNT) AS A_AMOUNT,
       SUM(AMOUNT.B_AMOUNT) AS B_AMOUNT,
       SUM(AMOUNT.C_AMOUNT) AS C_AMOUNT,
       SUM(AMOUNT.D_AMOUNT) AS D_AMOUNT
  FROM T_AMOUNT AMOUNT, T_LIFE TPL
 WHERE TPL.PRODUCT_ID IN
       (SELECT PRODUCT_ID
          FROM T_1
         WHERE ITEM_ID = AMOUNT.ITEM_ID
        UNION
        SELECT PRODUCT_ID
          FROM T_2
         WHERE ITEM_ID = AMOUNT.ITEM_ID
        UNION
        SELECT PRODUCT_ID 
        FROM T_3 
        WHERE ITEM_ID = AMOUNT.ITEM_ID)
   AND TPL.TYPE IN ('1', '2')
   AND TPL.ACCUM = 'Y'
   AND AMOUNT.UNDER_ID = 1011110000
 GROUP BY AMOUNT.UNDER_ID, AMOUNT.INSUR_ID;

分析SQL语句结构，子查询中通过关联条件ITEM_ID与外部表的AMOUNT.ITEM_ID存在关联条件。说明这是一个关联子查询。除此之外，子查询还通过查询出的PRODUCT_ID列，与外部表的TPL有关联，TPL表的PRODUCT_ID需要满足在子查询查询出的结果中。

因此，上述的关联子查询就与两个表同时存在了关联条件。造成的现象是有代表性的，无法应用查询转换技术。

第三章 问题原因

进一步理解SQL结构：
查看子查询外的两个表，AMOUNT、TPL与子查询都有关联条件。但这两个表自身并没有任何的连接条件。当

两个表没有关联条件时，我们最先想到的是什么问题呢？
笛卡尔积，对。笛卡尔积是SQL代码中对性能影响最大的问题之一。

除此之外，子查询又无法应用查询转换技术。那子查询部分的访问方式也是固定并无法改进的。那整个SQL的表访问方式可以说基本上是确定的。

下面分析执行计划：

根据我们上面对SQL语句结构的剖析，首先看外部表。外部表TPL、AMOUNT通过执行计划看是走的循环嵌套。但我们对比连接条件。被驱动表的AMOUNT连接条件只是他自身的过滤条件(“AMOUNT”.“UNDER_ID”=:SYS_B_3)，并没有与驱动表TPL有任何的连接列。这也复合SQL的查询结构。因此这里可以理解成就是笛卡尔积的访问方式。

接下来看子查询的访问，子查询的访问方式，是通过外部主查询将两个表的关联条件(“ITEM_ID”=:B1)、(“PRODUCT_ID”=:B1)，传入到子查询内部，来获得待返回结果的。访问的方式就是：外部主查询返回多少行，这里主查询部分就要访问多少次。通过执行计划的Starts看，子查询访问了46056次。大量的子查询访问造成了本条SQL的查询性能问题。这里也能印证外部主查询的访问方式。外部表的A-ROWS（404*114=46056）正好对应于子查询的访问次数。因此这里理解外部表是笛卡尔积连接是正确的。

第四章 解决方案

明确了问题原因，下面考虑解决方案：

外部主查询可以看做是一个集合，把条件"ITEM_ID"传入到子查询内部，可能会关联出0-N行记录，这部分记录的PRODUCT_ID由于外部是通过IN关联的，那也就是说，不论关联出来的N是否大于1。最后的IN子查询关联条件都会去重，只要满足TPL.PRODUCT_ID IN …就返回，否则不返回。

既然问题发生在关联子查询部分，考虑能否将关联子查询改写为子查询与外部表的直接JOIN。通常关联子查询改写为JOIN时，需要对子查询去重，因此需要加GROUP BY操作。

而本案例中由于内部子查询是通过UNION合并的，UNION动作本身就是会自动去重的，因此这个例子中的子查询部分，可以不加GROUP BY聚合。

聚合后，保证了子查询与外部表的JOIN过程中不会由于关联子查询产生多余记录。之后将待关联的两个列ITEM_ID、PRODUCT_ID与外部集合做JOIN关联。实现了手动的查询改写。基于上述思路，可以改写为如下SQL写法：

select AMOUNT.UNDER_ID,
       AMOUNT.INSUR_ID,
       SUM(AMOUNT.LIFE1_AMOUNT) AS LIFE1_AMOUNT,
       SUM(AMOUNT.LIFE2_AMOUNT) AS LIFE2_AMOUNT,
       SUM(AMOUNT.LIFE3_AMOUNT) AS LIFE3_AMOUNT,
       SUM(AMOUNT.A_AMOUNT) AS A_AMOUNT,
       SUM(AMOUNT.B_AMOUNT) AS B_AMOUNT,
       SUM(AMOUNT.C_AMOUNT) AS C_AMOUNT,
       SUM(AMOUNT.D_AMOUNT) AS D_AMOUNT
  from T_AMOUNT AMOUNT,
       (SELECT ITEM_ID, PRODUCT_ID
          FROM T_1
        UNION
        SELECT ITEM_ID, PRODUCT_ID
          FROM T_2
        UNION
        SELECT ITEM_ID, PRODUCT_ID 
		FROM T_3) tp,
       T_LIFE TPL
 where AMOUNT.ITEM_ID = tp.ITEM_ID
   AND AMOUNT.UNDER_ID = 1011110000
   and TPL.TYPE IN ('1', '2')
   AND TPL.ACCUM = 'Y'
   and TPL.PRODUCT_ID = tp.PRODUCT_ID
 GROUP BY AMOUNT.UNDER_ID, AMOUNT.INSUR_ID;

分析改写后的语句是否等价：

外部主查询的AMOUNT.ITEM_ID与子查询的tp.ITEM_ID做关联，满足关联条件的同时，还要满足tp.PRODUCT_ID列与另一个列TPL.PRODUCT_ID的关联。而关联过程中子查询做了去重动作，同时考虑tp.ITEM_ID、tp.PRODUCT_ID并不会为外部主查询带来多余记录。与改写前的TPL.PRODUCT_ID IN 子查询完全是等价的。

下面对比执行计划的效率：

改写后，T_AMOUNT将关联条件与子查询做连接，通过谓词推入技术，将连接谓词推入到子查询中。获得关联后的结果集。只访问了114次子查询部分。且有相应的索引做支撑，与原始的46056次对比有非常大的性能提升。之后将关联后的结果集，在于另一个表T_LIFE做关联。连接后直接获得最终结果。

最终的效果是降低了循环访问子查询的次数。
仅消耗1000多逻辑读。COST也仅为14158。时间0.01秒
而原始计划中，资源消耗达到了188K次。COST达到了5178K。时间0.66秒
这也从多个方面证明了改写后的效率是有质的提升的。

第五章 问题总结

通过上述案例的分析。exist/in 关联子查询，通常是产生性能问题的一个比较关键的部分。因此在实际书写SQL的过程中，需要考虑子查询的写法。尽量避免对子查询做过于复杂的嵌套关联。如本案例中，做了关联子查询，又对子查询中的其他列与其他表再次关联。从而造成了无法应用查询转换技术，只能采用固定好的查询方式访问。