一条SQL的优化，3个小时--->6秒到底什么在作怪？(tom_fans )

[quote]原帖由 tom_fans 于 2010-8-12 20:21 发表

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

-------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                 | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |     1 |   104 |    23 |
|   1 |  SORT GROUP BY                    |                       |     1 |   104 |    23 |
|   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 |
|   8 |      SORT UNIQUE                  |                       |  1530 | 24480 |  1067 |
|   9 |       UNION-ALL                   |                       |       |       |       |
|  10 |        VIEW                       |                       |  1529 | 24464 |  1056 |
|  11 |         INTERSECTION              |                       |       |       |       |
|  12 |          SORT UNIQUE              |                       |  1635 | 24525 |       |
|  13 |           INDEX FAST FULL SCAN    | TA_MDL_SELL_IN_P_I01  |  1635 | 24525 |   520 |
|  14 |          SORT UNIQUE              |                       |  1529 | 22935 |       |
|  15 |           INDEX FAST FULL SCAN    | TA_MDL_SELL_IN_P_I01  |  1529 | 22935 |   520 |
|  16 |        TABLE ACCESS BY INDEX ROWID| TA_MDL_DEF_FLG        |     1 |    16 |     2 |
|  17 |         INDEX RANGE SCAN          | PK_TA_MDL_DEF_FLG     |     1 |       |     1 |
-------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------------
| Id  | Operation                        |  Name                 | Rows  | Bytes |TempSpc| Cost  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                       |     1 |   121 |       | 15951 |
|   1 |  SORT GROUP BY                   |                       |     1 |   121 |       | 15951 |
|   2 |   HASH JOIN                      |                       |     7 |   847 |       | 15949 |
|   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                          |                       |   392K|  6511K|       | 15924 |
|  10 |     MINUS                        |                       |       |       |       |       |
|  11 |      SORT UNIQUE                 |                       |   392K|  5745K|    18M|       |
|  12 |       INDEX FAST FULL SCAN       | TA_MDL_SELL_IN_P_I01  |   392K|  5745K|       |   520 |
|  13 |      SORT UNIQUE                 |                       |     1 |    31 |       |       |
|  14 |       HASH JOIN                  |                       |  2085K|    61M|   848K|  2245 |
|  15 |        VIEW                      |                       | 30744 |   480K|       |  1445 |
|  16 |         SORT UNIQUE              |                       | 30744 |   480K|  1448K|  1445 |
|  17 |          UNION-ALL               |                       |       |       |       |       |
|  18 |           VIEW                   |                       | 30582 |   477K|       |  1305 |
|  19 |            INTERSECTION          |                       |       |       |       |       |
|  20 |             SORT UNIQUE          |                       | 32698 |   478K|  1560K|       |
|  21 |              INDEX FAST FULL SCAN| TA_MDL_SELL_IN_P_I01  | 32698 |   478K|       |   520 |
|  22 |             SORT UNIQUE          |                       | 30582 |   447K|  1448K|       |
|  23 |              INDEX FAST FULL SCAN| TA_MDL_SELL_IN_P_I01  | 30582 |   447K|       |   520 |
|  24 |           TABLE ACCESS FULL      | TA_MDL_DEF_FLG        |   162 |  2592 |       |     7 |
|  25 |        INDEX FAST FULL SCAN      | TA_MDL_SELL_IN_P_I01  |   392K|  5745K|       |   520 |
--------------------------------------------------------------------------------------------------

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

• 博文量
41
• 访问量
245418