子查询包含or引起的filter性能问题案例

Sql语句

。。。

这个sql语句的主要问题在于最后的一个filter操作，一般我们在子查询中经常会看见hash jon 和filter两种执行计划，cbo在9i下就能够根据条件去选择合适的执行计划，当然走hash join也需要一些限制，而这里的cbo之所以没有选择hash join而选择糟糕的filter正是因为这个子查询的or引起的，我们在执行计划id=4 filter的谓词转换中能够清晰的看见cbo转换为一个exists or exists形式

这个版本的数据库是10.2.0.5，这里cbo没有能够对这个or做一个union all的操作然后转换为view来做hash join，这里我们选择改写or为union all来帮助cbo选择合适的hash join，改写完后的sql语句执行计划如下：（由于sql语句较长，这里我只摘要修改的部分和执行计划）

这里看出改写为union all的sql语句执行计划已经由filter改变了hash join，而且驱动表的顺序也已经改变了，都是用小结果集去做驱动表。

改成上述sql后，这个sql执行成本下降了许多，这里截取部分赋部分值给绑定变量予以显示区别：

1) Union all改写后当：1=10时的消耗资源

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17138 consistent gets
2816 physical reads
0 redo size
27539 bytes sent via SQL*Net to client
25555 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

2 ) 原sql语句 ：1=10消耗的资源：

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
313213 consistent gets
10029 physical reads
64 redo size
27539 bytes sent via SQL*Net to client
24605 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

需要注意的是oracle 11g下，对于子查询中包含or的已经能被cbo优化为union all操作来和另外的表的做hash join联合，从而可能避免了某些糟糕的filter执行计划。