Oracle分析函数和exists/not exists命中注定相克啊

 

Oracle分析函数和exists/not exists命中注定相克啊

 

举个栗子，有如下SQL：

select h.c1, h.c2, h.c3, h.c4, b.c5
  from h, b
 where h.c1 = b.c1
   and not exists
 (select 1
          from child cs
         where cs.old_c3 = h.c3
           and cs.old_c4 = h.c4
           and cs.c5 = b.c5
           and cs.create_time = (select max(t.create_time)
                                   from child t
                                  where t.old_c3 = h.c3
                                    and t.old_c4 = h.c4
                                    and t.c5 = b.c5));

这条SQL中，exists后边的子查询中，child表使用了两次，若是关联条件没有索引或者有索引但是用不上导致的child全表扫，就会有两次全表扫描。

child表如果很大的话是无法忍受两次的全表扫，极大的延迟SQL的执行时间并且消耗大量IO。

可以利用分析函数将SQL改成如下：

select h.c1, h.c2, h.c3, h.c4, b.c5
  from h, b
 where h.c1 = b.c1
   and not exists (select 1
          from (select 1,
                       rank() over(partition by cs.old_c3, cs.old_c4, cs.c5 order by cs.create_time desc) rk
                  from child cs
                 where cs.old_c3 = h.c3
                   and cs.old_c4 = h.c4
                   and cs.c5 = b.c5)
         where rk = 1);

这两条SQL完全等价，并且更改后的child表只会扫描一次。

但是！！！

更改后的SQL是无法成功执行的，因为两层子查询会导致h表，b表无法被最里层的查询认到。

执行会报错：

ORA-00904: "B"."C5": invalid identifier

这个问题网上也有人有类似的：https://blog.csdn.net/weixin_28950015/article/details/116386137

 

没办法了，用了分析函数就注定要套多一层子查询然后将rk放在外边过滤（总不能用了分析函数然后不过滤数据吧？），这样在用exists/not exists就势必会产生两层子查询。。

所以分析函数和exists/not exists命中注定相克啊。

 

或者有哪个大佬有啥好的解决替代方案，求赐教。