[quote]原帖由
tom_fans 于 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秒），很显然会很慢。
先看看计划：
-------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------
我的思路是：
既然是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
计划：
--------------------------------------------------------------------------------------------------
| 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
另外一条要等3个小时，我看不需要给结果在这里。
快在哪里呢？ 不过是nest loop换HASH 而已。这条SQL统计信息其实已经分析过来，好像还是信息还是有点不对，这里不用管它。
也许还有别的提高方法，你们可以发上来，我来try. [/quote]
我这里有个系统，每天会跑一个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秒），很显然会很慢。
先看看计划：
-------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------
我的思路是：
既然是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
计划：
--------------------------------------------------------------------------------------------------
| 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
另外一条要等3个小时，我看不需要给结果在这里。
快在哪里呢？ 不过是nest loop换HASH 而已。这条SQL统计信息其实已经分析过来，好像还是信息还是有点不对，这里不用管它。
也许还有别的提高方法，你们可以发上来，我来try. [/quote]
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24492954/viewspace-692183/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24492954/viewspace-692183/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
