|
일전에 문의드려 아래와 같이 1) 번 쿼리를 2)번 쿼리와 같이 수정해서 더 낳은 실행계획이 보였는데 막상 실행해보면 시간이 오히려 조금 더 걸립니다.
해당 쿼리만 실행하면 분명히 2)번 쿼리시간 더 빠릅니다.
그런데 아래 쿼리는 WITH 절에 사용된 쿼리로 다른 쿼리에서 여러번 반복되어 사용이 되어집니다. 전체를 돌려보면 더 오래 걸리네요.
어떤 부분때문에 이런현상이 나타나는건가요?
1)번 쿼리 ===================================================================================================================================
SELECT DISTINCT FTO GFM
, SUBSTR(BID,1,18) ITEM
, NVL(CQ,1) MQ
FROM MM1
WHERE (SUBSTR(BID,1,18), FTO)
IN (SELECT SUBSTR(BID,1,18) ITEM
, MAX(FTO) GFM
FROM MM1
WHERE ENAME ='MT'
AND (FTO, CONID)
IN (SELECT GFM,STID
FROM MG1
WHERE PNID = '201136'
AND AR = 'WARE'
AND GQY > 0
)
GROUP BY SUBSTR(BID,1,18)
)
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 2255 | 50 |00:00:00.38 | 909 | 1012K| 1012K| |
|* 2 | HASH JOIN | | 1 | 2255 | 15450 |00:00:00.36 | 909 | 741K| 741K| 1224K (0)|
| 3 | VIEW | VW_NSO_1 | 1 | 2417 | 2139 |00:00:00.34 | 721 | | | |
| 4 | HASH GROUP BY | | 1 | 2417 | 2139 |00:00:00.34 | 721 | 805K| 805K| 1143K (0)|
|* 5 | HASH JOIN | | 1 | 2417 | 196K|00:00:00.03 | 721 | 993K| 993K| 1522K (0)|
|* 6 | TABLE ACCESS BY INDEX ROWID| MG1 | 1 | 7719 | 6703 |00:00:00.02 | 533 | | | |
|* 7 | INDEX RANGE SCAN | PK_MG1 | 1 | 7722 | 12150 |00:00:00.01 | 106 | | | |
|* 8 | TABLE ACCESS FULL | MM1 | 1 | 8212 | 16174 |00:00:00.01 | 188 | | | |
| 9 | TABLE ACCESS FULL | MM1 | 1 | 16423 | 16468 |00:00:00.01 | 188 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM"=SUBSTR("BID",1,18) AND "FTO"="GFM")
5 - access("FTO"="GFM" AND "CONID"="STID")
6 - filter("GQY">0)
7 - access("PNID"='201136' AND "AR"='WARE')
8 - filter("ENAME"='MT')
2)번 쿼리 ===================================================================================================================================
SELECT MAX(FTO) GFM
, SUBSTR(BID, 1, 18) ITEM
, NVL(MAX(CQ) KEEP(DENSE_RANK LAST ORDER BY FTO), 1) MQ
FROM MM1
WHERE ENAME = 'MT'
AND (FTO, CONID) IN (SELECT GFM,STID
FROM MG1
WHERE PNID = '201136'
AND AR = 'WARE'
AND GQY > 0
)
GROUP BY SUBSTR(BID, 1, 18);
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT GROUP BY | | 1 | 1 | 50 |00:00:00.09 | 721 | 549K| 549K| 487K (0)|
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 1 | 14896 |00:00:00.05 | 721 | 993K| 993K| 1490K (0)|
|* 3 | TABLE ACCESS BY INDEX ROWID| MG1 | 1 | 7719 | 6703 |00:00:00.03 | 533 | | | |
|* 4 | INDEX RANGE SCAN | PK_MG1 | 1 | 7722 | 12150 |00:00:00.01 | 106 | | | |
|* 5 | TABLE ACCESS FULL | MM1 | 1 | 8212 | 16174 |00:00:00.01 | 188 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FTO"="GFM" AND "CONID"="STID")
3 - filter("GQY">0)
4 - access("PNID"='201136' AND "AR"='WARE')
5 - filter("ENAME"='MT')
|
