pushed predicate in 10g and 11g

select o.id,
       o.LOC_ID,
       o.QTY_AVAILABLE – nvl (sa.qty_allocating, 0 as actQty,
       o.LOT_ID,
       o.LPN_NO,
       nvl (sp.qty_pending, 0 as pending,
       b.LotAtt03,
       b.LotAtt02
  from STK_BATCH_LOC_LPN o
  inner join STOCK_BATCH_ATT b
    on o.LOT_ID = b.id
   and b.IS_DELETED = 0
   and b.warehouse_id = 15628
  inner join MD_LOCATION loc
    on o.LOC_ID = loc.id
   and loc.IS_DELETED = 0
   and loc.warehouse_id = 15628
  left join (select sai.stk_lpn_id as stockid,
                    sum (sai.qty_allocating) as qty_allocating
               from stk_allocating sai
              where sai.is_deleted = 0
                and sai.warehouse_id = 15628
              group by sai.stk_lpn_id) sa
    on o.id = sa.stockid
  left join (select stp.stk_lpn_id, sum(stp.qty_pending) as qty_pending
               from stk_pending stp
              where stp.is_deleted = 0
                and stp.warehouse_id = 15628
              group by stp.stk_lpn_id) sp
    on sp.stk_lpn_id = o.id
  where o.IS_DELETED = 0
   and loc.LOC_TYPE in (‘EA’ )
   and o.SKU_ID = :skuId
   and o.warehouse_id = 15628 ;
 
create index IDX_STK_ALLOCING_ID on STK_ALLOCATING (STK_LPN_ID);
 
–10g的表现是 stk_allocating hash group by 不走IDX_STK_ALLOCING_ID index, 想用上sort group by 看是否走index? NO. opt_param(‘_gby_hash_aggregation_enabled’,’false’)
select o.id,
       o.LOC_ID,
       o.QTY_AVAILABLE – nvl (sa.qty_allocating, 0 as actQty,
       o.LOT_ID,
       o.LPN_NO,
       nvl (sp.qty_pending, 0 as pending,
       b.LotAtt03,
       b.LotAtt02
  from STK_BATCH_LOC_LPN o
  inner join STOCK_BATCH_ATT b
    on o.LOT_ID = b.id
   and b.IS_DELETED = 0
   and b.warehouse_id = 15628
  inner join MD_LOCATION loc
    on o.LOC_ID = loc.id
   and loc.IS_DELETED = 0
   and loc.warehouse_id = 15628
  left join (select /*+ opt_param(‘_gby_hash_aggregation_enabled’,’false’) */
              sai.stk_lpn_id as stockid,
              sum (sai.qty_allocating) as qty_allocating
               from stk_allocating sai
              where sai.is_deleted = 0
                and sai.warehouse_id = 15628
              group by sai.stk_lpn_id) sa
    on o.id = sa.stockid
  left join (select stp.stk_lpn_id, sum(stp.qty_pending) as qty_pending
               from stk_pending stp
              where stp.is_deleted = 0
                and stp.warehouse_id = 15628
              group by stp.stk_lpn_id) sp
    on sp.stk_lpn_id = o.id
  where o.IS_DELETED = 0
   and loc.LOC_TYPE in (‘EA’ )
   and o.SKU_ID = :skuId
   and o.warehouse_id = 15628;
 
 
–11g 里的表现是VIEW PUSHED PREDICATE, 且走了index.
The PUSH_PRED hint instructs the optimizer to push a join predicate into the view.

其实,执行计划在有限的集合返回下采用了hash Join,而且对STK_ALLOCATING进行了全表扫描,显然index没有进入union view,理想的是走index,然后选择nested loop,从执行计划来看,应该是Oracle的问题,没有在该语句上启用pushed predicate. 再加hint /*+ push_pred(sai) */, 还是不起作用…遇到该情况还是将SQL语句重写join来完成push pred.