大表範圍掃描走SORT MERGE JOIN的SQL優化

liiinuuux發表於2015-01-06
SQL優化
SQL文字
（部分表名欄位名做了替換）
select XXXXX
  from t1 a
 where 1 = 1
   and a.flag = '0'
   and a.flag2 = '1'
   and exists (select 'x'
          from t2 b
         where b.startno <= t1_certno
           and b.endno >= t1_certno
           and balanceno in
               (select c.balanceno
                  from t3 c
                 where 1 = 1
                   and c.proposalno = '50062000429971'))
 order by a.t1_certno

邏輯讀排第一。這個 SQL由於是範圍掃描，因此只能走 SORT MERGE JOIN，幾個小時都跑不出結果。
靠固定執行計劃解決不了問題。
要解決問題只能改程式。

其實，給定 proposalno從 t3裡搜出來的資料一般只有幾行，而且每行資料的 startno和 endno也相差不遠。
因此可以將 b.startno <= t1_certno and b.endno >= t1_certno改成t1_certno in “t3裡每行的b.startno和 b.endno之間每一個值”。
也就是先找出所有的 t1_certno所有可能的取值，最後再通過索引從大表 t1找資料。

下面寫了一個方案，開發可以根據需要改寫成能和程式互動的方式，例如寫成函式，返回 table型別的資料等。
set serverout on
declare
    tmp t2.startno%type;
    i number;
    len int;
begin
    for c1 in ( select startno, endno from t2 b where balanceno in
                      (select c.balanceno
                         from t3 c
                        where 1 = 1
                          and c.proposalno = '50062000429971')
    ) loop
        i := to_number(c1.startno);
        len := length(c1.startno);
        while i <= to_number(c1.endno) loop
           -- dbms_output.put_line(lpad(to_char(i), len, '0'));
            for c2 in (
               select 要查的欄位
                 from t1 a
                where 1 = 1
                  and a.flag = '0'
                  and a.flag2 = '1'
                  and t1_certno = lpad(to_char(i), len, '0')
                order by a.t1_certno
              ) loop
                dbms_output.put_line(c2.xxx||' '||c2.xxx||' '||c2.xxx||' '||c2.xxx||' '||c2.xxx||' '||c2.xxx||' '||c2.xxx);
              end loop;
            i:=i+1;
        end loop;
    end loop;
end;
/

......
......


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26239116/viewspace-1392049/，如需轉載，請註明出處，否則將追究法律責任。

相關文章