데이터베이스 질문과 답변
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d7v4pcsu07b4s, child number 1
-------------------------------------
SELECT p.* FROM ( SELECT Z.*,ROWNUM AS RNUM FROM ( SELECT /*+ INDEX_DESC ( CH MON_CWL_DATE_IX1) */ CM.CRAWL_ID,
CM.SITE_ID, BS.SITE_NAME, CM.POST_ID, CM.POST_NAME, CM.POST_GENRE, CM.FILE_COUNT,
CM.FILE_SIZE, CH.CWL_PRICE, CM.UPLOADER_ID, CH.CWL_DATE, CM.DESC_URL, CH.CWL_STATUS,
CH.CWL_FILE_PATH, NVL(CMP.SYNC_RATE,UMP.SYNC_RATE) AS SYNC_RATE , NVL(CMP.PRE_JUDGE,UMP.PRE_JUDGE) AS PRE_JUDGE ,
NVL(CMP.ORD,UMP.ORD) ORD FROM (SELECT /*+ INDEX_FFS ( CMP IDX_CRAWL_ID_CONT_IDX1) */ CMP.CRAWL_ID, MAX (CMP.SYNC_RATE) SYNC_RATE,CMP.PRE_JUDGE,
'C' AS ORD FROM TBMO_CONTENT_MAPPING CMP GROUP BY CMP.CRAWL_ID,CMP.PRE_JUDGE )CMP, TBMO_CRAWL_MASTER CM,
TBMO_CRAWL_HIST CH, (SELECT /*+ INDEX_FFS ( UMP IDX_CRAWL_ID_UP_IDX1) */
Plan hash value: 1017945315
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 20 | 20 |00:00:03.41 | 94394 | 16 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:03.41 | 94394 | 16 | | | |
| 3 | VIEW | | 1 | 17262 | 20 |00:00:03.41 | 94394 | 16 | | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 17262 | 20 |00:00:03.41 | 94394 | 16 | 974K| 535K| 1/0/0|
|* 5 | HASH JOIN RIGHT OUTER | | 1 | 17262 | 85193 |00:00:03.31 | 94394 | 16 | 1224K| 1153K| 1/0/0|
| 6 | VIEW | | 1 | 5331 | 7739 |00:00:00.02 | 99 | 0 | | | |
| 7 | HASH GROUP BY | | 1 | 5331 | 7739 |00:00:00.02 | 99 | 0 | 1289K| 1086K| 1/0/0|
| 8 | TABLE ACCESS FULL | TBMO_UPLOAD_MAPPING | 1 | 5331 | 7818 |00:00:00.01 | 99 | 0 | | | |
|* 9 | HASH JOIN | | 1 | 17262 | 85193 |00:00:03.10 | 94295 | 16 | 990K| 990K| 1/0/0|
| 10 | TABLE ACCESS FULL | TBMO_BASIC_SITE | 1 | 61 | 61 |00:00:00.01 | 7 | 0 | | | |
|* 11 | HASH JOIN RIGHT OUTER | | 1 | 17262 | 85193 |00:00:03.01 | 94288 | 16 | 4392K| 1153K| 1/0/0|
| 12 | VIEW | | 1 | 57866 | 55183 |00:00:01.78 | 16581 | 3 | | | |
| 13 | HASH GROUP BY | | 1 | 57866 | 55183 |00:00:01.72 | 16581 | 3 | 5901K| 2172K| 1/0/0|
| 14 | TABLE ACCESS FULL | TBMO_CONTENT_MAPPING | 1 | 757K| 1021K|00:00:00.01 | 16581 | 3 | | | |
|* 15 | HASH JOIN | | 1 | 17262 | 79386 |00:00:01.00 | 77707 | 13 | 12M| 1999K| 1/0/0|
| 16 | PARTITION RANGE SINGLE | | 1 | 16882 | 79388 |00:00:00.48 | 74037 | 12 | | | |
|* 17 | TABLE ACCESS BY LOCAL INDEX ROWID| TBMO_CRAWL_HIST | 1 | 16882 | 79388 |00:00:00.40 | 74037 | 12 | | | |
|* 18 | INDEX RANGE SCAN DESCENDING | MON_CWL_DATE_IX1 | 1 | 17332 | 82844 |00:00:00.13 | 1530 | 0 | | | |
| 19 | PARTITION RANGE ALL | | 1 | 83988 | 113K|00:00:00.11 | 3670 | 1 | | | |
| 20 | TABLE ACCESS FULL | TBMO_CRAWL_MASTER | 5 | 83988 | 113K|00:00:00.01 | 3670 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=1)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
5 - access("CH"."CRAWL_ID"="UMP"."CRAWL_ID")
9 - access("CM"."SITE_ID"="BS"."SITE_ID")
11 - access("CMP"."CRAWL_ID"="CM"."CRAWL_ID")
15 - access("CM"."CRAWL_ID"="CH"."CRAWL_ID")
17 - filter("CH"."CWL_STATUS"='CRAWLS00')
18 - access("CH"."CWL_DATE"<='20151125235959' AND "CH"."CWL_DATE">='20151124000000')
filter(("CH"."CWL_DATE"<='20151125235959' AND "CH"."CWL_DATE">='20151124000000'))
SELECT p.*
FROM ( SELECT Z.*,ROWNUM AS RNUM
FROM (
SELECT /*+ INDEX_DESC ( CH MON_CWL_DATE_IX1) */
CM.CRAWL_ID,
CM.SITE_ID,
BS.SITE_NAME,
CM.POST_ID,
CM.POST_NAME,
CM.POST_GENRE,
CM.FILE_COUNT,
CM.FILE_SIZE,
CH.CWL_PRICE,
CM.UPLOADER_ID,
CH.CWL_DATE,
CM.DESC_URL,
CH.CWL_STATUS,
CH.CWL_FILE_PATH,
NVL(CMP.SYNC_RATE,UMP.SYNC_RATE) AS SYNC_RATE ,
NVL(CMP.PRE_JUDGE,UMP.PRE_JUDGE) AS PRE_JUDGE ,
NVL(CMP.ORD,UMP.ORD) ORD
FROM (SELECT /*+ INDEX_FFS ( CMP IDX_CRAWL_ID_CONT_IDX1) */ CMP.CRAWL_ID, MAX (CMP.SYNC_RATE) SYNC_RATE,CMP.PRE_JUDGE, 'C' AS ORD
FROM TBMO_CONTENT_MAPPING CMP
GROUP BY CMP.CRAWL_ID,CMP.PRE_JUDGE
)CMP,
TBMO_CRAWL_MASTER CM,
TBMO_CRAWL_HIST CH,
(SELECT /*+ INDEX_FFS ( UMP IDX_CRAWL_ID_UP_IDX1) */ UMP.CRAWL_ID, MAX (UMP.SYNC_RATE) SYNC_RATE,UMP.PRE_JUDGE, 'U' AS ORD
FROM TBMO_UPLOAD_MAPPING UMP
GROUP BY UMP.CRAWL_ID,UMP.PRE_JUDGE
)UMP,
TBMO_BASIC_SITE BS
WHERE CMP.CRAWL_ID(+) = CM.CRAWL_ID
AND CM.CRAWL_ID = CH.CRAWL_ID
AND CH.CRAWL_ID = UMP.CRAWL_ID(+)
AND CM.SITE_ID = BS.SITE_ID
AND CH.CWL_STATUS = 'CRAWLS00'
-- AND CH.CWL_DATE BETWEEN '20151125000000' AND '20151125235959'
--AND NVL(CMP.PRE_JUDGE,UMP.PRE_JUDGE) = 9
ORDER BY CH.CWL_DATE DESC
)Z
WHERE ROWNUM <= (1*20)
) p
WHERE RNUM >= 1+(1*20)-20
;
TBMO_CONTENT_MAPPING, TBMO_UPLOAD_MAPPING 테이블의 CRAWL_ID 가 다수로 있고 이것들을 GROUP BY 해서 중복값을 없애고
TBMO_CRAWL_HIST, TBMO_CRAWL_MASTER 테이블에 조인 하였는데요
GROUP BY 부분 에서 성능이 안나와서 여기에 올려봅니다. 어떻게 극복할 방법이 없을까요?
여러 테이블 중 주(Main)가 되는 정보와 부(Sub)가 되는 정보가 있습니다.
CMP 와 UMP 는 부가정보죠.
이런 정보들을 모두 다 조인하여 페이징 처리를 한다면 상당히 비효율입니다.
페이징 처리를 하고 난 결과물만 가지고 조인한다면? 효율적일 것입니다.
CH, CM, BS 가 있는데요.
CM 과 BS 또한 CH 의 부수적인 정보인 듯 합니다.
CH 만 필요한 만큼만 가져온 뒤
나머지 정보들은 그만큼만 조인하면 좋을 것입니다.
이렇게 부분범위만 처리하려면 Hash 조인이 아닌 NL 조인이 유리해 보이는데요.
죄다 HASH 로 풀리고 있네요.
적절한 인덱스가 존재하는지 의문이네요?
) 버튼을 클릭하여 작성 하시면 됩니다.