使用mod对数据进行进行分组解决TEMP表空间不足的问题

一个统计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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-751981/