rows算错导致错误的笛卡尔积造成temp不足

今天一哥们发来一条sql说一直不出结果，最后报错：ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段

 

select tran_info.*,
       rp.mcc_typ_id,
       urm2.org_no,
       trim(urm2.rate_type) as rate_type,
       (case
         when tran_info.oldSXF < urm2.max_fee_amt then
          '0'
         when tran_info.oldSXF >= urm2.max_fee_amt then
          '1'
         else
          '0'
       end) as iSCap
  from biz_test.T_BIZ_SINGLE_FEE_TEMP tran_info,
       (select mst.*
          from biz_test.T_BIZ_MERC_SCOPE_TEMP mst
         where mst.merc_id not in
               (select merc_id
                  from biz_test.T_BIZ_SPECIAL_MERC bsm
                 where bsm.spec_type = '2'
                   and bsm.is_used = '1')) urm2,
       biz_test.t_mcc rp
 where tran_info.merc_id = urm2.merc_id
   and tran_info.mcc_cd = rp.mcc_cd

 

解释：执行计划里面有笛卡尔积MERGE JOIN CARTESIAN，单看SQL的话，是不可能产生笛卡尔积的,所以这个问题统计信息不准导致ROWS算成了1（越是小表ROWS越有可能算成1）。优化器则认为其走的正确的笛卡尔积【什么是正确的笛卡尔积，什么是错误的笛卡尔积？】假设一个表的数据是30w ，被算成了1 走笛卡尔积，那么结果集就要被放大30w倍，所以导致temp不足报错也在情理之中

处理方法：

1.加hint，根据表的大小让其强制走hash 或者 nl

写hint很容易眼高手低，而且优化器的版本不同hint的格式也有微小的差别 

2.收集统计信息：系统自带有，DBA也会做相应的策略总结：

rows算错的情况在执行计划中经常存在，大多都是因为rows算小了，一般会引起两种错误的执行计划rows算成1 错误的笛卡尔积，执行计划里面笛卡尔积关键字和rowsrows算小了本来应该HASH 结果走了NL，这时候逻辑读会大的很离谱