ITPub博客

首页 > 数据库 > Oracle > SQL优化-大表的回表,等价条件改写,半连接改写,改变表关联顺序

SQL优化-大表的回表,等价条件改写,半连接改写,改变表关联顺序

原创 Oracle 作者:墨雪-guoyan 时间:2017-12-06 15:38:05 0 删除 编辑


总结:这个sql的优化思路是:首先确认 问题出在 mmt表的索引所描再回表上,立马想到的是消除mmt的索引扫描,索引加了 hint(full(mmt))sql5s可运行出来;但是觉得这个sql 还有优化空间,于是改写sql,将半连接改成left join,目的是利用sql中的等价条件,想让mmt表过滤的数据多一些,由于只对主表汇总,索不考虑为null的问题
,改完之后觉得过滤数据应该没问题了,但是运行不出来,于是考虑关联顺序问题,既然mmt驱动表的时候很慢就将它换成被驱动表,于是加了 hint(/*+  use_nl(OOL) leading(OOL)*/ )sql 0.5s出

select * from table(dbms_xplan.display);
explain plan for

 --change before   run long long time
 
 SELECT 
   NVL(ABS(SUM(MMT.TRANSACTION_QUANTITY)), 0)
  FROM MTL_MATERIAL_TRANSACTIONS MMT, OE_ORDER_LINES_ALL OOL
 WHERE MMT.TRANSACTION_TYPE_ID = 33
   AND MMT.TRANSACTION_ACTION_ID = 1
   AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2
   AND MMT.TRX_SOURCE_LINE_ID = OOL.LINE_ID
   AND MMT.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID
   AND MMT.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID
   AND OOL.SOLD_TO_ORG_ID = 2057

   AND EXISTS
 (SELECT 1
          FROM CUX_OE_SALEFORECAST_ALL S
         WHERE OOL.ORIG_SYS_DOCUMENT_REF = S.PLANNING_NUMBER
           AND OOL.ORG_ID = S.ORG_ID
           AND ABS(MMT.TRANSACTION_QUANTITY) = ABS(S.PRIMARY_QUANTITY)
           AND TRUNC(NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE))
            BETWEEN  to_date('11/01/2017 00:00:00','MM/DD/YYYY  hh24:mi:ss')
             AND to_date('11/10/2017 00:00:00','MM/DD/YYYY  hh24:mi:ss')
           AND TRUNC(MMT.TRANSACTION_DATE) =
               NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE));
              
               Plan hash value: 226097174 --INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_DATE
              
                ---TRANSACTION_ACTION_ID, TRANSACTION_TYPE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_DATE
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                               |     1 |   126 |   635   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE                         |                               |     1 |   126 |            |          |
|   2 |   NESTED LOOPS                          |                               |     1 |   126 |   635   (2)| 00:00:01 |
|   3 |    NESTED LOOPS                         |                               |     9 |   126 |   635   (2)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                               |     9 |   675 |   622   (2)| 00:00:01 |
|   5 |      SORT UNIQUE                        |                               |     2 |    72 |     9   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS STORAGE FULL         | CUX_OE_SALEFORECAST_ALL       |     2 |    72 |     9   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED| MTL_MATERIAL_TRANSACTIONS     |     5 |   195 |   611   (2)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN                  | MTL_MATERIAL_TRANSACTIONS_N15 |   579 |       |   428   (3)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN                   | OE_ORDER_LINES_U1             |     1 |       |     1   (0)| 00:00:01 |
|* 10 |    TABLE ACCESS BY INDEX ROWID          | OE_ORDER_LINES_ALL            |     1 |    51 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO_DATE(' 2017-11-10
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO_DATE(' 2017-11-10
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - filter("TRX_SOURCE_LINE_ID" IS NOT NULL AND ABS("TRANSACTION_QUANTITY")=ABS("S"."PRIMARY_QUANTITY"))
   8 - access("TRANSACTION_ACTION_ID"=1 AND "TRANSACTION_TYPE_ID"=33 AND "TRANSACTION_SOURCE_TYPE_ID"=2)
       filter(TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))=NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))
   9 - access("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID")
  10 - filter("OOL"."SOLD_TO_ORG_ID"=2057 AND "INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID" AND
              "ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID" AND "OOL"."ORIG_SYS_DOCUMENT_REF"="S"."PLANNING_NUMBER" AND
              "OOL"."ORG_ID"="S"."ORG_ID")

 
-- add hint -5s
SELECT  /*+ full(MMT)*/
   NVL(ABS(SUM(MMT.TRANSACTION_QUANTITY)), 0)
  FROM MTL_MATERIAL_TRANSACTIONS MMT, OE_ORDER_LINES_ALL OOL
 WHERE MMT.TRANSACTION_TYPE_ID = 33
   AND MMT.TRANSACTION_ACTION_ID = 1
   AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2
   AND MMT.TRX_SOURCE_LINE_ID = OOL.LINE_ID
   AND MMT.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID
   AND MMT.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID
   AND OOL.SOLD_TO_ORG_ID = 2057

   AND EXISTS
 (SELECT 1
          FROM CUX_OE_SALEFORECAST_ALL S
         WHERE OOL.ORIG_SYS_DOCUMENT_REF = S.PLANNING_NUMBER
           AND OOL.ORG_ID = S.ORG_ID
           AND ABS(MMT.TRANSACTION_QUANTITY) = ABS(S.PRIMARY_QUANTITY)
           AND TRUNC(NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE))
            BETWEEN  to_date('11/01/2017 00:00:00','MM/DD/YYYY  hh24:mi:ss')
             AND to_date('11/10/2017 00:00:00','MM/DD/YYYY  hh24:mi:ss')
           AND TRUNC(MMT.TRANSACTION_DATE) =
               NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE));
              
               Plan hash value: 2114840721
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |     1 |   126 |   611K  (2)| 00:00:24 |
|   1 |  SORT AGGREGATE                |                           |     1 |   126 |            |          |
|   2 |   NESTED LOOPS                 |                           |     1 |   126 |   611K  (2)| 00:00:24 |
|   3 |    NESTED LOOPS                |                           |     9 |   126 |   611K  (2)| 00:00:24 |
|*  4 |     HASH JOIN                  |                           |     9 |   675 |   611K  (2)| 00:00:24 |
|   5 |      SORT UNIQUE               |                           |     2 |    72 |     9   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS STORAGE FULL| CUX_OE_SALEFORECAST_ALL   |     2 |    72 |     9   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS STORAGE FULL | MTL_MATERIAL_TRANSACTIONS | 47905 |  1824K|   611K  (2)| 00:00:24 |
|*  8 |     INDEX UNIQUE SCAN          | OE_ORDER_LINES_U1         |     1 |       |     1   (0)| 00:00:01 |
|*  9 |    TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL        |     1 |    51 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access(ABS("TRANSACTION_QUANTITY")=ABS("S"."PRIMARY_QUANTITY") AND
              TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))=NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))
   6 - storage(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=
              TO_DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=
              TO_DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - storage("TRANSACTION_TYPE_ID"=33 AND "TRANSACTION_SOURCE_TYPE_ID"=2 AND "TRX_SOURCE_LINE_ID"
              IS NOT NULL AND "TRANSACTION_ACTION_ID"=1)
       filter("TRANSACTION_TYPE_ID"=33 AND "TRANSACTION_SOURCE_TYPE_ID"=2 AND "TRX_SOURCE_LINE_ID"
              IS NOT NULL AND "TRANSACTION_ACTION_ID"=1)
   8 - access("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID")
   9 - filter("OOL"."SOLD_TO_ORG_ID"=2057 AND "INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID" AND
              "ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID" AND "OOL"."ORIG_SYS_DOCUMENT_REF"="S"."PLANNING_NUMBER"
              AND "OOL"."ORG_ID"="S"."ORG_ID")

 


--after change sql  0.5s
select * from table(dbms_xplan.display);
explain plan for
SELECT  /*+  use_nl(OOL) leading(OOL) */
   NVL(ABS(SUM(MMT.TRANSACTION_QUANTITY)), 0)
  FROM MTL_MATERIAL_TRANSACTIONS MMT, OE_ORDER_LINES_ALL OOL
   left join   CUX_OE_SALEFORECAST_ALL s on  OOL.ORIG_SYS_DOCUMENT_REF = S.PLANNING_NUMBER
   AND OOL.ORG_ID = S.ORG_ID
 WHERE MMT.TRANSACTION_TYPE_ID = 33
   AND MMT.TRANSACTION_ACTION_ID = 1
   AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2
   AND MMT.TRX_SOURCE_LINE_ID = OOL.LINE_ID
   AND MMT.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID
   AND MMT.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID
   AND OOL.SOLD_TO_ORG_ID = 2057
   AND ABS(MMT.TRANSACTION_QUANTITY) = ABS(S.PRIMARY_QUANTITY)
           AND TRUNC(NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE))
            BETWEEN  to_date('11/01/2017 00:00:00','MM/DD/YYYY  hh24:mi:ss')
             AND to_date('11/10/2017 00:00:00','MM/DD/YYYY  hh24:mi:ss')
           AND TRUNC(MMT.TRANSACTION_DATE) =
               NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE);
              
Plan hash value: 183599765
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |     1 |   126 |   229K  (1)| 00:00:09 |
|   1 |  SORT AGGREGATE               |                              |     1 |   126 |            |          |
|   2 |   NESTED LOOPS                |                              |     1 |   126 |   229K  (1)| 00:00:09 |
|   3 |    NESTED LOOPS               |                              |     1 |   126 |   229K  (1)| 00:00:09 |
|*  4 |     HASH JOIN                 |                              |     1 |    87 |   229K  (1)| 00:00:09 |
|*  5 |      TABLE ACCESS STORAGE FULL| OE_ORDER_LINES_ALL           |   132 |  6732 |   229K  (1)| 00:00:09 |
|*  6 |      TABLE ACCESS STORAGE FULL| CUX_OE_SALEFORECAST_ALL      |     2 |    72 |     9   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | MTL_MATERIAL_TRANSACTIONS_N1 |     1 |       |     3   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID| MTL_MATERIAL_TRANSACTIONS    |     1 |    39 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("OOL"."ORIG_SYS_DOCUMENT_REF"="S"."PLANNING_NUMBER" AND "OOL"."ORG_ID"="S"."ORG_ID")
   5 - storage("OOL"."SOLD_TO_ORG_ID"=2057)
       filter("OOL"."SOLD_TO_ORG_ID"=2057)
   6 - storage(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO
              _DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO
              _DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID" AND
              "ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID")
       filter(TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))=NVL("S"."RESERVATION_DATE_TO","S"."PLANNING
              _DATE"))
   8 - filter("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID" AND "TRANSACTION_TYPE_ID"=33 AND
              "TRANSACTION_SOURCE_TYPE_ID"=2 AND "TRX_SOURCE_LINE_ID" IS NOT NULL AND "TRANSACTION_ACTION_ID"=1 AND
              ABS("TRANSACTION_QUANTITY")=ABS("S"."PRIMARY_QUANTITY"));
              
              
           


再修改之后表的关联顺序发生了变化,使用了另外一个索引 N1,下面测试一下两个两个索引哪个相对性能好些;
   
          --/*+  use_nl(OOL) leading(OOL)index(MMT,MTL_MATERIAL_TRANSACTIONS_N15)*/ 
    
              
              
              Plan hash value: 3610194404
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                               |     1 |   126 |   230K  (1)| 00:00:10 |
|   1 |  SORT AGGREGATE               |                               |     1 |   126 |            |          |
|   2 |   NESTED LOOPS                |                               |     1 |   126 |   230K  (1)| 00:00:10 |
|   3 |    NESTED LOOPS               |                               |   579 |   126 |   230K  (1)| 00:00:10 |
|*  4 |     HASH JOIN                 |                               |     1 |    87 |   229K  (1)| 00:00:09 |
|*  5 |      TABLE ACCESS STORAGE FULL| OE_ORDER_LINES_ALL            |   132 |  6732 |   229K  (1)| 00:00:09 |
|*  6 |      TABLE ACCESS STORAGE FULL| CUX_OE_SALEFORECAST_ALL       |     2 |    72 |     9   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | MTL_MATERIAL_TRANSACTIONS_N15 |   579 |       |   428   (3)| 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID| MTL_MATERIAL_TRANSACTIONS     |     1 |    39 |   611   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("OOL"."ORIG_SYS_DOCUMENT_REF"="S"."PLANNING_NUMBER" AND "OOL"."ORG_ID"="S"."ORG_ID")
   5 - storage("OOL"."SOLD_TO_ORG_ID"=2057)
       filter("OOL"."SOLD_TO_ORG_ID"=2057)
   6 - storage(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO_
              DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO_
              DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("TRANSACTION_ACTION_ID"=1 AND "TRANSACTION_TYPE_ID"=33 AND
              "TRANSACTION_SOURCE_TYPE_ID"=2)
       filter(TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))=NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_
              DATE"))
   8 - filter("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID" AND "INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID"
              AND "ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID" AND "TRX_SOURCE_LINE_ID" IS NOT NULL AND
              ABS("TRANSACTION_QUANTITY")=ABS("S"."PRIMARY_QUANTITY"))

 

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

请登录后发表评论 登录
全部评论

注册时间:2013-02-18

  • 博文量
    14
  • 访问量
    68341