使用mod對資料進行進行分組解決TEMP表空間不足的問題

westzq1984發表於2013-01-01
一個統計SQL，消耗了100G TEMP報錯。

SELECT /*+ parallel(a,10) parallel(b,10) parallel(c,10) use_hash(a,b,c) */*
  from TU_INT.I_PRD_OFFER_MEMBER_1 partition(p13) a,
       tu_model.tb_prd_offer_inst_280 partition(p2) b,
       TU_MODEL.TB_PRD_OFFER_ROLES c
 where a.OFFER_ID = b.SRC_OFFER_INST_ID
  and a.OFFER_ROLE_ID=c.OFFER_ROLE_ID(+);

主機空閒記憶體嚴重不足，嘗試了手工設定HASH區，一共分了7G PGA還是不行，不敢在分多。NESTED LOOP的速度客戶無法接受

於是想到了對資料進行分組

SELECT /*+ parallel(a,10) parallel(b,10) parallel(c,10) use_hash(a,b,c) */*
  from TU_INT.I_PRD_OFFER_MEMBER_1 partition(p13) a,
       tu_model.tb_prd_offer_inst_280 partition(p2) b,
       TU_MODEL.TB_PRD_OFFER_ROLES c
 where a.OFFER_ID = b.SRC_OFFER_INST_ID
  AND MOD(a.OFFER_ID,10)=0
  AND MOD(b.SRC_OFFER_INST_ID,10)=0
  and a.OFFER_ROLE_ID=c.OFFER_ROLE_ID(+);

進行分組後，基本上TEMP消耗在20G左右，可以跑出來資料。
客戶可以接受

不過這個SQL挺奇怪的，對於1個17G的表和1個30多G的表HASH，在和一個20M的表做外連線，按照測試竟然需要200G以上的TEMP。感覺9I無法進行hash right outer join有關。有空在研究

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

相關文章