ITPub博客

首页 > 数据库 > Oracle > Oracle 针对子查询里有group by 表全扫的优化

Oracle 针对子查询里有group by 表全扫的优化

原创 Oracle 作者:maohaiqing0304 时间:2016-05-11 13:55:52 1 删除 编辑


标题:Oracle 针对子查询里有group by 表全扫的优化 

作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]


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

      
表信息
表名称 表行数
BILL_OM_DELIVER_DTL 32747368
BILL_OM_RECHECK_DTL 33115919

字段情况
表名称 字段名称 num_distinct
BILL_OM_DELIVER_DTL CONTAINER_NO 2398464
BILL_OM_DELIVER_DTL BOX_NO 2330112
BILL_OM_DELIVER_DTL BARCODE 545792
BILL_OM_DELIVER_DTL ITEM_NO 123328
BILL_OM_DELIVER_DTL EXP_NO 108864
BILL_OM_DELIVER_DTL DELIVER_NO 99328
BILL_OM_DELIVER_DTL CREATETM 93112
BILL_OM_DELIVER_DTL EDITTM 92248
BILL_OM_DELIVER_DTL EXP_DATE 91640
BILL_OM_DELIVER_DTL STORE_NO 23984
BILL_OM_DELIVER_DTL CONTAINER_ID 3037
BILL_OM_DELIVER_DTL SIZE_NO 1276
BILL_OM_DELIVER_DTL SUPPLIER_NO 912
BILL_OM_DELIVER_DTL CREATOR 640
BILL_OM_DELIVER_DTL EDITOR 634
BILL_OM_DELIVER_DTL CREATORNAME 630
BILL_OM_DELIVER_DTL EDITORNAME 624
BILL_OM_DELIVER_DTL QTY 268
BILL_OM_DELIVER_DTL ORDER_UNIT 173
BILL_OM_DELIVER_DTL BRAND_NO 137
BILL_OM_DELIVER_DTL LOCNO 120
BILL_OM_DELIVER_DTL IMPORT_BATCH_NO 117
BILL_OM_DELIVER_DTL ITEM_TYPE 5
BILL_OM_DELIVER_DTL BUSINESS_TYPE 4
BILL_OM_DELIVER_DTL EXP_TYPE 2
BILL_OM_DELIVER_DTL QUALITY 2
BILL_OM_DELIVER_DTL STATUS_TRANS 2
BILL_OM_DELIVER_DTL TMS_STATUS_TRANS 2
BILL_OM_DELIVER_DTL STATUS 2
BILL_OM_DELIVER_DTL OWNER_NO 1
BILL_OM_DELIVER_DTL LOCATE_NO 1
BILL_OM_DELIVER_DTL PACK_QTY 1
BILL_OM_DELIVER_DTL LOT_NO 1
BILL_OM_DELIVER_DTL BATCH_SERIAL_NO 1
BILL_OM_DELIVER_DTL EXT_BARCODE_NO 1
BILL_OM_DELIVER_DTL REAL_QTY 1
BILL_OM_DELIVER_DTL M3_TRANS_STATUS 1
BILL_OM_DELIVER_DTL D_ORDER_UNIT 0
BILL_OM_DELIVER_DTL PRODUCE_DATE 0
BILL_OM_DELIVER_DTL EXPIRE_DATE 0
BILL_OM_DELIVER_DTL ITEM_ID 0
BILL_OM_DELIVER_DTL ROW_ID 0
BILL_OM_DELIVER_DTL VOLUME 0
BILL_OM_DELIVER_DTL LOADPROPOSE_NO 0
BILL_OM_DELIVER_DTL LINE_NO 0
BILL_OM_DELIVER_DTL WEIGHT 0
BILL_OM_RECHECK_DTL RECHECK_DATE 9156608
BILL_OM_RECHECK_DTL BOX_NO 2866432
BILL_OM_RECHECK_DTL CONTAINER_NO 2483968
BILL_OM_RECHECK_DTL EDITTM 1272832
BILL_OM_RECHECK_DTL RECHECK_NO 1158912
BILL_OM_RECHECK_DTL JOIN_DATE 312032
BILL_OM_RECHECK_DTL ITEM_NO 125176
BILL_OM_RECHECK_DTL EXP_NO 108960
BILL_OM_RECHECK_DTL EXP_DATE 30948
BILL_OM_RECHECK_DTL ROW_ID 5079
BILL_OM_RECHECK_DTL BOX_ROW_ID 1494
BILL_OM_RECHECK_DTL SIZE_NO 1280
BILL_OM_RECHECK_DTL RECHECK_NAME 1108
BILL_OM_RECHECK_DTL ASSIGN_NAME 1106
BILL_OM_RECHECK_DTL RECHECKNAMECH 1063
BILL_OM_RECHECK_DTL ASSIGNNAMECH 1058
BILL_OM_RECHECK_DTL EDITOR 1052
BILL_OM_RECHECK_DTL EDITORNAME 1031
BILL_OM_RECHECK_DTL JOIN_NAME 646
BILL_OM_RECHECK_DTL JOINNAMECH 635
BILL_OM_RECHECK_DTL REAL_QTY 291
BILL_OM_RECHECK_DTL ITEM_QTY 289
BILL_OM_RECHECK_DTL BRAND_NO 136
BILL_OM_RECHECK_DTL LOCNO 120
BILL_OM_RECHECK_DTL STATUS 5
BILL_OM_RECHECK_DTL ISWHOLE 4
BILL_OM_RECHECK_DTL PACK_QTY 1
BILL_OM_RECHECK_DTL EXP_TYPE 1
BILL_OM_RECHECK_DTL ADD_FLAG 1
BILL_OM_RECHECK_DTL OWNER_NO 1
BILL_OM_RECHECK_DTL ITEM_ID 0
BILL_OM_RECHECK_DTL RECHECKNAMECH2 0
BILL_OM_RECHECK_DTL RECHECK_NAME2 0

索引情况
表名称 索引名称 字段 表行数 字段去重值 字段顺序
BILL_OM_DELIVER_DTL M1_IDXBILL_OM_DELIVER_DTL_MID BOX_NO 32747368 2330112 1
BILL_OM_DELIVER_DTL M1_IDXBILL_OM_DELIVER_DTL_MID EXP_NO 32747368 108864 2
BILL_OM_DELIVER_DTL M1_IDXBILL_OM_DELIVER_DTL_MID SIZE_NO 32747368 1276 3
BILL_OM_DELIVER_DTL M1_IDXBILL_OM_DELIVER_DTL_MID LOCNO 32747368 120 4
BILL_OM_DELIVER_DTL M1_IDXBILL_OM_DELIVER_DTL_MID STATUS_TRANS 32747368 2 5
BILL_OM_DELIVER_DTL PKBILL_OM_DELIVER_DTL CONTAINER_NO 32747368 2398464 1
BILL_OM_DELIVER_DTL PKBILL_OM_DELIVER_DTL DELIVER_NO 32747368 99328 2
BILL_OM_DELIVER_DTL PKBILL_OM_DELIVER_DTL CONTAINER_ID 32747368 3037 3
BILL_OM_DELIVER_DTL PKBILL_OM_DELIVER_DTL LOCNO 32747368 120 4
BILL_OM_DELIVER_DTL PKBILL_OM_DELIVER_DTL OWNER_NO 32747368 1 5
BILL_OM_RECHECK_DTL M1_INDXBILL_OM_RECHECK_DTL CONTAINER_NO 33115919 2483968 1
BILL_OM_RECHECK_DTL M1_INDXBILL_OM_RECHECK_DTL RECHECK_NO 33115919 1158912 2
BILL_OM_RECHECK_DTL M1_INDXBILL_OM_RECHECK_DTL LOCNO 33115919 120 3
BILL_OM_RECHECK_DTL PKBILL_OM_RECHECK_DTL CONTAINER_NO 33115919 2483968 1
BILL_OM_RECHECK_DTL PKBILL_OM_RECHECK_DTL RECHECK_NO 33115919 1158912 2
BILL_OM_RECHECK_DTL PKBILL_OM_RECHECK_DTL ROW_ID 33115919 5079 3
BILL_OM_RECHECK_DTL PKBILL_OM_RECHECK_DTL LOCNO 33115919 120 4

改前SQL信息

SQL> SET AUTOT TRACEONLY  -->仅显示执行计划和统计信息,执行SQL但不显示SQL 
SQL> set line 1000
SQL> set timing on
SQL>select d.item_no itemNo,
       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蓝色部分;
        根据sql逻辑<bill_om_deliver_dtl d   left join (select r.locno,....  from bill_om_recheck_dtl r  where r.locno = 'C4311' group by r.locno,....) sub 
        on sub.column_name = d.column_name and  where d.locno = 'C4311'   and d.deliver_no = 'C4311PL16050600014'>
        间接说明bill_om_recheck_dtl只取bill_om_deliver_dtl表过滤where d.locno = 'C4311'  and d.deliver_no = 'C4311PL16050600014'后且满足sub.column_name=d.column_name 匹配数据;
        没必要将bill_om_recheck_dtl不匹配'(不匹配的数据极多)'的数据扫描出后再与最外层bill_om_deliver_dtl主表 left on sub.column_name=d.column_name再将不匹配的过滤掉;
        所以完全可以将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等其他表再做一次数据核对匹配..
   2、【本sql最优方案】只要BILL_OM_RECHECK_DTL表group by 查询字段没有max..min/avg/count后且当做与外层bill_om_deliver_dtl主表做关联的字段的情况;
         完全可以将BILL_OM_RECHECK_DTL表挪到外层与主表关联..CBO会自己考虑量大小是否会走索引...
        ---优点:不需要将主表传入BILL_OM_RECHECK_DTL表做额外bill_om_deliver_dtl主表消耗;
        ---缺点1:功能最外层没有group by ...且分页求和的功能需要加distinct来去重....  
        ---缺点2:bill_om_deliver_dtl与BILL_OM_RECHECK_DTL 存在NL循环情况,所以量越大可能越慢 还不如方案1的快(线上有其他功能存在用方案2比方案1还慢的情况)
        若存在BILL_OM_RECHECK_DTL表group by 查询字段有max..min/avg/count 且count_字段当做与外层bill_om_deliver_dtl主表做关联的字段的情况,可以参考方案1进行优化解决慢的问题,或者根据逻辑改多层group by..;  
    3、也可以将BILL_OM_RECHECK_DTL子查询的group by 去掉改成distinct. oracle 可能会将外表自动和子查询关联走索引很快返回结果,若group by的话将不会....
    ****  如上三种均可以解决《select 表1 left join (select * from 表2 group by )on 表1.column_name=表2.column_name and 表1.选择性极好的字段=:B1》 子查询表2全扫慢的情况....
    ****  不过具体采用什么方案可以根据具体sql/量/等其他情况来定...
     

方案一、
查看关联后数据量
SQL> SELECT COUNT(1)  FROM bill_om_deliver_dtl d where d.locno = 'C4311' and d.deliver_no = 'C4311PL16050600014';
  COUNT(1)
----------
   51

SQL> SELECT COUNT(1) FROM  bill_om_recheck_dtl r where r.locno = 'C4311'; -->表总量 
32214049 
  COUNT(1)
----------
   1139474

SQL>  SELECT COUNT(1)
  2    FROM bill_om_recheck_dtl r
  3    join bill_om_deliver_dtl d
  4      on r.locno = d.locno
  5     and r.container_no = d.container_no
  6     and r.item_no = d.item_no
  7     and r.size_no = d.size_no
  8     and d.locno = 'C4311'
  9     and d.deliver_no = 'C4311PL16050600014'
10   where r.locno = 'C4311';
  COUNT(1)
----------
    51
SQL>
改后sql 执行情况
SQL>  select d.item_no itemNo,
       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
               join bill_om_deliver_dtl d
                 on r.locno = d.locno
                and r.container_no = d.container_no
                and r.item_no = d.item_no                                     -->改后部分
                and r.size_no = d.size_no
                and d.locno = 'C4311'
                and d.deliver_no = 'C4311PL16050600014'

              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:00.20


Execution Plan
----------------------------------------------------------
Plan hash value: 1497280285

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - 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"(+))
  22 - access("D"."LOCNO"='C4311' AND "D"."DELIVER_NO"='C4311PL16050600014')
       filter("D"."DELIVER_NO"='C4311PL16050600014')
  23 - access("R"."CONTAINER_NO"="D"."CONTAINER_NO" AND "R"."LOCNO"="D"."LOCNO")
       filter("R"."LOCNO"='C4311' AND "R"."LOCNO"="D"."LOCNO")
  24 - filter("R"."ITEM_NO"="D"."ITEM_NO" AND "R"."SIZE_NO"="D"."SIZE_NO")
  26 - access("from$_subquery$_005"."QCSJ_C000000000500002"="ST"."STORE_NO"(+))
  27 - access("I"."ITEM_NO"="from$_subquery$_005"."ITEM_NO")
  28 - filter("S"."SIZE_KIND"="I"."SIZE_KIND")


Statistics
----------------------------------------------------------
       1  recursive calls
       0  db block gets
     1877  consistent gets
       0  physical reads

       0  redo size
       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>


方案二、
改后sql 执行情况 
SQL> select d.item_no itemNo,
       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,
       r.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 bill_om_recheck_dtl r
    on r.locno = 'C4311'
   and r.locno = d.locno
   and r.recheck_no = l.recheck_no
   and r.container_no = d.container_no             --->更改部分
   and r.item_no = d.item_no
   and r.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,
          r.box_row_id,
          d.box_no,
          s.HCOL_NO
order by d.store_no, r.box_row_id, s.HCOL_NO;

51 rows selected.

Elapsed: 00:00:00.04


Execution Plan
----------------------------------------------------------
Plan hash value: 2193174591


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

   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"(+))
  19 - filter("R"."SIZE_NO"(+)="D"."SIZE_NO" AND "R"."ITEM_NO"(+)="D"."ITEM_NO")
  20 - access("R"."CONTAINER_NO"(+)="D"."CONTAINER_NO" AND "R"."RECHECK_NO"(+)="L"."RECHECK_NO" AND
           "R"."LOCNO"(+)='C4311')
       filter("R"."LOCNO"(+)="D"."LOCNO")
  21 - access("I"."ITEM_NO"="from$_subquery$_005"."ITEM_NO")
  22 - filter("S"."SIZE_KIND"="I"."SIZE_KIND")

Statistics
----------------------------------------------------------
       1  recursive calls
       0  db block gets
       1285  consistent gets
       0  physical reads

       0  redo size
       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> 

    本文是针对 《子查询和外层关系是left join,且子查询全扫》的优化;
 《Oracle not exist子查询全扫的优化针对子查询与外层关系是not exists的优化

【源于本人笔记】 若有书写错误,表达错误,请指正...



此条目发表在   SQL、SQL优化篇  分类目录。将固定连接加入收藏夹。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-2097783/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
擅长SQL编写及SQL优化,分析瓶颈,性能调优、故障处理,根据实际情况定制备份策略; 擅长编写脚本来实现自动化功能,600+SQL优化经验案例,为人热爱学习,喜欢钻研技术,对工作认真负责。

注册时间:2013-03-13

  • 博文量
    121
  • 访问量
    2305194