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/

Oracle分析函數和exists/not exists命中注定相克啊2022-05-31 17:09:31

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

Oracle分析函數和exists/not exists命中注定相克啊
2022-05-31 17:09:31