19c优化器对left join含or关联的

有下面类似SQL写法, 在19c之前, 只能使用Nested loops的执行计划, 如果T1表比较大, SQL执行效率会比较差.

下面SQL如果不在T2表的object_name字段上创建索引, 执行效率更是会差到无法接受.

升级到了19c版本后, 优化器做了一些改进, 上面的SQL可以使用hash join了, 如果T1表是大表, 执行效率会有明显的提高.

那么问题来了:

19c版本之前, 该做怎样的改写, 能让优化器使用Hash join的执行计划?

这个是有真实生产案例的, SQL执行时间长达40多分钟, 客户无奈只能接受(或者说忍受)这个时间, 把or条件用case when改写一下, 执行时间降到10分钟以下, 确实是让大家都挺开心的一件事.

一个业务SQL需要访问多少数据量和执行时间是有一个预期的, 超出了这个预期, 都在优化范围.

现在的生产系统, 大家都把性能提升寄希望于硬件的改善, 这种思想是不好的, 基本上大部分Top SQL都是需要优化而且是可以优化的.

create table t1 as select *from dba_objects;

create table t2 as select *from t1;
insert into t2 select *from t1;
create index t2_idx on t2(object_name)

select count(*) from t1;
select count(*) from t2;
select count(*) from t1 x where x.owner='SYS'

select count(*) from (
select /*+ no_use_hash(x,t2)*/x.owner t1,x.* from t1 x left join t2 on x.object_name=t2.object_name
and (x.object_id=t2.object_id  or  x.object_id=t2.data_object_id) 
and x.owner='SYS'
)
 where  t1='SYS'
 
 select /*+ no_use_hash(x,t2)*/count(*) from t1 x left join t2 on x.object_name=t2.object_name
and (x.object_id=t2.object_id  or  x.object_id=t2.data_object_id) 
 and x.owner='SYS'
 
  select /*+ no_use_hash(x,t2)*/count(*) from t1 x left join t2 on x.object_name=t2.object_name
and (x.object_id=t2.object_id  or  x.object_id=t2.data_object_id) 
 where  x.owner='SYS'

 
select count(*) from t1 x ,t2 where  x.object_name=t2.object_name(+)
and (x.object_id=t2.object_id(+)  or  x.object_id=t2.data_object_id(+)) 
and x.owner='SYS'

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

select count(*) from t1 x ,t2 where  x.object_name=t2.object_name(+)
and x.object_id=t2.object_id(+)   
and x.owner='SYS' --422265

select count(*)  from t1 x left join t2 on x.object_name=t2.object_name
and (x.object_id=t2.object_id  ) 
and x.owner='SYS'--442762 --and 的意思T1表全部要，但只用SYS的数据去join

select count(*)  from t1 x left join t2 on x.object_name=t2.object_name
and (x.object_id=t2.object_id  ) 
where  x.owner='SYS'--422265-----where 才是过滤T1的

select count(*) from (
select * from t1 x left join t2 on x.object_name=t2.object_name
and (x.object_id=t2.object_id  ) 
and x.owner='SYS'
union 
select * from t1 x left join t2 on x.object_name=t2.object_name
and (x.object_id=t2.data_object_id  ) 
and x.owner='SYS')