关于表关联关联条件中存在or的一次优化

在生产系统上发现个sql执行需要十几个小时，查看monitor的瓶颈在于一个结果集被作为视图参与nest loop，被当做被驱动表执行了n次

生产上的语句较为复杂，剔除了其他表关联，我用自己的环境模拟了这个场景create table dw_0704 as select * from dba_objects;

create table dw_0704a as select * from dba_objects where rownum <10000;

--写法1
select *
  from dw_0704a a
  left join dw_0704 b
    on a.object_id = b.object_id
   and ((b.object_name like 'D%' and b.namespace = 1) or
       (b.object_type like 'T%' and b.namespace = 4) or
       (b.created > to_date('20230101', 'yyyymmdd') and b.namespace = 4))

--写法2
select *
  from dw_0704a a
  left join (SELECT *
               FROM dw_0704 b
              WHERE (b.object_name like 'D%' and b.namespace = 1)
                 or (b.object_type like 'T%' and b.namespace = 4)
                 or (b.created > to_date('20230101', 'yyyymmdd') and
                    b.namespace = 4)) D
    on a.object_id = D.object_id

比较下两次执行的效果以及执行计划，我们采用sql monitor收集真实的执行计划，用hint强制采集，/*+ monitor dwtest92 */

select * from v$sql where sql_text like '%dwtest92%'；          --查看sql_id

select dbms_sqltune.report_sql_monitor(sql_id=>'2r7jww7j05rv8',type=>'HTML',report_lEVEL=>'ALL') AS REPORT FROM DUAL;       ---查询monitor报告

写法1的报告：

可以看出执行消耗了47s，采用的是嵌套循环链接方式，dw_0704被执行了9999次全表扫描，总cost是惊人的300w；

第二种写法报告：

可以看到耗时很短，0.2s多，采用的是hash链接方式，dw_0704被执行了一次，总cost是385；

对于以上的优化总结，left join (select * from t where name like 'new%') x on x.id=a.id ; 等价于:left join t on t.id=a.id and t.name like 'new%'；

建议使用left join (select * from t where name like 'new%') x on x.id=a.id ; 当on后面的条件复杂例如存在多个or的时候，该执行计划很可能是走嵌套循环连接，如果这个表比较大

此时就是悲剧加上悲剧；部分情况下，On 后面的条件不复杂时，执行计划是跟（left join (select * from t where name like 'new%') x on x.id=a.id ;）写法的执行计划一样的。所以，

在实际情况中还是要多测试观察真实的执行计划，督促开发设计减少使用or，以及关联表的数量，建议不要超过3个。