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命中注定相克啊。
或者有哪个大佬有啥好的解决替代方案,求赐教。
本文摘自 :https://www.cnblogs.com/