针对 《select 表1 left join (select * from 表2 group by ）on 表1.column_name=表2.column_name and 表1.选择性极好的字段=：B1》 子查询表2全扫的优化；

      

SQL> SET AUTOT TRACEONLY  -->仅显示执行计划和统计信息，执行SQL但不显示SQL 
SQL> set line 1000
SQL> set timing on
       i.item_name itemName,
       d.size_no sizeNo,
       d.store_no storeNo,
       max(st.store_code) storeCode,
       max(st.store_name) storeName,
       sum(d.qty) qty,
       max(s.size_kind) sizeKind,
       max(s.size_code) sizeCode,
       substr(d.brand_no, 0, 2) sysNo,
       sub.box_row_id boxRowId,
       d.box_no boxNO,
       max(dl.remarks) remarks,
       max(dl.createtm) createtm,
       max(dl.audittm) audittm
  from bill_om_deliver_dtl d
  left join bill_om_deliver dl
    on d.locno = dl.locno
   and d.owner_no = dl.owner_no
   and d.deliver_no = dl.deliver_no
  left join con_label l
    on l.locno = d.locno
   and d.container_no = l.container_no
   and d.store_no = l.store_no
  left join (select r.locno,
                    r.recheck_no,
                    r.container_no,
                    r.item_no,
                    r.size_no,
                    r.box_row_id
               from bill_om_recheck_dtl r                   --->此子查询为慢的根源,详情见如下分析
              where r.locno = 'C4311'
              group by r.locno,
                       r.recheck_no,
                       r.container_no,
                       r.item_no,
                       r.size_no,
                       r.box_row_id) sub
    on sub.locno = d.locno
   and sub.recheck_no = l.recheck_no
   and sub.container_no = d.container_no
   and sub.item_no = d.item_no
   and sub.size_no = d.size_no
inner join item i
    on i.item_no = d.item_no
inner join size_info s
    on s.sys_no = substr(d.brand_no, 0, 2)
   and s.size_no = d.size_no
   and s.size_kind = i.size_kind
  left join store st
    on d.store_no = st.store_no
where 1 = 1
   and d.locno = 'C4311'
   and d.deliver_no = 'C4311PL16050600014'
group by d.LOCNO,
          d.OWNER_NO,
          d.DELIVER_NO,
          d.Store_No,
          d.ITEM_NO,
          i.ITEM_NAME,
          d.SIZE_NO,
          d.LOADPROPOSE_NO,
          d.brand_no,
          sub.box_row_id,
          d.box_no,
          s.HCOL_NO
order by d.store_no, sub.box_row_id, s.HCOL_NO;

51 rows selected. 

Elapsed: 00:00:48.34                           -->51条数据执行48秒

Execution Plan
----------------------------------------------------------
Plan hash value: 2729322515


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("SUB"."SIZE_NO"(+)="D"."SIZE_NO" AND "SUB"."ITEM_NO"(+)="D"."ITEM_NO" AND
           "SUB"."CONTAINER_NO"(+)="D"."CONTAINER_NO" AND "SUB"."RECHECK_NO"(+)="L"."RECHECK_NO" AND
           "SUB"."LOCNO"(+)="D"."LOCNO")
   6 - access("S"."SYS_NO"=SUBSTR("from$_subquery$_005"."BRAND_NO",0,2) AND
           "S"."SIZE_NO"="from$_subquery$_005"."SIZE_NO")
   9 - access("D"."DELIVER_NO"="DL"."DELIVER_NO"(+) AND "D"."OWNER_NO"="DL"."OWNER_NO"(+) AND
           "D"."LOCNO"="DL"."LOCNO"(+))
  11 - access("D"."LOCNO"='C4311' AND "D"."DELIVER_NO"='C4311PL16050600014')
       filter("D"."DELIVER_NO"='C4311PL16050600014')
  13 - access("DL"."LOCNO"(+)='C4311' AND "DL"."DELIVER_NO"(+)='C4311PL16050600014')
  14 - filter("D"."STORE_NO"="L"."STORE_NO"(+))
  15 - access("L"."LOCNO"(+)='C4311' AND "D"."CONTAINER_NO"="L"."CONTAINER_NO"(+))
  18 - access("from$_subquery$_005"."QCSJ_C000000000500002"="ST"."STORE_NO"(+))
  21 - filter("R"."LOCNO"='C4311')
  22 - access("I"."ITEM_NO"="from$_subquery$_005"."ITEM_NO")
  23 - filter("S"."SIZE_KIND"="I"."SIZE_KIND")

Statistics
----------------------------------------------------------
       0  recursive calls
       0  db block gets
    1063099  consistent gets
      67590  physical reads
       4512  redo size   -->产生redo size 是因为有人在对bill_om_deliver_dtl/BILL_OM_RECHECK_DTL..等本次查询的表做DML(非select)操作..具体参考 Oracle DML（非select） 操作不commit 对select的影响  

       4287  bytes sent via SQL*Net to client
     557  bytes received via SQL*Net from client
       5  SQL*Net roundtrips to/from client
       1  sorts (memory)
       0  sorts (disk)
     51  rows processed
SQL> 

   分析注释：

   主要性能消耗处：BILL_OM_RECHECK_DTL 3kw表全扫并范围分组，且由于BILL_OM_RECHECK_DTL部分是子查询group by 导致外层表bill_om_deliver_dtl d量及时很小也未传入到子查询中；开发确认该SQL不能再加过滤条件；

   针对该sql 有3种优化方案；

   1、查看原SQL蓝色部分；

        所以完全可以将bill_om_recheck_dtl和范围where d.locno=''  and d.deliver_no=''传入bill_om_deliver_dtl表大量扫描的里层做关联sub.column_name=d.column_name后取出俩者关联后的小量再与最外层bill_om_recheck_dtl等其他表再做一次数据核对匹配..