查看: 5409|回复: 29

[性能调整] 一条SQL的优化,3个小时--->6秒到底什么在作怪?

[复制链接]
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-8-12 20:21 | 显示全部楼层 |阅读模式
我这里有个系统,每天会跑一个JOB,简单的说只有一条select,这个SELECT不知道多久就开始跑3个小时。至于为什么以前没优化,或者如何就不提了。

这条SQL是因为NOT IN慢。也许有太多not in 的优化方式,但是这次我要创新。我只是想表示一个观点,SQL没有规则可循,根本就不需要规则。SQL优化器的优化思路就是人本身的优化的思路。我从来不记优化器什么能做,什么不能做。也不会去刻意记住什么情况会用索引,什么情况不用。 我只记住nest loop. hash join, merge join的原理。

数据版本:9.2.0.4,跑CBO。

SQL原型:
SELECT '306400',      
       TA_MDL_SELL_IN_P.MDL_CD,      
       TO_CHAR(TA_MDL_SELL_IN_P.WEEK - 1, 'YYYYMMDD'),      
       SUM(NVL(TA_MDL_SELL_IN_P.PRD_REQ_QTY, 0)),      
       '1',      
       '0501'
  FROM TA_MDL_SELL_IN_P, TX_COMN_MDL_MST, TX_HRCH_MST
WHERE TA_MDL_SELL_IN_P.MDL_CD NOT IN
       (SELECT 'P' || A.MOD MDL_CD      
          FROM (SELECT SUBSTR(T.MDL_CD, 2, LENGTH(T.MDL_CD) - 1) MOD               
                  FROM TA_MDL_SELL_IN_P T               
                 WHERE T.MDL_CD LIKE 'P%'               
                INTERSECT               
                SELECT SUBSTR(P.MDL_CD, 2, LENGTH(P.MDL_CD) - 1) MOD               
                  FROM TA_MDL_SELL_IN_P P               
                 WHERE P.MDL_CD LIKE 'N%') A        
        UNION        
        SELECT DISTINCT TA_MDL_DEF_FLG.MDL_CD        
          FROM TA_MDL_DEF_FLG        
         WHERE TA_MDL_DEF_FLG.DISCON_FLG = '1')      

   AND TA_MDL_SELL_IN_P.PL_CD = TX_COMN_MDL_MST.PL_CD      
   AND TA_MDL_SELL_IN_P.KIND_CD = TX_COMN_MDL_MST.KIND_CD     
   AND TA_MDL_SELL_IN_P.MDL_CD = TX_COMN_MDL_MST.MDL_CD     
   AND TX_COMN_MDL_MST.FCST_MDL_CD IN (1, 2, 3, 4)      
   AND TX_COMN_MDL_MST.DLT_FLG = 0      
   AND TA_MDL_SELL_IN_P.WEEK > TO_DATE('一个变量', 'YYYYMMDD')      
   AND TA_MDL_SELL_IN_P.CHNL_CD = '1'      
   AND TX_COMN_MDL_MST.PL_CD = TX_HRCH_MST.PL_CD     
   AND TX_COMN_MDL_MST.KIND_CD = TX_HRCH_MST.KIND_CD     
   AND TX_COMN_MDL_MST.KIND_DTL_CD = TX_HRCH_MST.KIND_DTL_CD     
   AND TRIM(TX_HRCH_MST.CAT_CD) = trim(‘一个固定的变量‘)
GROUP BY TA_MDL_SELL_IN_P.MDL_CD, TA_MDL_SELL_IN_P.WEEK
ORDER BY TA_MDL_SELL_IN_P.MDL_CD, TA_MDL_SELL_IN_P.WEEK

这是一条标准的开发喜欢写的SQL类型,多看几眼就知道,其实这条SQL非常简单,只不过条件多了一些,表也不过4张。那么以前为什么跑三个小时?其实就是因为not in.

SQL> select count(*) from TA_MDL_SELL_IN_P;
  COUNT(*)
----------
    610194

SQL> select count(*) from  TX_COMN_MDL_MST
  2  ;
  COUNT(*)
----------
      9780


SQL> select count(*) from  TX_HRCH_MST;
  COUNT(*)
----------
       435


SQL> select count(*) from  TA_MDL_DEF_FLG;
  COUNT(*)
----------
      4513

最后得到数据是1W多条。

当开发把这条SQL给我的时候,给我的感觉就是SQL太简单,表也不大,计划也是标准的,所谓标准就是你看到这个SQL就知道大概怎么跑。仅仅是一个NOT IN不舒服,因为毕竟TA_MDL_SELL_IN_P有60W,过滤掉条件也有40W左右。40W去NOT IN 一个子查询(子查询大概每次要3秒),很显然会很慢。

先看看计划:
  1. -------------------------------------------------------------------------------------------
  2. | Id  | Operation                         |  Name                 | Rows  | Bytes | Cost  |
  3. -------------------------------------------------------------------------------------------
  4. |   0 | SELECT STATEMENT                  |                       |     1 |   104 |    23 |
  5. |   1 |  SORT GROUP BY                    |                       |     1 |   104 |    23 |
  6. |   2 |   TABLE ACCESS BY INDEX ROWID     | TA_MDL_SELL_IN_P      |     1 |    37 |     3 |
  7. |   3 |    NESTED LOOPS                   |                       |     1 |   104 |    21 |
  8. |   4 |     HASH JOIN                     |                       |     1 |    67 |    18 |
  9. |   5 |      TABLE ACCESS FULL            | TX_HRCH_MST           |    11 |   352 |     3 |
  10. |   6 |      TABLE ACCESS FULL            | TX_COMN_MDL_MST       |   730 | 25550 |    14 |
  11. |   7 |     INDEX RANGE SCAN              | PK_TA_MDL_SELL_IN_P   |     1 |       |     2 |
  12. |   8 |      SORT UNIQUE                  |                       |  1530 | 24480 |  1067 |
  13. |   9 |       UNION-ALL                   |                       |       |       |       |
  14. |  10 |        VIEW                       |                       |  1529 | 24464 |  1056 |
  15. |  11 |         INTERSECTION              |                       |       |       |       |
  16. |  12 |          SORT UNIQUE              |                       |  1635 | 24525 |       |
  17. |  13 |           INDEX FAST FULL SCAN    | TA_MDL_SELL_IN_P_I01  |  1635 | 24525 |   520 |
  18. |  14 |          SORT UNIQUE              |                       |  1529 | 22935 |       |
  19. |  15 |           INDEX FAST FULL SCAN    | TA_MDL_SELL_IN_P_I01  |  1529 | 22935 |   520 |
  20. |  16 |        TABLE ACCESS BY INDEX ROWID| TA_MDL_DEF_FLG        |     1 |    16 |     2 |
  21. |  17 |         INDEX RANGE SCAN          | PK_TA_MDL_DEF_FLG     |     1 |       |     1 |
  22. -------------------------------------------------------------------------------------------
复制代码


我的思路是:

既然是NOT IN,那就是要TA_MDL_SELL_IN_P.MDL_CD不在子查询里的数据才要。如果这个SQL是我写,我肯定也会写NOT IN,最简单。
为什么不能把思路掉回来呢? 既然你要查询不在的,我就先查询TA_MDL_SELL_IN_P.MDL_CD包含在子查询的,那很显然就会变成等值查询,走HASH肯定不会慢。

找到了包含的数据,再通过TA_MDL_SELL_IN_P和这个临时数据做MINUS取出差值。这和直接用NOT IN是同一个道理。

修改后的SQL:

SELECT '306400',      
       E.MDL_CD,      
       TO_CHAR(E.WEEK - 1, 'YYYYMMDD'),      
       SUM(NVL(E.PRD_REQ_QTY, 0)),      
       '1',      
       '0501'
  from (select A.*
          from (select TA_MDL_SELL_IN_P.MDL_CD      MDL_CD,
                        TA_MDL_SELL_IN_P.week        week,
                        TA_MDL_SELL_IN_P.PRD_REQ_QTY PRD_REQ_QTY
                   FROM TA_MDL_SELL_IN_P, TX_COMN_MDL_MST, TX_HRCH_MST                 
                  WHERE                 
                  TA_MDL_SELL_IN_P.PL_CD = TX_COMN_MDL_MST.PL_CD                 
               AND TA_MDL_SELL_IN_P.KIND_CD = TX_COMN_MDL_MST.KIND_CD               
               AND TA_MDL_SELL_IN_P.MDL_CD = TX_COMN_MDL_MST.MDL_CD                 
               AND TX_COMN_MDL_MST.FCST_MDL_CD IN (1, 2, 3, 4)                 
               AND TX_COMN_MDL_MST.DLT_FLG = 0                 
               AND TA_MDL_SELL_IN_P.WEEK > TO_DATE('20100809', 'YYYYMMDD')               
               AND TA_MDL_SELL_IN_P.CHNL_CD = '1'               
               AND TX_COMN_MDL_MST.PL_CD = TX_HRCH_MST.PL_CD               
               AND TX_COMN_MDL_MST.KIND_CD = TX_HRCH_MST.KIND_CD                 
               AND TX_COMN_MDL_MST.KIND_DTL_CD = TX_HRCH_MST.KIND_DTL_CD                 
               AND TRIM(TX_HRCH_MST.CAT_CD) = trim('VAIO')) A,               
               (select C.MDL_CD MDL_CD
                  from TA_MDL_SELL_IN_P C
                minus (select distinct A.MDL_CD
                        from TA_MDL_SELL_IN_P A,
                             (SELECT 'P' || A.MOD MDL_CD
                                FROM (SELECT SUBSTR(T.MDL_CD,
                                                    2,
                                                    LENGTH(T.MDL_CD) - 1) MOD
                                        FROM TA_MDL_SELL_IN_P T
                                       WHERE T.MDL_CD LIKE 'P%'
                                      INTERSECT
                                      SELECT SUBSTR(P.MDL_CD,
                                                    2,
                                                    LENGTH(P.MDL_CD) - 1) MOD
                                        FROM TA_MDL_SELL_IN_P P
                                       WHERE P.MDL_CD LIKE 'N%') A
                              UNION
                              SELECT DISTINCT TA_MDL_DEF_FLG.MDL_CD
                                FROM TA_MDL_DEF_FLG
                               WHERE TA_MDL_DEF_FLG.DISCON_FLG = '1') B
                       where A.MDL_CD = B.MDL_CD))
D
         where A.mdl_cd = D.mdl_cd) E
GROUP BY E.MDL_CD, E.WEEK
ORDER BY E.MDL_CD, E.WEEK

变换后就等于是NOT IN换成2个等值连接+MINUS

计划:
  1. --------------------------------------------------------------------------------------------------
  2. | Id  | Operation                        |  Name                 | Rows  | Bytes |TempSpc| Cost  |
  3. --------------------------------------------------------------------------------------------------
  4. |   0 | SELECT STATEMENT                 |                       |     1 |   121 |       | 15951 |
  5. |   1 |  SORT GROUP BY                   |                       |     1 |   121 |       | 15951 |
  6. |   2 |   HASH JOIN                      |                       |     7 |   847 |       | 15949 |
  7. |   3 |    TABLE ACCESS BY INDEX ROWID   | TA_MDL_SELL_IN_P      |     3 |   111 |       |     3 |
  8. |   4 |     NESTED LOOPS                 |                       |     1 |   104 |       |    21 |
  9. |   5 |      HASH JOIN                   |                       |     1 |    67 |       |    18 |
  10. |   6 |       TABLE ACCESS FULL          | TX_HRCH_MST           |    11 |   352 |       |     3 |
  11. |   7 |       TABLE ACCESS FULL          | TX_COMN_MDL_MST       |   730 | 25550 |       |    14 |
  12. |   8 |      INDEX RANGE SCAN            | PK_TA_MDL_SELL_IN_P   |     1 |       |       |     2 |
  13. |   9 |    VIEW                          |                       |   392K|  6511K|       | 15924 |
  14. |  10 |     MINUS                        |                       |       |       |       |       |
  15. |  11 |      SORT UNIQUE                 |                       |   392K|  5745K|    18M|       |
  16. |  12 |       INDEX FAST FULL SCAN       | TA_MDL_SELL_IN_P_I01  |   392K|  5745K|       |   520 |
  17. |  13 |      SORT UNIQUE                 |                       |     1 |    31 |       |       |
  18. |  14 |       HASH JOIN                  |                       |  2085K|    61M|   848K|  2245 |
  19. |  15 |        VIEW                      |                       | 30744 |   480K|       |  1445 |
  20. |  16 |         SORT UNIQUE              |                       | 30744 |   480K|  1448K|  1445 |
  21. |  17 |          UNION-ALL               |                       |       |       |       |       |
  22. |  18 |           VIEW                   |                       | 30582 |   477K|       |  1305 |
  23. |  19 |            INTERSECTION          |                       |       |       |       |       |
  24. |  20 |             SORT UNIQUE          |                       | 32698 |   478K|  1560K|       |
  25. |  21 |              INDEX FAST FULL SCAN| TA_MDL_SELL_IN_P_I01  | 32698 |   478K|       |   520 |
  26. |  22 |             SORT UNIQUE          |                       | 30582 |   447K|  1448K|       |
  27. |  23 |              INDEX FAST FULL SCAN| TA_MDL_SELL_IN_P_I01  | 30582 |   447K|       |   520 |
  28. |  24 |           TABLE ACCESS FULL      | TA_MDL_DEF_FLG        |   162 |  2592 |       |     7 |
  29. |  25 |        INDEX FAST FULL SCAN      | TA_MDL_SELL_IN_P_I01  |   392K|  5745K|       |   520 |
  30. --------------------------------------------------------------------------------------------------
复制代码


SQL> set timing on
SQL> select count(*) from (SELECT '306400',      
  2         E.MDL_CD,      
  3         TO_CHAR(E.WEEK - 1, 'YYYYMMDD'),      
  4         SUM(NVL(E.PRD_REQ_QTY, 0)),      
  5         '1',      
  6         '0501'
  7    from (select A.*
  8            from (select TA_MDL_SELL_IN_P.MDL_CD      MDL_CD,
  9                          TA_MDL_SELL_IN_P.week        week,
10                          TA_MDL_SELL_IN_P.PRD_REQ_QTY PRD_REQ_QTY
11                     FROM TA_MDL_SELL_IN_P, TX_COMN_MDL_MST, TX_HRCH_MST                 
12                    WHERE                 
13                    TA_MDL_SELL_IN_P.PL_CD = TX_COMN_MDL_MST.PL_CD                 
14                 AND TA_MDL_SELL_IN_P.KIND_CD = TX_COMN_MDL_MST.KIND_CD               
15                 AND TA_MDL_SELL_IN_P.MDL_CD = TX_COMN_MDL_MST.MDL_CD                 
16                 AND TX_COMN_MDL_MST.FCST_MDL_CD IN (1, 2, 3, 4)                 
17                 AND TX_COMN_MDL_MST.DLT_FLG = 0                 
18                 AND TA_MDL_SELL_IN_P.WEEK > TO_DATE('20100809', 'YYYYMMDD')               
19                 AND TA_MDL_SELL_IN_P.CHNL_CD = '1'               
20                 AND TX_COMN_MDL_MST.PL_CD = TX_HRCH_MST.PL_CD               
21                 AND TX_COMN_MDL_MST.KIND_CD = TX_HRCH_MST.KIND_CD                 
               AND TX_COMN_MDL_MST.KIND_DTL_CD = TX_HRCH_MST.KIND_DTL_CD                 
               AND TRIM(TX_HRCH_MST.CAT_CD) = trim('VAIO')) A,               
               (select C.MDL_CD MDL_CD
                  from TA_MDL_SELL_IN_P C
                minus (select distinct A.MDL_CD
                        from TA_MDL_SELL_IN_P A,
                             (SELECT 'P' || A.MOD MDL_CD
                                FROM (SELECT SUBSTR(T.MDL_CD,
                                                    2,
                                                    LENGTH(T.MDL_CD) - 1) MOD
                                        FROM TA_MDL_SELL_IN_P T
                                       WHERE T.MDL_CD LIKE 'P%'
                                      INTERSECT
                                      SELECT SUBSTR(P.MDL_CD,
                                                    2,
                                                    22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   LENGTH(P.MDL_CD) - 1) MOD
                                        FROM TA_MDL_SELL_IN_P P
                                       WHERE P.MDL_CD LIKE 'N%') A
                              UNION
                              SELECT DISTINCT TA_MDL_DEF_FLG.MDL_CD
                                FROM TA_MDL_DEF_FLG
                               WHERE TA_MDL_DEF_FLG.DISCON_FLG = '1') B
                       where A.MDL_CD = B.MDL_CD)) D
         where A.mdl_cd = D.mdl_cd) E
GROUP BY E.MDL_CD, E.WEEK
ORDER BY E.MDL_CD, E.WEEK)
38   39   40   41   42   43   44   45   46   47   48  ;
  COUNT(*)
----------
     12516
Elapsed: 00:00:06.50

另外一条要等3个小时,我看不需要给结果在这里。

快在哪里呢? 不过是nest loop换HASH 而已。这条SQL统计信息其实已经分析过来,好像还是信息还是有点不对,这里不用管它。

也许还有别的提高方法,你们可以发上来,我来try.
论坛徽章:
1
2009日食纪念
日期:2009-07-22 09:30:00
发表于 2010-8-12 20:41 | 显示全部楼层
如果是我的话,会考虑创建索引,因为有两个全表扫描,一般不会考虑改语句,因为怕提高了性能但是牺牲了功能,呵呵

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
 楼主| 发表于 2010-8-12 20:48 | 显示全部楼层
你是指:
  TABLE ACCESS FULL            | TX_HRCH_MST         
   TABLE ACCESS FULL            | TX_COMN_MDL_MST

这里吗?

你认为这是关键吗?慢的地方不是这个地方,如果是,那么下面的SQL同样是FULL SCAN, 还有之所以不用索引你可以看看为什么。 建了索引也不一定用的上

[ 本帖最后由 tom_fans 于 2010-8-12 20:50 编辑 ]

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-8-12 20:56 | 显示全部楼层
SELECT '306400',      
       TA_MDL_SELL_IN_P.MDL_CD,      
       TO_CHAR(TA_MDL_SELL_IN_P.WEEK - 1, 'YYYYMMDD'),      
       SUM(NVL(TA_MDL_SELL_IN_P.PRD_REQ_QTY, 0)),      
       '1',      
       '0501'
  FROM TA_MDL_SELL_IN_P, TX_COMN_MDL_MST, TX_HRCH_MST
WHERE TA_MDL_SELL_IN_P.MDL_CD NOT IN
      (select /*+ hash_aj no_merge(A) */ MDL_CD from (SELECT 'P' || A.MOD MDL_CD      
          FROM (SELECT SUBSTR(T.MDL_CD, 2, LENGTH(T.MDL_CD) - 1) MOD               
                  FROM TA_MDL_SELL_IN_P T               
                 WHERE T.MDL_CD LIKE 'P%'               
                INTERSECT               
                SELECT SUBSTR(P.MDL_CD, 2, LENGTH(P.MDL_CD) - 1) MOD               
                  FROM TA_MDL_SELL_IN_P P               
                 WHERE P.MDL_CD LIKE 'N%') A        
        UNION        
        SELECT DISTINCT TA_MDL_DEF_FLG.MDL_CD        
          FROM TA_MDL_DEF_FLG        
         WHERE TA_MDL_DEF_FLG.DISCON_FLG = '1')  t where t.MDL_CD is not null)   
   and TA_MDL_SELL_IN_P.MDL_CD is not null  
   AND TA_MDL_SELL_IN_P.PL_CD = TX_COMN_MDL_MST.PL_CD      
   AND TA_MDL_SELL_IN_P.KIND_CD = TX_COMN_MDL_MST.KIND_CD     
   AND TA_MDL_SELL_IN_P.MDL_CD = TX_COMN_MDL_MST.MDL_CD     
   AND TX_COMN_MDL_MST.FCST_MDL_CD IN (1, 2, 3, 4)      
   AND TX_COMN_MDL_MST.DLT_FLG = 0      
   AND TA_MDL_SELL_IN_P.WEEK > TO_DATE('一个变量', 'YYYYMMDD')      
   AND TA_MDL_SELL_IN_P.CHNL_CD = '1'      
   AND TX_COMN_MDL_MST.PL_CD = TX_HRCH_MST.PL_CD     
   AND TX_COMN_MDL_MST.KIND_CD = TX_HRCH_MST.KIND_CD     
   AND TX_COMN_MDL_MST.KIND_DTL_CD = TX_HRCH_MST.KIND_DTL_CD     
   AND TRIM(TX_HRCH_MST.CAT_CD) = trim(‘一个固定的变量‘)
GROUP BY TA_MDL_SELL_IN_P.MDL_CD, TA_MDL_SELL_IN_P.WEEK
ORDER BY TA_MDL_SELL_IN_P.MDL_CD, TA_MDL_SELL_IN_P.WEEK
;

看看执行计划

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-8-12 21:09 | 显示全部楼层
统计信息问题比较大

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-8-12 21:22 | 显示全部楼层
|   2 |   TABLE ACCESS BY INDEX ROWID     | TA_MDL_SELL_IN_P      |     1 |    37 |     3 |
|   3 |    NESTED LOOPS                   |                       |    1 |   104 |    21 |
|   4 |     HASH JOIN                     |                       |     1 |    67 |    18 |
|   5 |      TABLE ACCESS FULL            | TX_HRCH_MST           |    11 |   352 |     3 |
|   6 |      TABLE ACCESS FULL            | TX_COMN_MDL_MST       |   730 | 25550 |    14 |
|   7 |     INDEX RANGE SCAN              | PK_TA_MDL_SELL_IN_P   |     1 |       |     2 |

这里第2步红字部分导致了与not in 子句走了一种类似nest loop的方式

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-8-12 21:25 | 显示全部楼层
后来的执行计划里cardinality发生了变化:

|   3 |    TABLE ACCESS BY INDEX ROWID   | TA_MDL_SELL_IN_P      |     3 |   111 |       |     3 |
|   4 |     NESTED LOOPS                 |                       |     1 |   104 |       |    21 |
|   5 |      HASH JOIN                   |                       |     1 |    67 |       |    18 |
|   6 |       TABLE ACCESS FULL          | TX_HRCH_MST           |    11 |   352 |       |     3 |
|   7 |       TABLE ACCESS FULL          | TX_COMN_MDL_MST       |   730 | 25550 |       |    14 |
|   8 |      INDEX RANGE SCAN            | PK_TA_MDL_SELL_IN_P   |     1 |       |       |     2 |

使用道具 举报

回复
论坛徽章:
1
2009日食纪念
日期:2009-07-22 09:30:00
发表于 2010-8-12 21:27 | 显示全部楼层
如果不能修改语句的话怎么办呢?试试这个
create index idx_tmsip_001
on  TA_MDL_SELL_IN_P(MDL_CD ,PL_CD ,KIND_CD ,CHNL_CD ,WEEK );

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-8-12 21:43 | 显示全部楼层
多字段具有相关性时选择性的评估是oracle CBO的一个软肋
db2 v8开始通过列组统计信息来解决这一问题的
oracle 11G才利用扩展统计信息来应对这一问题
如果你表的统计信息都没严重问题
那么你这里多列组合后最后评估出的cardinality成了1可能就是因为这个了

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
 楼主| 发表于 2010-8-12 22:22 | 显示全部楼层

回复 #4 棉花糖ONE 的帖子

---------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                 | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |     1 |   120 |  1095 |
|   1 |  SORT GROUP BY                |                       |     1 |   120 |  1095 |
|   2 |   HASH JOIN ANTI              |                       |     1 |   120 |  1093 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TA_MDL_SELL_IN_P      |     3 |   111 |     3 |
|   4 |     NESTED LOOPS              |                       |     1 |   104 |    21 |
|   5 |      HASH JOIN                |                       |     1 |    67 |    18 |
|   6 |       TABLE ACCESS FULL       | TX_HRCH_MST           |    11 |   352 |     3 |
|   7 |       TABLE ACCESS FULL       | TX_COMN_MDL_MST       |   730 | 25550 |    14 |
|   8 |      INDEX RANGE SCAN         | PK_TA_MDL_SELL_IN_P   |     1 |       |     2 |
|   9 |    VIEW                       |                       |  1691 | 27056 |  1072 |
|  10 |     SORT UNIQUE               |                       |  1691 | 27056 |  1072 |
|  11 |      UNION-ALL                |                       |       |       |       |
|  12 |       VIEW                    |                       |  1529 | 24464 |  1056 |
|  13 |        INTERSECTION           |                       |       |       |       |
|  14 |         SORT UNIQUE           |                       |  1635 | 24525 |       |
|  15 |          INDEX FAST FULL SCAN | TA_MDL_SELL_IN_P_I01  |  1635 | 24525 |   520 |
|  16 |         SORT UNIQUE           |                       |  1529 | 22935 |       |
|  17 |          INDEX FAST FULL SCAN | TA_MDL_SELL_IN_P_I01  |  1529 | 22935 |   520 |
|  18 |       TABLE ACCESS FULL       | TA_MDL_DEF_FLG        |   162 |  2592 |     7 |
---------------------------------------------------------------------------------------

这条计划的顶端的HASH就是我想要的,可惜ORACLE不能自己走。
SQL> select count(*) from (SELECT '306400',      
  2         TA_MDL_SELL_IN_P.MDL_CD,      
  3         TO_CHAR(TA_MDL_SELL_IN_P.WEEK - 1, 'YYYYMMDD'),      
  4         SUM(NVL(TA_MDL_SELL_IN_P.PRD_REQ_QTY, 0)),      
  5         '1',      
  6         '0501'
  FROM TA_MDL_SELL_IN_P, TX_COMN_MDL_MST, TX_HRCH_MST
  7    8  WHERE TA_MDL_SELL_IN_P.MDL_CD NOT IN
  9        (select /*+ hash_aj no_merge(A) */ MDL_CD from (SELECT 'P' || A.MOD MDL_CD      
10            FROM (SELECT SUBSTR(T.MDL_CD, 2, LENGTH(T.MDL_CD) - 1) MOD               
11                    FROM TA_MDL_SELL_IN_P T               
                 WHERE T.MDL_CD LIKE 'P%'               
                INTERSECT               
12   13   14                  SELECT SUBSTR(P.MDL_CD, 2, LENGTH(P.MDL_CD) - 1) MOD               
                  FROM TA_MDL_SELL_IN_P P               
15   16                   WHERE P.MDL_CD LIKE 'N%') A        
17          UNION        
18          SELECT DISTINCT TA_MDL_DEF_FLG.MDL_CD        
          FROM TA_MDL_DEF_FLG        
19   20           WHERE TA_MDL_DEF_FLG.DISCON_FLG = '1')  t where t.MDL_CD is not null)   
   and TA_MDL_SELL_IN_P.MDL_CD is not null  
21   22     AND TA_MDL_SELL_IN_P.PL_CD = TX_COMN_MDL_MST.PL_CD      
23     AND TA_MDL_SELL_IN_P.KIND_CD = TX_COMN_MDL_MST.KIND_CD     
24     AND TA_MDL_SELL_IN_P.MDL_CD = TX_COMN_MDL_MST.MDL_CD     
25     AND TX_COMN_MDL_MST.FCST_MDL_CD IN (1, 2, 3, 4)      
26     AND TX_COMN_MDL_MST.DLT_FLG = 0      
27     AND TA_MDL_SELL_IN_P.WEEK > TO_DATE('20100809', 'YYYYMMDD')      
28     AND TA_MDL_SELL_IN_P.CHNL_CD = '1'      
   AND TX_COMN_MDL_MST.PL_CD = TX_HRCH_MST.PL_CD     
29   30     AND TX_COMN_MDL_MST.KIND_CD = TX_HRCH_MST.KIND_CD     
31     AND TX_COMN_MDL_MST.KIND_DTL_CD = TX_HRCH_MST.KIND_DTL_CD     
32     AND TRIM(TX_HRCH_MST.CAT_CD) = trim('VAIO')
33  GROUP BY TA_MDL_SELL_IN_P.MDL_CD, TA_MDL_SELL_IN_P.WEEK
34  ORDER BY TA_MDL_SELL_IN_P.MDL_CD, TA_MDL_SELL_IN_P.WEEK)
35  ;

  COUNT(*)
----------
     12516

Elapsed: 00:00:07.14

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表