MERGE JOIN CARTESIAN 优化…

原文地址：MERGE JOIN CARTESIAN 优化一例 作者：plutohiyo

     前两天开发告诉我，项目数据迁移需要用到的一条sql在跑了几分钟后就会报错。截图给我是temp表空间撑满了。sql拿过来看了一下执行计划走了MERGE JOIN CARTESIAN。大多数情况下，笛卡尔join效率都非常差，出现错误的MERGE JOIN CARTESIAN原因主要是CBO估算错误或者sql语句忘记写等值连接条件。此sql参与笛卡尔join的两个结果集都非常大，所以耗尽temp表空间就不奇怪了。

     仔细看看sql，发现sql虽然关联比较复杂，但最终的结果集才1万多条记录。而且一个大的业务表上写的两个过滤条件选择性都还不错，所以想到了加上合适的hint，（统计信息已经是最新的了）让选择性不错的表做驱动走nest loop应该5秒内就能出结果。 

-----------------------------------------------

    select  xxx  from  ooi,oosla,ooslb
 where oosla.order_item_id = ooi.id
 and oosla.gmt_service_begin > trunc(sysdate - 2)
            or oosla.gmt_service_end > (sysdate - 3)
   and oosla.order_item_id = ooslb.order_item_id
   and ooi.is_deleted = 'n'
   and oosla.is_deleted = 'n'
   and ooi.PRODUCT_CODE IN ('pc001', 'pc005', 'pc002', 'pc006', 'pc003','pc090', 'pc091', 'pc007')      
 ORDER BY ooslb.gmt_service_begin;

------------------------------------------------

图中标红的两个条件上是有索引的，而且通过过滤后记录数值有2万多条，所以sql改写成：

select xxx  from ooi,
       (select *
          from oosla
         where gmt_service_begin > trunc(sysdate - 2)or gmt_service_end > (sysdate - 3)) oosla,
                  ooslb
 where oosla.order_item_id = ooi.id
   and oosla.order_item_id = ooslb.order_item_id
   and ooi.is_deleted = 'n'
   and oosla.is_deleted = 'n'
   and ooi.PRODUCT_CODE IN ('pc001', 'pc005', 'pc002', 'pc006', 'pc003','pc090', 'pc091', 'pc007')      
 ORDER BY ooslb.gmt_service_begin;

--------------------------------------------

oosla，ooi，ooslb走了nest loop，3秒不到就出来了。

在写hint的时候，还发现了和index_combine相近的一个hint：index_join ，oracle在使用多个index返回结果集的时候其实还有一种方法：and_equal 有兴趣的同学可以参考lewis的文章：

http://nixforums.org/about109312-Hints-difference-between-AND_EQUAL--INDEX_JOIN-and-INDEX_.html