“no_merge”hints優化檢視訪問低效問題

pwz1688發表於2014-03-06
今天在itpub論壇看到一則貼子,樓主反應以下sql執行緩慢:
create table test_20140230 as
select to_number(201401) deal_date, e.src_terminal_id, e.cont_type
  from (select *
          from test1 a
         where a.forward_time >= 201401 || '00000000'
           and a.forward_time < 201402 || '00000000'
           and exists
         (select TERMINAL_ID
                  from test2 b
                 where b.oper_type in (1, 2, 3)
                   and b.oper_time >= to_date(201401, 'yyyymm')
                   and b.oper_time <
                       trunc(add_months(to_date(201401, 'yyyymm'), 1))
                   and b.terminal_id = a.src_terminal_id
                   and b.dest_id = a.dst_terminal_id
                   and b.info_id = a.info_id
                   and (to_date(a.forward_time, 'yyyyMMddHH24miss') -
                       b.oper_time) < 1
                   and decode(b.type,
                              1,
                              1,
                              2,
                              1,
                              6,
                              1,
                              7,
                              1,
                              3,
                              2,
                              4,
                              2,
                              5,
                              2) = a.cont_type)) e,
       (select distinct d.mobile
          from (select *
                  from test3
                 where act_id in ('192065', '130444')
                   and substr(register_time, 1, 6) <= 201401) c,
               (select *
                  from test4
                 where substr(create_time, 1, 6) <= 201401
                   and status = 1) d
         where c.fee_terminal_id = d.mobile) f
 where e.src_terminal_id = f.mobile
但將以上紅色標識程式碼先建立為臨時表,再用臨時表代替執行上面sql時,速度明顯加快。樓主想問個所以然。
     因為實際工作中,像樓主這種優化方式,我用過很多,一直以為,借用臨時表,先將需要資料抽取出來,然後再根據應用需要,從臨時表中
直接抽取資料,這樣肯定快的多,只是會犧牲掉臨時表的維護。沒想到,還有更優化的方式,那就是使用no_merge。
    將以上紅色部分程式碼select * from test1 a 改為select /*+ no_merge*/ * from test1 a 此hints方式與建立臨時表方式優化是等效的。
網上度娘了下no_merge用法,沒有很詳細的解釋,個人認為,就是一個子查詢或檢視,本身單獨執行不慢,但放入一個子查詢中時,oracle會打散原來的執行計劃,為了將此子查詢或檢視,作為一個整體,不被打散,提供no_merge hints方式。
如:查詢系統表的二個檢視

SELECT COUNT(*) FROM DBA_SEQUENCES, DBA_OBJECTS;

耗時約半小時,單獨執行這二個檢視count查詢時,都在8秒內。

使用no_merge hints如下:

SELECT /*+ NO_MERGE(A) NO_MERGE(B) */ COUNT(*)  FROM DBA_SEQUENCES A, DBA_OBJECTS B;

約1分鐘。

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

相關文章