데이터베이스 질문과 답변
SELECT A.WORK_DT,
A.RULE_GRP_ID,
A.RULE_TYP,
A.SYS_CATG_CD,
A.FAC_ID,
A.FAB_ID,
A.OWNER_CD,
C.FAMILY_CD,
C.TECH_CD,
C.DEVICE_ID,
C.LOT_CD,
A.PROD_ID ,
(SELECT S.WIP_MT_AREA_ID
FROM TATADM.TAT_LOTDAYDEVICE_S S
WHERE S.WORK_DT = A.WORK_DT
AND S.SYS_CATG_CD = A.SYS_CATG_CD
AND S.OWNER_CD = A.OWNER_CD
AND S.RULE_GRP_ID = A.RULE_GRP_ID
AND S.RULE_TYP = A.RULE_TYP
AND S.FAC_ID = A.FAC_ID
AND S.LOT_CD = A.LOT_CD
AND S.DEVICE_ID = A.DEVICE_ID
AND S.STD_OPER_ID = A.STD_OPER_ID
AND ROWNUM = 1 ) AS WIP_MT_AREA_ID ,
(SELECT S.WIP_DET_AREA_ID
FROM TATADM.TAT_LOTDAYDEVICE_S S
WHERE S.WORK_DT = A.WORK_DT
AND S.SYS_CATG_CD = A.SYS_CATG_CD
AND S.OWNER_CD = A.OWNER_CD
AND S.RULE_GRP_ID = A.RULE_GRP_ID
AND S.RULE_TYP = A.RULE_TYP
AND S.FAC_ID = A.FAC_ID
AND S.LOT_CD = A.LOT_CD
AND S.DEVICE_ID = A.DEVICE_ID
AND S.STD_OPER_ID = A.STD_OPER_ID
AND ROWNUM = 1 ) AS WIP_DET_AREA_ID ,
(SELECT S.PU_CD
FROM TATADM.TAT_LOTDAYDEVICE_S S
WHERE S.WORK_DT = A.WORK_DT
AND S.SYS_CATG_CD = A.SYS_CATG_CD
AND S.OWNER_CD = A.OWNER_CD
AND S.RULE_GRP_ID = A.RULE_GRP_ID
AND S.RULE_TYP = A.RULE_TYP
AND S.FAC_ID = A.FAC_ID
AND S.LOT_CD = A.LOT_CD
AND S.DEVICE_ID = A.DEVICE_ID
AND S.STD_OPER_ID = A.STD_OPER_ID
AND ROWNUM = 1 ) AS PU_CD ,
(SELECT S.PG_CD
FROM TATADM.TAT_LOTDAYDEVICE_S S
WHERE S.WORK_DT = A.WORK_DT
AND S.SYS_CATG_CD = A.SYS_CATG_CD
AND S.OWNER_CD = A.OWNER_CD
AND S.RULE_GRP_ID = A.RULE_GRP_ID
AND S.RULE_TYP = A.RULE_TYP
AND S.FAC_ID = A.FAC_ID
AND S.LOT_CD = A.LOT_CD
AND S.DEVICE_ID = A.DEVICE_ID
AND S.STD_OPER_ID = A.STD_OPER_ID
AND ROWNUM = 1 ) AS PG_CD ,
A.STD_OPER_ID ,
(SELECT MAX(S.STD_OPER_SEQ)
FROM TATADM.TAT_LOTDAYDEVICE_S S
WHERE S.WORK_DT = A.WORK_DT
AND S.SYS_CATG_CD = A.SYS_CATG_CD
AND S.OWNER_CD = A.OWNER_CD
AND S.FAC_ID = A.FAC_ID
AND S.FAB_ID = A.FAB_ID
AND S.LOT_CD = A.LOT_CD
AND S.DEVICE_ID = A.DEVICE_ID
AND S.STD_OPER_ID = A.STD_OPER_ID ) AS STD_OPER_SEQ ,
A.TAT_APPLY_YN,
A.SINGLE_EQP_OPER_CNT,
A.BATCH_EQP_OPER_CNT,
A.TAT,
A.WF_25_EQV_TAT,
A.OUT_LOT_CNT,
A.OUT_WF_CNT,
A.WF_QTY_EQV_TAT ,
A.WORK_DT AS REWORK_DT,
NULL AS REWORK_WF ,
COUNT(DISTINCT A.WORK_DT) OVER(PARTITION BY A.FAC_ID, C.FAMILY_CD, C.TECH_CD, A.LOT_CD, A.PROD_ID, A.STD_OPER_ID) LOTCNT ,
COUNT(DISTINCT A.WORK_DT) OVER() DCNT
FROM TATADM.TAT_LOTDAYPROD_S A,
TAT_STDDEVICE_M C
WHERE A.WORK_DT BETWEEN '20160801' AND '20160801'
AND A.SYS_CATG_CD IN ('TEST')
AND A.LOT_CD IN (SELECT T.LOT_CD
FROM TAT_TGTPROD_I T
WHERE 1=1
AND T.YM = '201608'
AND T.FAC_ID IN ('TEST')
AND T.FAC_ID = A.FAC_ID
AND T.LOT_CD = A.LOT_CD )
AND A.FAC_ID IN ('TEST')
AND C.FAMILY_CD IN ('TEST1')
AND C.TECH_CD IN ('PL')
AND A.LOT_CD IN ('TMB')
AND A.TAT_APPLY_YN IN ('Y','U')
AND A.FAC_ID = C.FAC_ID
AND A.LOT_CD = C.LOT_CD
AND A.DEVICE_ID = C.DEVICE_ID
Rows Row Source Operation
------- -----------------------------------------------------------------------
3189 COUNT STOPKEY (cr=3435764 pr=1167 pw=0 time=127273663 us)
3189 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3435764 pr=1167 pw=0 time=127207562 us cost=14 size=77 card=1)
3189 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYDEVICE_S PARTITION: KEY KEY (cr=3435764 pr=1167 pw=0 time=127139336 us cost=14 size=77 card=1)
3189 INDEX RANGE SCAN TAT_LOTDAYDEVICE_S_PK PARTITION: KEY KEY (cr=3432574 pr=976 pw=0 time=126187290 us cost=13 size=0 card=1)
3189 COUNT STOPKEY (cr=3435763 pr=0 pw=0 time=125586790 us)
3189 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3435763 pr=0 pw=0 time=125526530 us cost=14 size=77 card=1)
3189 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYDEVICE_S PARTITION: KEY KEY (cr=3435763 pr=0 pw=0 time=125465021 us cost=14 size=77 card=1)
3189 INDEX RANGE SCAN TAT_LOTDAYDEVICE_S_PK PARTITION: KEY KEY (cr=3432574 pr=0 pw=0 time=125302195 us cost=13 size=0 card=1)
3189 COUNT STOPKEY (cr=3435763 pr=0 pw=0 time=126013120 us)
3189 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3435763 pr=0 pw=0 time=125956912 us cost=14 size=77 card=1)
3189 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYDEVICE_S PARTITION: KEY KEY (cr=3435763 pr=0 pw=0 time=125896412 us cost=14 size=77 card=1)
3189 INDEX RANGE SCAN TAT_LOTDAYDEVICE_S_PK PARTITION: KEY KEY (cr=3432574 pr=0 pw=0 time=125745723 us cost=13 size=0 card=1)
3189 COUNT STOPKEY (cr=3435763 pr=0 pw=0 time=126431992 us)
3189 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3435763 pr=0 pw=0 time=126375776 us cost=14 size=77 card=1)
3189 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYDEVICE_S PARTITION: KEY KEY (cr=3435763 pr=0 pw=0 time=126317216 us cost=14 size=77 card=1)
3189 INDEX RANGE SCAN TAT_LOTDAYDEVICE_S_PK PARTITION: KEY KEY (cr=3432574 pr=0 pw=0 time=126171591 us cost=13 size=0 card=1)
1395 SORT AGGREGATE (cr=2570310 pr=936 pw=0 time=90625024 us)
4583 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2570310 pr=936 pw=0 time=72329013 us cost=6 size=57 card=1)
4583 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYDEVICE_S PARTITION: KEY KEY (cr=2570310 pr=936 pw=0 time=72297638 us cost=6 size=57 card=1)
4583 INDEX RANGE SCAN TAT_LOTDAYDEVICE_S_PK PARTITION: KEY KEY (cr=2569640 pr=870 pw=0 time=71973703 us cost=5 size=0 card=1)
3237 WINDOW SORT (cr=5362 pr=3501 pw=0 time=3063354 us cost=2639 size=2800 card=16)
19279 WINDOW SORT (cr=5362 pr=3501 pw=0 time=2862833 us cost=2639 size=2800 card=16)
19279 NESTED LOOPS (cr=5362 pr=3501 pw=0 time=3071338 us cost=2637 size=2800 card=16)
19466 NESTED LOOPS (cr=2934 pr=2934 pw=0 time=1180655 us cost=2637 size=2800 card=71)
1 NESTED LOOPS (cr=4 pr=4 pw=0 time=17243 us cost=1 size=41 card=1)
1 INDEX UNIQUE SCAN TAT_TGTPROD_I_PK (cr=2 pr=2 pw=0 time=4066 us cost=0 size=12 card=1)
1 TABLE ACCESS BY INDEX ROWID TAT_STDDEVICE_M (cr=2 pr=2 pw=0 time=13165 us cost=1 size=29 card=1)
1 INDEX RANGE SCAN TAT_STDDEVICE_M_IX1 (cr=1 pr=1 pw=0 time=8370 us cost=0 size=0 card=1)
19466 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2930 pr=2930 pw=0 time=1155155 us cost=2610 size=0 card=71)
19466 INDEX RANGE SCAN TAT_LOTDAYPROD_S_PK PARTITION: 2 2 (cr=2930 pr=2930 pw=0 time=1146364 us cost=2610 size=0 card=71)
19279 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYPROD_S PARTITION: 2 2 (cr=2428 pr=567 pw=0 time=884405 us cost=2636 size=27470 card=205)
********************************************************************************
TATADM.TAT_LOTDAYDEVICE_S 테이블 반복 ACCESS로 인한 부하가 심합니다.
5개 서브쿼리가 동일 조인 조건이면 OUTER_JOIN으로 변경하여 1회 ACCESS 변경 할 수 있을것 같은데..
JOIN 조건절이 다릅니다.
해당 서브쿼리 최적화 할 수 있는 방법이 뭐가 있을까요?
4개의 서브쿼리가 동일하네요. 이건 합치면 될 것 같은데.. 마지막 서브쿼리가 조건이 다르네요.
RULE_GRP_ID, SULE_TYPE, FAB_ID 가 인덱스를 활용하는 컬럼인지 아닌지에 따라 5개를 전부 합칠지
4개만 따로 합칠지 정해야할 것 같아요. 일단 4개만 묶어보면..
SELECT ~~
,REGEXP_SUBSTR(DATAVALUES,'[^^]+',1,1) WIP_MT_AREA_ID
,REGEXP_SUBSTR(DATAVALUES,'[^^]+',1,2) WIP_DET_AREA_ID
,REGEXP_SUBSTR(DATAVALUES,'[^^]+',1,3) PU_CD
,REGEXP_SUBSTR(DATAVALUES,'[^^]+',1,4) PG_CD
~~
FROM
(
SELECT A.WORK_DT,
A.RULE_GRP_ID,
A.RULE_TYP,
A.SYS_CATG_CD,
A.FAC_ID,
A.FAB_ID,
A.OWNER_CD,
C.FAMILY_CD,
C.TECH_CD,
C.DEVICE_ID,
C.LOT_CD,
A.PROD_ID ,
(SELECT NVL(S.WIP_MT_AREA_ID,' ')||'^'||NVL(S.WIP_DET_AREA_ID,' ')||'^'||NVL(S.PU_CD,' ')||'^'||NVL(S.PG_CD,' ')
FROM TATADM.TAT_LOTDAYDEVICE_S S
WHERE S.WORK_DT = A.WORK_DT
AND S.SYS_CATG_CD = A.SYS_CATG_CD
AND S.OWNER_CD = A.OWNER_CD
AND S.RULE_GRP_ID = A.RULE_GRP_ID
AND S.RULE_TYP = A.RULE_TYP
AND S.FAC_ID = A.FAC_ID
AND S.LOT_CD = A.LOT_CD
AND S.DEVICE_ID = A.DEVICE_ID
AND S.STD_OPER_ID = A.STD_OPER_ID
AND ROWNUM = 1 ) AS DATAVALUES ,
A.STD_OPER_ID ,
(SELECT MAX(S.STD_OPER_SEQ)
FROM TATADM.TAT_LOTDAYDEVICE_S S
WHERE S.WORK_DT = A.WORK_DT
AND S.SYS_CATG_CD = A.SYS_CATG_CD
AND S.OWNER_CD = A.OWNER_CD
AND S.FAC_ID = A.FAC_ID
AND S.FAB_ID = A.FAB_ID
AND S.LOT_CD = A.LOT_CD
AND S.DEVICE_ID = A.DEVICE_ID
AND S.STD_OPER_ID = A.STD_OPER_ID ) AS STD_OPER_SEQ ,
A.TAT_APPLY_YN,
A.SINGLE_EQP_OPER_CNT,
A.BATCH_EQP_OPER_CNT,
A.TAT,
A.WF_25_EQV_TAT,
A.OUT_LOT_CNT,
A.OUT_WF_CNT,
A.WF_QTY_EQV_TAT ,
A.WORK_DT AS REWORK_DT,
NULL AS REWORK_WF ,
COUNT(DISTINCT A.WORK_DT) OVER(PARTITION BY A.FAC_ID, C.FAMILY_CD, C.TECH_CD, A.LOT_CD, A.PROD_ID, A.STD_OPER_ID) LOTCNT ,
COUNT(DISTINCT A.WORK_DT) OVER() DCNT
FROM TATADM.TAT_LOTDAYPROD_S A,
TAT_STDDEVICE_M C
WHERE A.WORK_DT BETWEEN '20160801' AND '20160801'
AND A.SYS_CATG_CD IN ('TEST')
AND A.LOT_CD IN (SELECT T.LOT_CD
FROM TAT_TGTPROD_I T
WHERE 1=1
AND T.YM = '201608'
AND T.FAC_ID IN ('TEST')
AND T.FAC_ID = A.FAC_ID
AND T.LOT_CD = A.LOT_CD )
AND A.FAC_ID IN ('TEST')
AND C.FAMILY_CD IN ('TEST1')
AND C.TECH_CD IN ('PL')
AND A.LOT_CD IN ('TMB')
AND A.TAT_APPLY_YN IN ('Y','U')
AND A.FAC_ID = C.FAC_ID
AND A.LOT_CD = C.LOT_CD
AND A.DEVICE_ID = C.DEVICE_ID
)
SELECT A.WORK_DT,
A.RULE_GRP_ID,
A.RULE_TYP,
A.SYS_CATG_CD,
A.FAC_ID,
A.FAB_ID,
A.OWNER_CD,
C.FAMILY_CD,
C.TECH_CD,
C.DEVICE_ID,
C.LOT_CD,
A.PROD_ID ,
S.WIP_MT_AREA_ID,
S.WIP_DET_AREA_ID,
S.PU_CD,
S.PG_CD,
A.STD_OPER_ID ,
S2.STD_OPER_SEQ,
A.TAT_APPLY_YN,
A.SINGLE_EQP_OPER_CNT,
A.BATCH_EQP_OPER_CNT,
A.TAT,
A.WF_25_EQV_TAT,
A.OUT_LOT_CNT,
A.OUT_WF_CNT,
A.WF_QTY_EQV_TAT ,
A.WORK_DT AS REWORK_DT,
NULL AS REWORK_WF ,
COUNT(DISTINCT A.WORK_DT) OVER(PARTITION BY A.FAC_ID, C.FAMILY_CD, C.TECH_CD, A.LOT_CD, A.PROD_ID, A.STD_OPER_ID) LOTCNT ,
COUNT(DISTINCT A.WORK_DT) OVER() DCNT
FROM TATADM.TAT_LOTDAYPROD_S A,
TAT_STDDEVICE_M C,
(SELECT WORK_DT,
SYS_CATG_CD,
OWNER_CD,
RULE_GRP_ID,
RULE_TYP,
FAC_ID,
LOT_CD,
DEVICE_ID,
STD_OPER_ID,
MAX(WIP_MT_AREA_ID) WIP_MT_AREA_ID,
MAX(WIP_DET_AREA_ID) WIP_DET_AREA_ID,
MAX(PU_CD) PU_CD,
MAX(PG_CD) PG_CD
FROM TATADM.TAT_LOTDAYDEVICE_S
GROUP BY WORK_DT, SYS_CATG_CD, OWNER_CD, RULE_GRP_ID, RULE_TYP, FAC_ID, LOT_CD, DEVICE_ID, STD_OPER_ID, PU_CD, PG_CD) S,
(SELECT WORK_DT,
SYS_CATG_CD,
OWNER_CD,
FAC_ID,
FAB_ID,
LOT_CD ,
DEVICE_ID ,
STD_OPER_ID ,
MAX(STD_OPER_SEQ) STD_OPER_SEQ
FROM TATADM.TAT_LOTDAYDEVICE_S
GROUP BY WORK_DT, SYS_CATG_CD, OWNER_CD, FAC_ID, FAB_ID, LOT_CD , DEVICE_ID , STD_OPER_ID ) S2
WHERE A.WORK_DT BETWEEN '20160801' AND '20160801'
AND A.SYS_CATG_CD IN ('M10')
AND A.LOT_CD IN (SELECT T.LOT_CD
FROM TAT_TGTPROD_I T
WHERE 1=1
--SKIP
--S
AND S.WORK_DT(+) = A.WORK_DT
AND S.SYS_CATG_CD(+) = A.SYS_CATG_CD
AND S.OWNER_CD(+) = A.OWNER_CD
AND S.RULE_GRP_ID(+) = A.RULE_GRP_ID
AND S.RULE_TYP(+) = A.RULE_TYP
AND S.FAC_ID(+) = A.FAC_ID
AND S.LOT_CD(+) = A.LOT_CD
AND S.DEVICE_ID(+) = A.DEVICE_ID
AND S.STD_OPER_ID(+) = A.STD_OPER_ID
--S2
AND S2.WORK_DT(+) = A.WORK_DT
AND S2.SYS_CATG_CD(+) = A.SYS_CATG_CD
AND S2.OWNER_CD(+) = A.OWNER_CD
AND S2.FAC_ID(+) = A.FAC_ID
AND S2.FAB_ID(+) = A.FAB_ID
AND S2.LOT_CD(+) = A.LOT_CD
AND S2.DEVICE_ID(+) = A.DEVICE_ID
AND S2.STD_OPER_ID(+) = A.STD_OPER_ID
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 2 0.020 0.019 0 0 0 0
Execute 2 0.000 0.001 0 0 0 0
Fetch 194 1.390 1.425 0 14917 0 19279
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 198 1.410 1.445 0 14917 0 19279
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
19279 WINDOW SORT (cr=14917 pr=0 pw=0 time=1366488 us cost=21540 size=11408 card=16)
19279 WINDOW SORT (cr=14917 pr=0 pw=0 time=1086782 us cost=21540 size=11408 card=16)
19279 HASH JOIN RIGHT OUTER (cr=14917 pr=0 pw=0 time=792279 us cost=21538 size=11408 card=16)
4667 VIEW (cr=4778 pr=0 pw=0 time=185263 us cost=9451 size=208 card=1)
4667 HASH GROUP BY (cr=4778 pr=0 pw=0 time=182861 us cost=9451 size=55 card=1)
14865 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=4778 pr=0 pw=0 time=103186 us cost=9449 size=778690 card=14158)
14865 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYDEVICE_S PARTITION: 2 2 (cr=4778 pr=0 pw=0 time=95555 us cost=9449 size=778690 card=14158)
14865 INDEX RANGE SCAN TAT_LOTDAYDEVICE_S_PK PARTITION: 2 2 (cr=2571 pr=0 pw=0 time=43370 us cost=2659 size=0 card=4272)
19279 HASH JOIN OUTER (cr=10139 pr=0 pw=0 time=557005 us cost=12088 size=8080 card=16)
19279 NESTED LOOPS (cr=5361 pr=0 pw=0 time=190305 us cost=2637 size=2800 card=16)
19466 NESTED LOOPS (cr=2934 pr=0 pw=0 time=123066 us cost=2637 size=2800 card=71)
1 NESTED LOOPS (cr=4 pr=0 pw=0 time=122 us cost=1 size=41 card=1)
1 INDEX UNIQUE SCAN TAT_TGTPROD_I_PK (cr=2 pr=0 pw=0 time=39 us cost=0 size=12 card=1)
1 TABLE ACCESS BY INDEX ROWID TAT_STDDEVICE_M (cr=2 pr=0 pw=0 time=73 us cost=1 size=29 card=1)
1 INDEX RANGE SCAN TAT_STDDEVICE_M_IX1 (cr=1 pr=0 pw=0 time=36 us cost=0 size=0 card=1)
19466 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2930 pr=0 pw=0 time=114561 us cost=2610 size=0 card=71)
19466 INDEX RANGE SCAN TAT_LOTDAYPROD_S_PK PARTITION: 2 2 (cr=2930 pr=0 pw=0 time=105768 us cost=2610 size=0 card=71)
19279 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYPROD_S PARTITION: 2 2 (cr=2427 pr=0 pw=0 time=59146 us cost=2636 size=27470 card=205)
13755 VIEW (cr=4778 pr=0 pw=0 time=239935 us cost=9451 size=94710 card=287)
13755 HASH GROUP BY (cr=4778 pr=0 pw=0 time=228591 us cost=9451 size=26978 card=287)
14865 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=4778 pr=0 pw=0 time=101624 us cost=9449 size=1330852 card=14158)
14865 TABLE ACCESS BY LOCAL INDEX ROWID TAT_LOTDAYDEVICE_S PARTITION: 2 2 (cr=4778 pr=0 pw=0 time=94880 us cost=9449 size=1330852 card=14158)
14865 INDEX RANGE SCAN TAT_LOTDAYDEVICE_S_PK PARTITION: 2 2 (cr=2571 pr=0 pw=0 time=44235 us cost=2659 size=0 card=4272)
********************************************************************************
OUTER_JOIN 으로 변경하였습니다.
답변 감사합니다.
GROUP BY 로 처리하셨는데 GROUP BY 건수에 따라 성능이 다를 수도 있을 것 같아요.
서브쿼리에서는 ROWNUM = 1의 형태이기 때문에 약간 성능상 좋지 않을까.. 예상해보는데요.
한번 두 쿼리 비교해보세요^^;
) 버튼을 클릭하여 작성 하시면 됩니다.