데이터베이스 질문과 답변
안녕하세요. 항상 구루비에서 많이 공부 하고 있는 초짜 개발자 입니다. 대용량 쿼리 속도 개선이 너무 어려워서 문의 드립니다. 1달간 모든 생산 실적을 가지고 오려고 합니다. 해당 데이터는 약80만건 용량은 대략 200MB 정도 입니다. 조회시 너무 속도가 느려서 개선 하고자 합니다.
SELECT LEAD
, WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
, WMT.ITEM_UOM_CODE AS ITEM_UOM_CODE
, ARL.ACT_RUN_QTY AS UOM_QTY
, WMT.MTX_UOM_CODE AS WK_UOM
, WMT.FACTOR_VALUE1 AS WK_FACTOR1
, WMT.MTX_UOM_QTY1 AS WK_UOM_QTY1
, WMT.FACTOR_VALUE2 AS WK_FACTOR2
, WMT.MTX_UOM_QTY2 AS WK_UOM_QTY2
, ARL.ACT_RUN_EXTEND_DATE AS ACT_RUN_EXTEND_DATE
FROM (--작업종료실적--
SELECT SUM(CASE WMT.MOVE_TRX_TYPE
WHEN 'RUN_END' THEN WMT.UOM_QTY
WHEN 'CANCEL_RUN_END' THEN -1 * WMT.UOM_QTY END) AS ACT_RUN_QTY
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.EXTEND_DATE ELSE NULL END) AS ACT_RUN_EXTEND_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_START' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_START_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_END_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_TOMOVE_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.MOVE_TRX_ID ELSE NULL END) AS ACT_RUN_MOVE_TRX_ID -- 취소만 있는 경우도 있음
, MIN(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_FIRST_RECEIPT_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_LAST_RECEIPT_DATE
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
FROM WIP_MOVE_TRANSACTIONS WMT
, INV_ITEM_MASTER_TLV IIM
, EAPP_USER_TLV EU
WHERE IIM.INVENTORY_ITEM_ID = WMT.INVENTORY_ITEM_ID
AND IIM.SOB_ID = 90
AND IIM.ORG_ID = 901
AND WMT.MOVE_TRX_TYPE IN('RECEIPT', 'RUN_START', 'RUN_END', 'CANCEL_RUN_END', 'REJECT')
AND WMT.MOVE_TRX_DATE BETWEEN TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')
AND 'TOMOVE' = 'RUN_END'
AND EU.USER_ID = 2
AND ( (EU.WIP_READ_CONTROL = 'UNLIMITED' AND 1 = 1)
OR (EU.WIP_READ_CONTROL = 'LIMITED' AND EXISTS (SELECT 'Y'
FROM WIP_USER_CONTROL UC
, SDM_STANDARD_RESOURCE_TLV SR
WHERE SR.WORKCENTER_ID = UC.WORKCENTER_ID
AND UC.USER_ID = 2
AND UC.READ_FLAG = 'Y'
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID)))
GROUP BY WMT.JOB_ID
, WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
UNION ALL -- (자사 : 정상인계기준) -- 인계실적 --
SELECT SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' AND WMS.MOVE_STEP = 'WAIT_MOVE' THEN WMT.UOM_QTY ELSE 0 END)
+ SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' AND WMS.MOVE_STEP = 'TOMOVE' THEN WMT.UOM_QTY * (-1) ELSE 0 END) AS ACT_RUN_QTY
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' THEN WMT.EXTEND_DATE ELSE NULL END) AS ACT_RUN_EXTEND_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_START' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_START_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_END_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_TOMOVE_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' THEN WMT.MOVE_TRX_ID ELSE NULL END) AS ACT_RUN_MOVE_TRX_ID -- 취소만 있는 경우도 있음
, MIN(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_FIRST_RECEIPT_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_LAST_RECEIPT_DATE
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
FROM WIP_MOVE_TRANSACTIONS WMT
, INV_ITEM_MASTER_TLV IIM
, WIP_MOVE_STEP WMS
WHERE IIM.INVENTORY_ITEM_ID = WMT.INVENTORY_ITEM_ID
AND IIM.SOB_ID = 90
AND IIM.ORG_ID = 901
AND IIM.SOB_ID = WMT.SOB_ID
AND IIM.ORG_ID = WMT.ORG_ID
AND WMT.MOVE_TRX_TYPE IN('RECEIPT', 'RUN_START', 'RUN_END', 'CANCEL_RUN_END', 'REJECT', 'TOMOVE','CANCEL_TOMOVE')
AND WMT.FROM_STEP_ID = WMS.MOVE_STEP_ID -- TOMOVE의 경우에는 2번 발생이 되기 때문 FROM_STEP_ID가 TOMOVE인것만 고르기위해
AND WMT.MOVE_TRX_DATE BETWEEN TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')
AND EXISTS (SELECT 'Y'
FROM SDM_STANDARD_RESOURCE SR
, SDM_STANDARD_WORKCENTER SW
WHERE SW.WORKCENTER_ID = SR.WORKCENTER_ID
AND SW.OWNER_TYPE_LCODE != 'FAR_OUTSIDE' -- 자사
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID )
GROUP BY WMT.JOB_ID
, DECODE(WMT.MOVE_TRX_TYPE,'CANCEL_TOMOVE',WMT.TO_OP_SEQ_NO,WMT.FROM_OP_SEQ_NO)
, WMT.JOB_NO
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
UNION ALL --(외주 : 작업종료 후 입하처리기준) -- 인계실적 : 사외외주는 IQC에서 합격처리를 해야만 인계가 되므로, 작업종료실적 후 입하처리시 인계
SELECT SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' AND WMS.MOVE_STEP = 'WAIT_MOVE' THEN WMT.UOM_QTY ELSE 0 END)
+ SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' AND WMS.MOVE_STEP = 'TOMOVE' THEN WMT.UOM_QTY * (-1) ELSE 0 END) AS ACT_RUN_QTY
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'WAIT_MOVE' THEN WMT.EXTEND_DATE ELSE NULL END) AS ACT_RUN_EXTEND_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_START' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_START_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_END_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_TOMOVE_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' THEN WMT.MOVE_TRX_ID ELSE NULL END) AS ACT_RUN_MOVE_TRX_ID
, MIN(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_FIRST_RECEIPT_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_LAST_RECEIPT_DATE
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
FROM WIP_MOVE_TRANSACTIONS WMT
, INV_ITEM_MASTER IIM
, EAPP_USER EU
, WIP_MOVE_STEP WMS
WHERE IIM.INVENTORY_ITEM_ID = WMT.INVENTORY_ITEM_ID
AND IIM.SOB_ID = 90
AND IIM.ORG_ID = 901
AND WMT.MOVE_TRX_TYPE IN('RECEIPT', 'RUN_START', 'RUN_END', 'CANCEL_RUN_END', 'REJECT','TOMOVE','CANCEL_TOMOVE')
AND WMT.FROM_STEP_ID = WMS.MOVE_STEP_ID
AND WMT.MOVE_TRX_DATE BETWEEN TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')
AND EU.USER_ID = 2
AND ( (EU.WIP_READ_CONTROL = 'UNLIMITED' AND 1 = 1)
OR (EU.WIP_READ_CONTROL = 'LIMITED' AND EXISTS (SELECT 'Y'
FROM WIP_USER_CONTROL UC
, SDM_STANDARD_RESOURCE_TLV SR
WHERE SR.WORKCENTER_ID = UC.WORKCENTER_ID
AND UC.USER_ID = 2
AND UC.READ_FLAG = 'Y'
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID))) -- 공정별 사용자만 조회되도록 변경
AND EXISTS (SELECT 'Y' -- 사외외주만 해당
FROM SDM_STANDARD_RESOURCE SR
, SDM_STANDARD_WORKCENTER SW
WHERE SW.WORKCENTER_ID = SR.WORKCENTER_ID
AND SW.OWNER_TYPE_LCODE = 'FAR_OUTSIDE'
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID
)
GROUP BY WMT.JOB_ID
, WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
) ARL
, WIP_MOVE_TRANSACTIONS WMT
WHERE ARL.ACT_RUN_MOVE_TRX_ID = WMT.MOVE_TRX_ID
AND ARL.ACT_RUN_QTY != 0
AND WMT.SOB_ID = 90
AND WMT.ORG_ID = 901
ORDER BY ARL.ACT_RUN_EXTEND_DATE
, WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
;
----------------------------------------------------------------------------
Plan hash value: 1891521995
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 118K| 10M| | 1355K (1)| 04:31:12 |
| 1 | SORT ORDER BY | | 118K| 10M| 14M| 1355K (1)| 04:31:12 |
| 2 | NESTED LOOPS | | 118K| 10M| | 1353K (1)| 04:30:40 |
| 3 | NESTED LOOPS | | 133K| 10M| | 1353K (1)| 04:30:40 |
| 4 | VIEW | | 133K| 4567K| | 1086K (1)| 03:37:13 |
| 5 | UNION-ALL | | | | | | |
|* 6 | FILTER | | | | | | |
| 7 | HASH GROUP BY | | 1 | 147 | 287M| | |
|* 8 | FILTER | | | | | | |
|* 9 | FILTER | | | | | | |
|* 10 | HASH JOIN RIGHT OUTER | | 1914K| 268M| | 512K (1)| 01:42:29 |
|* 11 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_TL_U1 | 47763 | 373K| | 56 (8)| 00:00:01 |
|* 12 | HASH JOIN | | 1914K| 253M| | 512K (1)| 01:42:28 |
|* 13 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
| 14 | NESTED LOOPS | | 1914K| 230M| | 512K (1)| 01:42:27 |
| 15 | NESTED LOOPS OUTER | | 1 | 44 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID| EAPP_USER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| EAPP_USER_TL | 1 | 30 | | 0 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | EAPP_USER_TL_N2 | 1 | | | 0 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS | 1914K| 149M| | 512K (1)| 01:42:27 |
| 21 | NESTED LOOPS | | 1 | 40 | | 3 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1 | 26 | | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | | 0 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | | 1 (0)| 00:00:01 |
|* 27 | FILTER | | | | | | |
| 28 | HASH GROUP BY | | 132K| 17M| 377M| 543K (1)| 01:48:42 |
|* 29 | HASH JOIN RIGHT OUTER | | 2658K| 347M| | 512K (1)| 01:42:29 |
|* 30 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_TL_U1 | 47763 | 373K| | 56 (8)| 00:00:01 |
|* 31 | HASH JOIN | | 2658K| 327M| | 512K (1)| 01:42:28 |
|* 32 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
|* 33 | HASH JOIN | | 2658K| 294M| | 512K (1)| 01:42:27 |
| 34 | VIEW | index$_join$_009 | 13 | 156 | | 2 (0)| 00:00:01 |
|* 35 | HASH JOIN | | | | | | |
| 36 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U1 | 13 | 156 | | 1 (0)| 00:00:01 |
| 37 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U2 | 13 | 156 | | 1 (0)| 00:00:01 |
|* 38 | HASH JOIN RIGHT SEMI | | 2716K| 269M| | 512K (1)| 01:42:26 |
| 39 | VIEW | VW_SQ_1 | 387 | 3096 | | 14 (0)| 00:00:01 |
|* 40 | FILTER | | | | | | |
|* 41 | HASH JOIN | | 387 | 13158 | | 14 (0)| 00:00:01 |
|* 42 | TABLE ACCESS FULL | SDM_STANDARD_WORKCENTER | 163 | 2934 | | 6 (0)| 00:00:01 |
| 43 | TABLE ACCESS FULL | SDM_STANDARD_RESOURCE | 886 | 14176 | | 8 (0)| 00:00:01 |
|* 44 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS | 2796K| 256M| | 512K (1)| 01:42:26 |
|* 45 | FILTER | | | | | | |
| 46 | HASH GROUP BY | | 685 | 90420 | 372M| 542K (1)| 01:48:31 |
|* 47 | FILTER | | | | | | |
|* 48 | HASH JOIN | | 2667K| 335M| | 512K (1)| 01:42:28 |
|* 49 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
|* 50 | HASH JOIN | | 2667K| 302M| | 512K (1)| 01:42:27 |
| 51 | VIEW | index$_join$_015 | 13 | 156 | | 2 (0)| 00:00:01 |
|* 52 | HASH JOIN | | | | | | |
| 53 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U1 | 13 | 156 | | 1 (0)| 00:00:01 |
| 54 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U2 | 13 | 156 | | 1 (0)| 00:00:01 |
|* 55 | HASH JOIN | | 2725K| 278M| | 512K (1)| 01:42:27 |
| 56 | NESTED LOOPS | | 345 | 7590 | | 16 (0)| 00:00:01 |
| 57 | TABLE ACCESS BY INDEX ROWID | EAPP_USER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | | 1 (0)| 00:00:01 |
| 59 | VIEW | VW_SQ_2 | 345 | 2760 | | 14 (0)| 00:00:01 |
| 60 | SORT UNIQUE | | 345 | 11730 | | | |
|* 61 | FILTER | | | | | | |
|* 62 | HASH JOIN | | 345 | 11730 | | 14 (0)| 00:00:01 |
|* 63 | TABLE ACCESS FULL | SDM_STANDARD_WORKCENTER | 145 | 2610 | | 6 (0)| 00:00:01 |
| 64 | TABLE ACCESS FULL | SDM_STANDARD_RESOURCE | 886 | 14176 | | 8 (0)| 00:00:01 |
|* 65 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS | 3147K| 255M| | 512K (1)| 01:42:26 |
| 66 | NESTED LOOPS | | 1 | 40 | | 3 (0)| 00:00:01 |
| 67 | NESTED LOOPS OUTER | | 1 | 26 | | 2 (0)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | | 0 (0)| 00:00:01 |
|* 71 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | | 1 (0)| 00:00:01 |
|* 72 | INDEX UNIQUE SCAN | WIP_MOVE_TRANSACTIONS_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 73 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_TRANSACTIONS | 1 | 62 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(SUM(CASE "WMT"."MOVE_TRX_TYPE" WHEN 'RUN_END' THEN "WMT"."UOM_QTY" WHEN 'CANCEL_RUN_END' THEN
(-1)*"WMT"."UOM_QTY" END )<>0)
8 - filter("T"."WIP_READ_CONTROL"='UNLIMITED' OR "T"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B1 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B2 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID"))
9 - filter(NULL IS NOT NULL AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24M
ISS'))
10 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID")
11 - filter("IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
12 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID")
13 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
17 - access("T"."USER_ID"=2)
18 - filter("TL"."USER_ID"(+)=2)
19 - access("TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
20 - filter(("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START') AND
"WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
"WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS'))
24 - access("T"."RESOURCE_ID"=:B1)
25 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
26 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
27 - filter(SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY"
ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' AND "WMS"."MOVE_STEP"='TOMOVE') THEN
"WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0)
29 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID")
30 - filter("IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
31 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID" AND "IIM"."SOB_ID"="WMT"."SOB_ID" AND
"IIM"."ORG_ID"="WMT"."ORG_ID")
32 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
33 - access("WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
35 - access(ROWID=ROWID)
38 - access("ITEM_1"="WMT"."FROM_RESOURCE_ID")
40 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
41 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
42 - filter("SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE')
44 - filter("WMT"."ORG_ID"=901 AND "WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START' OR
"WMT"."MOVE_TRX_TYPE"='TOMOVE') AND "WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND
"WMT"."SOB_ID"=90)
45 - filter(SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY"
ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' AND "WMS"."MOVE_STEP"='TOMOVE') THEN
"WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0)
47 - filter("EU"."WIP_READ_CONTROL"='UNLIMITED' OR "EU"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B1 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B2 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID"))
48 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID")
49 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
50 - access("WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
52 - access(ROWID=ROWID)
55 - access("ITEM_2"="WMT"."FROM_RESOURCE_ID")
58 - access("EU"."USER_ID"=2)
61 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
62 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
63 - filter("SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE')
65 - filter("WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START' OR
"WMT"."MOVE_TRX_TYPE"='TOMOVE') AND "WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS'))
69 - access("T"."RESOURCE_ID"=:B1)
70 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
71 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
72 - access("ARL"."ACT_RUN_MOVE_TRX_ID"="WMT"."MOVE_TRX_ID")
73 - filter("WMT"."ORG_ID"=901 AND "WMT"."SOB_ID"=90)
고견 부탁 드립니다.
감사합니다.
by 부쉬맨
[2020.08.11 15:51:34]
-작업종료실적--
SELECT SUM(CASE WMT.MOVE_TRX_TYPE
WHEN 'RUN_END' THEN WMT.UOM_QTY
WHEN 'CANCEL_RUN_END' THEN -1 * WMT.UOM_QTY END) AS ACT_RUN_QTY
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.EXTEND_DATE ELSE NULL END) AS ACT_RUN_EXTEND_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_START' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_START_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_END_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_TOMOVE_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.MOVE_TRX_ID ELSE NULL END) AS ACT_RUN_MOVE_TRX_ID -- 취소만 있는 경우도 있음
, MIN(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_FIRST_RECEIPT_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_LAST_RECEIPT_DATE
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
FROM WIP_MOVE_TRANSACTIONS WMT
, INV_ITEM_MASTER_TLV IIM
, EAPP_USER_TLV EU
WHERE IIM.INVENTORY_ITEM_ID = WMT.INVENTORY_ITEM_ID
AND IIM.SOB_ID = 90
AND IIM.ORG_ID = 901
AND WMT.MOVE_TRX_TYPE IN('RECEIPT', 'RUN_START', 'RUN_END', 'CANCEL_RUN_END', 'REJECT')
AND WMT.MOVE_TRX_DATE BETWEEN TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')
AND 'TOMOVE' = 'RUN_END'
AND EU.USER_ID = 2
AND ( (EU.WIP_READ_CONTROL = 'UNLIMITED' AND 1 = 1)
OR (EU.WIP_READ_CONTROL = 'LIMITED' AND EXISTS (SELECT 'Y'
FROM WIP_USER_CONTROL UC
, SDM_STANDARD_RESOURCE_TLV SR
WHERE SR.WORKCENTER_ID = UC.WORKCENTER_ID
AND UC.USER_ID = 2
AND UC.READ_FLAG = 'Y'
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID)))
GROUP BY WMT.JOB_ID
, WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
예전에 작업했던 방식으로 설명을 드리면
일단 union all로 되어있는 (서브쿼리) 형태가 다 table full scan을 수행하면서 생긴문제로 보여집니다.
1) union all로 되어있는 부분을 하나하나 씩 돌려서 시간을 줄일수 있는 방법을 고민
2) AND WMT.SOB_ID = 90
AND WMT.ORG_ID = 901
두개의 조건이 조인조건으로 풀수 있을꺼같은데 업무적인 형태로 가능한지 여부 확인
최대한 가능하다면 두개의 조건으로 조인하는 형태를 취하면서 WIP_MOVE_TRANSACTIONS 테이블에 두개의 컬럼을 복합 index로 생성
집계를 내는 부분이라서 최대한 검색조건으로 모수데이터를 줄이고 합계를 내는 형태를 취해야할듯합니다.
by 냥냥펀치원투
[2020.08.11 18:06:38]
빠른 답변 감사합니다.
말씀하신데로 테스트를 위한 dummy 테이블을 생성 해서 관련 데이터를 넣고 인덱스를 생성 하고 있습니다. (약 800만건)
퇴근시간이라서 인덱스를 생성이 되면 실행 계획 결과를 첨부 하겠습니다.
by 냥냥펀치원투
[2020.08.12 11:28:59]
Plan hash value: 2205054348
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 170K| 15M| | 1028K (1)| 03:25:41 |
| 1 | SORT ORDER BY | | 170K| 15M| 20M| 1028K (1)| 03:25:41 |
| 2 | NESTED LOOPS | | 170K| 15M| | 1024K (1)| 03:24:56 |
| 3 | NESTED LOOPS | | 170K| 15M| | 1024K (1)| 03:24:56 |
| 4 | VIEW | | 170K| 5819K| | 684K (1)| 02:16:49 |
| 5 | UNION-ALL | | | | | | |
|* 6 | FILTER | | | | | | |
| 7 | HASH GROUP BY | | 1 | 153 | 315M| | |
|* 8 | FILTER | | | | | | |
|* 9 | FILTER | | | | | | |
|* 10 | HASH JOIN RIGHT OUTER | | 1979K| 288M| | 306K (1)| 01:01:13 |
|* 11 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_TL_U1 | 47763 | 373K| | 56 (8)| 00:00:01 |
|* 12 | HASH JOIN | | 1979K| 273M| | 305K (1)| 01:01:12 |
|* 13 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
| 14 | NESTED LOOPS | | 1979K| 249M| | 305K (1)| 01:01:10 |
| 15 | NESTED LOOPS OUTER | | 1 | 44 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID| EAPP_USER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| EAPP_USER_TL | 1 | 30 | | 0 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | EAPP_USER_TL_N2 | 1 | | | 0 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | 1979K| 166M| | 305K (1)| 01:01:10 |
| 21 | NESTED LOOPS | | 1 | 40 | | 3 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1 | 26 | | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | | 0 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | | 1 (0)| 00:00:01 |
|* 27 | FILTER | | | | | | |
| 28 | HASH GROUP BY | | 155K| 18M| 413M| 341K (1)| 01:08:23 |
|* 29 | FILTER | | | | | | |
|* 30 | FILTER | | | | | | |
|* 31 | HASH JOIN RIGHT OUTER | | 3176K| 387M| | 306K (1)| 01:01:14 |
|* 32 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_TL_U1 | 47763 | 373K| | 56 (8)| 00:00:01 |
|* 33 | HASH JOIN | | 3176K| 363M| | 306K (1)| 01:01:13 |
|* 34 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
|* 35 | HASH JOIN | | 3176K| 324M| | 305K (1)| 01:01:11 |
| 36 | VIEW | index$_join$_009 | 13 | 156 | | 2 (0)| 00:00:01 |
|* 37 | HASH JOIN | | | | | | |
| 38 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U1 | 13 | 156 | | 1 (0)| 00:00:01 |
| 39 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U2 | 13 | 156 | | 1 (0)| 00:00:01 |
|* 40 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | 3244K| 293M| | 305K (1)| 01:01:11 |
| 41 | NESTED LOOPS | | 1 | 34 | | 3 (0)| 00:00:01 |
| 42 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 43 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 44 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | 1 | 18 | | 1 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | 1 | | | 0 (0)| 00:00:01 |
|* 46 | FILTER | | | | | | |
| 47 | HASH GROUP BY | | 14707 | 1867K| 427M| 342K (1)| 01:08:27 |
|* 48 | FILTER | | | | | | |
|* 49 | FILTER | | | | | | |
|* 50 | HASH JOIN | | 3177K| 393M| | 306K (1)| 01:01:13 |
|* 51 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
|* 52 | HASH JOIN | | 3177K| 354M| | 305K (1)| 01:01:11 |
| 53 | NESTED LOOPS | | 13 | 338 | | 5 (0)| 00:00:01 |
| 54 | TABLE ACCESS BY INDEX ROWID | EAPP_USER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | | 1 (0)| 00:00:01 |
| 56 | TABLE ACCESS FULL | WIP_MOVE_STEP | 13 | 156 | | 3 (0)| 00:00:01 |
|* 57 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | 3244K| 281M| | 305K (1)| 01:01:11 |
| 58 | NESTED LOOPS | | 1 | 34 | | 3 (0)| 00:00:01 |
| 59 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 61 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | 1 | 18 | | 1 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | 1 | | | 0 (0)| 00:00:01 |
| 63 | NESTED LOOPS | | 1 | 40 | | 3 (0)| 00:00:01 |
| 64 | NESTED LOOPS OUTER | | 1 | 26 | | 2 (0)| 00:00:01 |
| 65 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | | 0 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | | 1 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | WIP_MOVE_TRANSACTIONS_KHK_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 70 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_TRANSACTIONS_KHK | 1 | 61 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(SUM(CASE "WMT"."MOVE_TRX_TYPE" WHEN 'RUN_END' THEN "WMT"."UOM_QTY" WHEN 'CANCEL_RUN_END' THEN
(-1)*"WMT"."UOM_QTY" END )<>0)
8 - filter("T"."WIP_READ_CONTROL"='UNLIMITED' OR "T"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B1 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B2 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID"))
9 - filter(NULL IS NOT NULL AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MI
SS'))
10 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID")
11 - filter("IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
12 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID" AND "WMT"."SOB_ID"="IIM"."SOB_ID")
13 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
17 - access("T"."USER_ID"=2)
18 - filter("TL"."USER_ID"(+)=2)
19 - access("TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
20 - filter(("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START') AND
"WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
"WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND "WMT"."SOB_ID"=90)
24 - access("T"."RESOURCE_ID"=:B1)
25 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
26 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
27 - filter(SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY"
ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' AND "WMS"."MOVE_STEP"='TOMOVE') THEN
"WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0)
29 - filter( EXISTS (SELECT 0 FROM "SDM_STANDARD_WORKCENTER" "SW","SDM_STANDARD_RESOURCE" "SR" WHERE
"SR"."RESOURCE_ID"=:B1 AND "SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID" AND "SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE'))
30 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
31 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID")
32 - filter("IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
33 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID" AND "IIM"."SOB_ID"="WMT"."SOB_ID" AND
"IIM"."ORG_ID"="WMT"."ORG_ID")
34 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
35 - access("WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
37 - access(ROWID=ROWID)
40 - filter("WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START' OR
"WMT"."MOVE_TRX_TYPE"='TOMOVE') AND "WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND
"WMT"."SOB_ID"=90 AND "WMT"."ORG_ID"=901)
43 - access("SR"."RESOURCE_ID"=:B1)
44 - filter("SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE')
45 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
46 - filter(SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY"
ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' AND "WMS"."MOVE_STEP"='TOMOVE') THEN
"WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0)
48 - filter( EXISTS (SELECT 0 FROM "SDM_STANDARD_WORKCENTER" "SW","SDM_STANDARD_RESOURCE" "SR" WHERE
"SR"."RESOURCE_ID"=:B1 AND "SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID" AND "SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE') AND
("EU"."WIP_READ_CONTROL"='UNLIMITED' OR "EU"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B2 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B3 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")))
49 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
50 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID" AND "WMT"."SOB_ID"="IIM"."SOB_ID")
51 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
52 - access("WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
55 - access("EU"."USER_ID"=2)
57 - filter("WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START' OR
"WMT"."MOVE_TRX_TYPE"='TOMOVE') AND "WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND
"WMT"."SOB_ID"=90)
60 - access("SR"."RESOURCE_ID"=:B1)
61 - filter("SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE')
62 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
66 - access("T"."RESOURCE_ID"=:B1)
67 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
68 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
69 - access("ARL"."ACT_RUN_MOVE_TRX_ID"="WMT"."MOVE_TRX_ID")
70 - filter("WMT"."SOB_ID"=90 AND "WMT"."ORG_ID"=901)
말씀하신대로 하니 속도가 30% 정도 향상이 되었습니다.
감사합니다.
by 냥냥펀치원투
[2020.08.12 11:31:32]
그리고 계획이 아래와 같이 변경이 되었습니다.
| 28 | HASH GROUP BY | | => | 28 | HASH GROUP BY | | |* 29 | HASH JOIN RIGHT OUTER | | => |* 29 | FILTER | | |* 30 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_TL_U1 | => |* 30 | FILTER | | |* 31 | HASH JOIN | | => |* 31 | HASH JOIN RIGHT OUTER | | |* 32 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | => |* 32 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_TL_U1 | |* 33 | HASH JOIN | | => |* 33 | HASH JOIN | | | 34 | VIEW | index$_join$_009 | => |* 34 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | |* 35 | HASH JOIN | | => |* 35 | HASH JOIN | | | 36 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U1 | => | 36 | VIEW | index$_join$_009 | | 37 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U2 | => |* 37 | HASH JOIN | | |* 38 | HASH JOIN RIGHT SEMI | | => | 38 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U1 | | 39 | VIEW | VW_SQ_1 | => | 39 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U2 | |* 40 | FILTER | | => |* 40 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | |* 41 | HASH JOIN | | => | 41 | NESTED LOOPS | | |* 42 | TABLE ACCESS FULL | SDM_STANDARD_WORKCENTER | => | 42 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | | 43 | TABLE ACCESS FULL | SDM_STANDARD_RESOURCE | => |* 43 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | |* 44 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS | => |* 44 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | |* 45 | FILTER | | => |* 45 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | | 46 | HASH GROUP BY | | => |* 46 | FILTER | | |* 47 | FILTER | | => | 47 | HASH GROUP BY | | |* 48 | HASH JOIN | | => |* 48 | FILTER | | |* 49 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | => |* 49 | FILTER | | |* 50 | HASH JOIN | | => |* 50 | HASH JOIN | | | 51 | VIEW | index$_join$_015 | => |* 51 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | |* 52 | HASH JOIN | | => |* 52 | HASH JOIN | | | 53 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U1 | => | 53 | NESTED LOOPS | | | 54 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U2 | => | 54 | TABLE ACCESS BY INDEX ROWID | EAPP_USER | |* 55 | HASH JOIN | | => |* 55 | INDEX UNIQUE SCAN | EAPP_USER_U1 | | 56 | NESTED LOOPS | | => | 56 | TABLE ACCESS FULL | WIP_MOVE_STEP | | 57 | TABLE ACCESS BY INDEX ROWID | EAPP_USER | => |* 57 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | |* 58 | INDEX UNIQUE SCAN | EAPP_USER_U1 | => | 58 | NESTED LOOPS | | | 59 | VIEW | VW_SQ_2 | => | 59 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | | 60 | SORT UNIQUE | | => |* 60 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | |* 61 | FILTER | | => |* 61 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | |* 62 | HASH JOIN | | => |* 62 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | |* 63 | TABLE ACCESS FULL | SDM_STANDARD_WORKCENTER | => | 64 | TABLE ACCESS FULL | SDM_STANDARD_RESOURCE | => |* 65 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS | => | 66 | NESTED LOOPS | | =>
by 부쉬맨
[2020.08.12 14:43:08]
마지막으로 주신플랜만보았을때
뷰에 들어가있는 테이블도 있는거같은데
원론적으로 말씀드리면
제일많은 테이블기준으로 처음 수행하게 하면안되고
EAPP_USER,EAPP_USER_U1 이런 형태의 테이블기준으로
먼저 수행하여서 WIP_MOVE_TRANSACTIONS_KHK 테이블이 제일 마지막으로 검색조건으로 나와야 하는 대상을 줄여줘야되는 부분입니다.
leading 으로 힌트를 넣어서 테이블을 수행하는 기준으로 바꿔보면서 테스트를 진행해보시기 바랍니다.
by 냥냥펀치원투
[2020.08.12 19:39:02]
답변 주셔서 감사합니다.
말씀하신데로
1. 모수를 줄이고 (서브쿼리에 HAVING 추가 및 SELECT 개수 조절)
2. LEADING을 사용 하였으나 실행 개획에 변경이 없습니다. 어떤 부분에서 놓쳤는지 잘 모르겠습니다.
-- LEADING TEST // 변경후
SELECT WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
, WMT.ITEM_UOM_CODE AS ITEM_UOM_CODE
, ARL.ACT_RUN_QTY AS UOM_QTY
, WMT.MTX_UOM_CODE AS WK_UOM
, WMT.FACTOR_VALUE1 AS WK_FACTOR1
, WMT.MTX_UOM_QTY1 AS WK_UOM_QTY1
, WMT.FACTOR_VALUE2 AS WK_FACTOR2
, WMT.MTX_UOM_QTY2 AS WK_UOM_QTY2
, ARL.ACT_RUN_EXTEND_DATE AS ACT_RUN_EXTEND_DATE
FROM (--작업종료실적--
SELECT ACT_RUN_MOVE_TRX_ID, ACT_RUN_QTY, ACT_RUN_EXTEND_DATE
FROM
(
SELECT /*+ LEADING(EU IIM WMT) PARALLEL(WMT 4) PARALLEL(IIM 4)*/
SUM(CASE WMT.MOVE_TRX_TYPE
WHEN 'RUN_END' THEN WMT.UOM_QTY
WHEN 'CANCEL_RUN_END' THEN -1 * WMT.UOM_QTY END) AS ACT_RUN_QTY
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.EXTEND_DATE ELSE NULL END) AS ACT_RUN_EXTEND_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_START' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_START_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_END_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_TOMOVE_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.MOVE_TRX_ID ELSE NULL END) AS ACT_RUN_MOVE_TRX_ID -- 취소만 있는 경우도 있음
, MIN(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_FIRST_RECEIPT_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_LAST_RECEIPT_DATE
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
FROM WIP_MOVE_TRANSACTIONS_KHK WMT
, INV_ITEM_MASTER_TLV IIM
, EAPP_USER_TLV EU
WHERE IIM.INVENTORY_ITEM_ID = WMT.INVENTORY_ITEM_ID
AND IIM.SOB_ID = 90
AND IIM.ORG_ID = 901
AND IIM.SOB_ID = EU.SOB_ID
AND IIM.ORG_ID = EU.ORG_ID
-- AND IIM.SOB_ID = WMS.SOB_ID
-- AND IIM.ORG_ID = WMS.ORG_ID
AND WMT.MOVE_TRX_TYPE IN('RECEIPT', 'RUN_START', 'RUN_END', 'CANCEL_RUN_END', 'REJECT')
AND WMT.MOVE_TRX_DATE BETWEEN TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')
AND 'TOMOVE' = 'RUN_END'
AND EU.USER_ID = 2
AND ( (EU.WIP_READ_CONTROL = 'UNLIMITED' AND 1 = 1)
OR (EU.WIP_READ_CONTROL = 'LIMITED' AND EXISTS (SELECT 'Y'
FROM WIP_USER_CONTROL UC
, SDM_STANDARD_RESOURCE_TLV SR
WHERE SR.WORKCENTER_ID = UC.WORKCENTER_ID
AND UC.USER_ID = 2
AND UC.READ_FLAG = 'Y'
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID)))
GROUP BY WMT.JOB_ID
, WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
HAVING (SUM(CASE WMT.MOVE_TRX_TYPE WHEN 'RUN_END' THEN WMT.UOM_QTY
WHEN 'CANCEL_RUN_END' THEN -1 * WMT.UOM_QTY END)) != 0
UNION ALL -- (자사 : 정상인계기준) -- 인계실적 --
SELECT /*+ LEADING(WMS IIM WMT) PARALLEL(WMT 4) PARALLEL(IIM 4)*/
SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' AND WMS.MOVE_STEP = 'WAIT_MOVE' THEN WMT.UOM_QTY ELSE 0 END)
+ SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' AND WMS.MOVE_STEP = 'TOMOVE' THEN WMT.UOM_QTY * (-1) ELSE 0 END) AS ACT_RUN_QTY
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' THEN WMT.EXTEND_DATE ELSE NULL END) AS ACT_RUN_EXTEND_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_START' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_START_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_END_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_TOMOVE_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' THEN WMT.MOVE_TRX_ID ELSE NULL END) AS ACT_RUN_MOVE_TRX_ID -- 취소만 있는 경우도 있음
, MIN(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_FIRST_RECEIPT_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_LAST_RECEIPT_DATE
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
FROM WIP_MOVE_TRANSACTIONS_KHK WMT
, INV_ITEM_MASTER_TLV IIM
, WIP_MOVE_STEP WMS
WHERE IIM.INVENTORY_ITEM_ID = WMT.INVENTORY_ITEM_ID
AND IIM.SOB_ID = 90
AND IIM.ORG_ID = 901
AND IIM.SOB_ID = WMS.SOB_ID
AND IIM.ORG_ID = WMS.ORG_ID
AND WMT.MOVE_TRX_TYPE IN('RECEIPT', 'RUN_START', 'RUN_END', 'CANCEL_RUN_END', 'REJECT', 'TOMOVE','CANCEL_TOMOVE')
AND WMT.FROM_STEP_ID = WMS.MOVE_STEP_ID -- TOMOVE의 경우에는 2번 발생이 되기 때문 FROM_STEP_ID가 TOMOVE인것만 고르기위해
AND WMT.MOVE_TRX_DATE BETWEEN TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')
AND EXISTS (SELECT 'Y'
FROM SDM_STANDARD_RESOURCE SR
, SDM_STANDARD_WORKCENTER SW
WHERE SW.WORKCENTER_ID = SR.WORKCENTER_ID
AND SW.OWNER_TYPE_LCODE != 'FAR_OUTSIDE' -- 자사
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID )
GROUP BY WMT.JOB_ID
, DECODE(WMT.MOVE_TRX_TYPE,'CANCEL_TOMOVE',WMT.TO_OP_SEQ_NO,WMT.FROM_OP_SEQ_NO)
, WMT.JOB_NO
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
HAVING(SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' AND WMS.MOVE_STEP = 'WAIT_MOVE' THEN WMT.UOM_QTY ELSE 0 END)
+ SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' AND WMS.MOVE_STEP = 'TOMOVE' THEN WMT.UOM_QTY * (-1) ELSE 0 END)) != 0
UNION ALL --(외주 : 작업종료 후 입하처리기준) -- 인계실적 : 사외외주는 IQC에서 합격처리를 해야만 인계가 되므로, 작업종료실적 후 입하처리시 인계
SELECT SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' AND WMS.MOVE_STEP = 'WAIT_MOVE' THEN WMT.UOM_QTY ELSE 0 END)
+ SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' AND WMS.MOVE_STEP = 'TOMOVE' THEN WMT.UOM_QTY * (-1) ELSE 0 END) AS ACT_RUN_QTY
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'WAIT_MOVE' THEN WMT.EXTEND_DATE ELSE NULL END) AS ACT_RUN_EXTEND_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_START' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_START_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RUN_END' OR WMT.MOVE_TRX_TYPE = 'CANCEL_RUN_END' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_RUN_END_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_TOMOVE_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' OR WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' THEN WMT.MOVE_TRX_ID ELSE NULL END) AS ACT_RUN_MOVE_TRX_ID
, MIN(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_FIRST_RECEIPT_DATE
, MAX(CASE WHEN WMT.MOVE_TRX_TYPE = 'RECEIPT' THEN WMT.MOVE_TRX_DATE ELSE NULL END) AS ACT_LAST_RECEIPT_DATE
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
FROM WIP_MOVE_TRANSACTIONS_KHK WMT
, INV_ITEM_MASTER IIM
, EAPP_USER EU
, WIP_MOVE_STEP WMS
WHERE IIM.INVENTORY_ITEM_ID = WMT.INVENTORY_ITEM_ID
AND IIM.SOB_ID = 90
AND IIM.ORG_ID = 901
AND IIM.SOB_ID = EU.SOB_ID
AND IIM.ORG_ID = EU.ORG_ID
AND IIM.SOB_ID = WMS.SOB_ID
AND IIM.ORG_ID = WMS.ORG_ID
AND WMT.MOVE_TRX_TYPE IN('RECEIPT', 'RUN_START', 'RUN_END', 'CANCEL_RUN_END', 'REJECT','TOMOVE','CANCEL_TOMOVE')
AND WMT.FROM_STEP_ID = WMS.MOVE_STEP_ID
AND WMT.MOVE_TRX_DATE BETWEEN TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')
AND EU.USER_ID = 2
AND ( (EU.WIP_READ_CONTROL = 'UNLIMITED' AND 1 = 1)
OR (EU.WIP_READ_CONTROL = 'LIMITED' AND EXISTS (SELECT 'Y'
FROM WIP_USER_CONTROL UC
, SDM_STANDARD_RESOURCE_TLV SR
WHERE SR.WORKCENTER_ID = UC.WORKCENTER_ID
AND UC.USER_ID = 2
AND UC.READ_FLAG = 'Y'
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID))) -- 공정별 사용자만 조회되도록 변경
AND EXISTS (SELECT 'Y' -- 사외외주만 해당
FROM SDM_STANDARD_RESOURCE SR
, SDM_STANDARD_WORKCENTER SW
WHERE SW.WORKCENTER_ID = SR.WORKCENTER_ID
AND SW.OWNER_TYPE_LCODE = 'FAR_OUTSIDE'
AND SR.RESOURCE_ID = WMT.FROM_RESOURCE_ID
)
GROUP BY WMT.JOB_ID
, WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
, WMT.MOVE_TRX_DATE -- ADD, 시간기준 데이터 안맞아 추가
HAVING(SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'TOMOVE' AND WMS.MOVE_STEP = 'WAIT_MOVE' THEN WMT.UOM_QTY ELSE 0 END)
+ SUM(CASE WHEN WMT.MOVE_TRX_TYPE = 'CANCEL_TOMOVE' AND WMS.MOVE_STEP = 'TOMOVE' THEN WMT.UOM_QTY * (-1) ELSE 0 END)) != 0
)
) ARL
, WIP_MOVE_TRANSACTIONS_KHK WMT
WHERE ARL.ACT_RUN_MOVE_TRX_ID = WMT.MOVE_TRX_ID
AND ARL.ACT_RUN_QTY != 0
AND WMT.SOB_ID = 90
AND WMT.ORG_ID = 901
ORDER BY ARL.ACT_RUN_EXTEND_DATE
, WMT.FROM_OP_SEQ_NO
, WMT.JOB_NO
;
by 냥냥펀치원투
[2020.08.12 19:40:19]
아래는 실행 계획 입니다.
Plan hash value: 2205054348
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8513 | 798K| | 693K (1)| 02:18:48 |
| 1 | SORT ORDER BY | | 8513 | 798K| | 693K (1)| 02:18:48 |
| 2 | NESTED LOOPS | | 8513 | 798K| | 693K (1)| 02:18:48 |
| 3 | NESTED LOOPS | | 8515 | 798K| | 693K (1)| 02:18:48 |
| 4 | VIEW | | 8515 | 291K| | 676K (1)| 02:15:24 |
| 5 | UNION-ALL | | | | | | |
|* 6 | FILTER | | | | | | |
| 7 | HASH GROUP BY | | 1 | 146 | | | |
|* 8 | FILTER | | | | | | |
|* 9 | FILTER | | | | | | |
|* 10 | HASH JOIN RIGHT OUTER | | 1980K| 275M| | 306K (1)| 01:01:13 |
|* 11 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_TL_U1 | 47763 | 373K| | 56 (8)| 00:00:01 |
|* 12 | HASH JOIN | | 1980K| 260M| | 305K (1)| 01:01:12 |
|* 13 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
| 14 | NESTED LOOPS | | 1980K| 236M| | 305K (1)| 01:01:10 |
| 15 | NESTED LOOPS OUTER | | 1 | 44 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID| EAPP_USER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| EAPP_USER_TL | 1 | 30 | | 0 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | EAPP_USER_TL_N2 | 1 | | | 0 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | 1980K| 152M| | 305K (1)| 01:01:10 |
| 21 | NESTED LOOPS | | 1 | 40 | | 3 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1 | 26 | | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | | 0 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | | 1 (0)| 00:00:01 |
|* 27 | FILTER | | | | | | |
| 28 | HASH GROUP BY | | 7778 | 972K| 413M| 339K (1)| 01:07:49 |
|* 29 | FILTER | | | | | | |
|* 30 | FILTER | | | | | | |
|* 31 | HASH JOIN RIGHT OUTER | | 3176K| 387M| | 306K (1)| 01:01:14 |
|* 32 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_TL_U1 | 47763 | 373K| | 56 (8)| 00:00:01 |
|* 33 | HASH JOIN | | 3176K| 363M| | 306K (1)| 01:01:13 |
|* 34 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
|* 35 | HASH JOIN | | 3176K| 324M| | 305K (1)| 01:01:11 |
| 36 | VIEW | index$_join$_009 | 13 | 156 | | 2 (0)| 00:00:01 |
|* 37 | HASH JOIN | | | | | | |
| 38 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U1 | 13 | 156 | | 1 (0)| 00:00:01 |
| 39 | INDEX FAST FULL SCAN | WIP_MOVE_STEP_U2 | 13 | 156 | | 1 (0)| 00:00:01 |
|* 40 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | 3244K| 293M| | 305K (1)| 01:01:11 |
| 41 | NESTED LOOPS | | 1 | 34 | | 3 (0)| 00:00:01 |
| 42 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 43 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 44 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | 1 | 18 | | 1 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | 1 | | | 0 (0)| 00:00:01 |
|* 46 | FILTER | | | | | | |
| 47 | HASH GROUP BY | | 736 | 90528 | 413M| 337K (1)| 01:07:36 |
|* 48 | FILTER | | | | | | |
|* 49 | FILTER | | | | | | |
|* 50 | HASH JOIN | | 3177K| 372M| | 306K (1)| 01:01:13 |
|* 51 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| | 119 (1)| 00:00:02 |
|* 52 | HASH JOIN | | 3177K| 333M| | 305K (1)| 01:01:11 |
| 53 | NESTED LOOPS | | 13 | 338 | | 5 (0)| 00:00:01 |
| 54 | TABLE ACCESS BY INDEX ROWID | EAPP_USER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | | 1 (0)| 00:00:01 |
| 56 | TABLE ACCESS FULL | WIP_MOVE_STEP | 13 | 156 | | 3 (0)| 00:00:01 |
|* 57 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | 3244K| 259M| | 305K (1)| 01:01:11 |
| 58 | NESTED LOOPS | | 1 | 34 | | 3 (0)| 00:00:01 |
| 59 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 61 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | 1 | 18 | | 1 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | 1 | | | 0 (0)| 00:00:01 |
| 63 | NESTED LOOPS | | 1 | 40 | | 3 (0)| 00:00:01 |
| 64 | NESTED LOOPS OUTER | | 1 | 26 | | 2 (0)| 00:00:01 |
| 65 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | | 2 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | | 0 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | | 1 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | WIP_MOVE_TRANSACTIONS_KHK_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 70 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_TRANSACTIONS_KHK | 1 | 61 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(SUM(CASE "WMT"."MOVE_TRX_TYPE" WHEN 'RUN_END' THEN "WMT"."UOM_QTY" WHEN 'CANCEL_RUN_END' THEN
(-1)*"WMT"."UOM_QTY" END )<>0 AND SUM(CASE "WMT"."MOVE_TRX_TYPE" WHEN 'RUN_END' THEN "WMT"."UOM_QTY" WHEN
'CANCEL_RUN_END' THEN (-1)*"WMT"."UOM_QTY" END )<>0)
8 - filter("T"."WIP_READ_CONTROL"='UNLIMITED' OR "T"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B1 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B2 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID"))
9 - filter(NULL IS NOT NULL AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MI
SS'))
10 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID")
11 - filter("IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
12 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID")
13 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
17 - access("T"."USER_ID"=2)
18 - filter("TL"."USER_ID"(+)=2)
19 - access("TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
20 - filter(("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START') AND
"WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
"WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS'))
24 - access("T"."RESOURCE_ID"=:B1)
25 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
26 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
27 - filter(SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY"
ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' AND "WMS"."MOVE_STEP"='TOMOVE') THEN
"WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0 AND SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND
"WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY" ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE'
AND "WMS"."MOVE_STEP"='TOMOVE') THEN "WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0)
29 - filter( EXISTS (SELECT 0 FROM "SDM_STANDARD_WORKCENTER" "SW","SDM_STANDARD_RESOURCE" "SR" WHERE
"SR"."RESOURCE_ID"=:B1 AND "SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID" AND "SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE'))
30 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
31 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID")
32 - filter("IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
33 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID" AND "IIM"."SOB_ID"="WMT"."SOB_ID" AND
"IIM"."ORG_ID"="WMT"."ORG_ID")
34 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
35 - access("WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
37 - access(ROWID=ROWID)
40 - filter("WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START' OR
"WMT"."MOVE_TRX_TYPE"='TOMOVE') AND "WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND
"WMT"."SOB_ID"=90 AND "WMT"."ORG_ID"=901)
43 - access("SR"."RESOURCE_ID"=:B1)
44 - filter("SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE')
45 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
46 - filter(SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY"
ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' AND "WMS"."MOVE_STEP"='TOMOVE') THEN
"WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0 AND SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND
"WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY" ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE'
AND "WMS"."MOVE_STEP"='TOMOVE') THEN "WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0)
48 - filter( EXISTS (SELECT 0 FROM "SDM_STANDARD_WORKCENTER" "SW","SDM_STANDARD_RESOURCE" "SR" WHERE
"SR"."RESOURCE_ID"=:B1 AND "SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID" AND "SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE') AND
("EU"."WIP_READ_CONTROL"='UNLIMITED' OR "EU"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B2 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B3 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")))
49 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
50 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID")
51 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
52 - access("WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
55 - access("EU"."USER_ID"=2)
57 - filter("WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
("WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' OR "WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' OR "WMT"."MOVE_TRX_TYPE"='RECEIPT' OR
"WMT"."MOVE_TRX_TYPE"='REJECT' OR "WMT"."MOVE_TRX_TYPE"='RUN_END' OR "WMT"."MOVE_TRX_TYPE"='RUN_START' OR
"WMT"."MOVE_TRX_TYPE"='TOMOVE') AND "WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS'))
60 - access("SR"."RESOURCE_ID"=:B1)
61 - filter("SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE')
62 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
66 - access("T"."RESOURCE_ID"=:B1)
67 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
68 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
69 - access("ARL"."ACT_RUN_MOVE_TRX_ID"="WMT"."MOVE_TRX_ID")
70 - filter("WMT"."SOB_ID"=90 AND "WMT"."ORG_ID"=901)
by 부쉬맨
[2020.08.12 20:08:54]
전체테이블기준으로 테스트를 하지마시고
union all로 되어있는 부분 sql1,sql2,sql3 을 하나씩 뜯어서 고치셔야됩니다.
parallel은 빼셔야될듯 나중에실행계획이나 결과가 잘나오면 넣어도될듯합니다.
아니면 방법적으로 동일한 sql결과물에 대해서 with 절에 넣고
메모리에 올려놓은 방식을 취하는것도 하나의 방법일듯합니다.
by 냥냥펀치원투
[2020.08.13 16:14:11]
union all로 되어 있는 부분 데이터에 having 조건을 where절로 변경 하여 아래와 같은 계획을 생성 하였습니다.
작업 시작하기전 cost: 1500000
작업 후 cost: 200000
Plan hash value: 2455536061
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4314 | 404K| 199K (1)| 00:39:53 |
| 1 | SORT ORDER BY | | 4314 | 404K| 199K (1)| 00:39:53 |
| 2 | NESTED LOOPS | | 4314 | 404K| 199K (1)| 00:39:53 |
| 3 | NESTED LOOPS | | 4315 | 404K| 199K (1)| 00:39:53 |
| 4 | VIEW | | 4315 | 147K| 190K (1)| 00:38:09 |
| 5 | UNION-ALL | | | | | |
|* 6 | FILTER | | | | | |
| 7 | HASH GROUP BY | | 1 | 153 | | |
|* 8 | FILTER | | | | | |
|* 9 | FILTER | | | | | |
| 10 | NESTED LOOPS OUTER | | 654K| 95M| 305K (1)| 01:01:11 |
|* 11 | HASH JOIN | | 654K| 90M| 305K (1)| 01:01:10 |
|* 12 | INDEX FAST FULL SCAN | INV_ITEM_MASTER_N15 | 72642 | 922K| 119 (1)| 00:00:02 |
| 13 | NESTED LOOPS | | 654K| 82M| 305K (1)| 01:01:09 |
| 14 | NESTED LOOPS OUTER | | 1 | 51 | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | EAPP_USER | 1 | 21 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | EAPP_USER_TL | 1 | 30 | 0 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | EAPP_USER_TL_N2 | 1 | | 0 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | WIP_MOVE_TRANSACTIONS_KHK | 654K| 50M| 305K (1)| 01:01:09 |
|* 20 | INDEX UNIQUE SCAN | INV_ITEM_MASTER_TL_U1 | 1 | 8 | 0 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 40 | 3 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1 | 26 | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | 2 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | 0 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | 1 (0)| 00:00:01 |
|* 27 | FILTER | | | | | |
| 28 | HASH GROUP BY | | 4313 | 539K| | |
| 29 | CONCATENATION | | | | | |
|* 30 | FILTER | | | | | |
| 31 | NESTED LOOPS OUTER | | 97 | 12416 | 2613 (1)| 00:00:32 |
| 32 | NESTED LOOPS | | 97 | 11640 | 2613 (1)| 00:00:32 |
| 33 | NESTED LOOPS | | 97 | 10379 | 2516 (1)| 00:00:31 |
| 34 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_STEP | 1 | 19 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | WIP_MOVE_STEP_U2 | 1 | | 0 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_TRANSACTIONS_KHK | 97 | 8536 | 2515 (1)| 00:00:31 |
|* 37 | INDEX RANGE SCAN | WIP_MOVE_TRANSACTIONS_KHK_N13 | 6179 | | 571 (1)| 00:00:07 |
| 38 | NESTED LOOPS | | 1 | 34 | 3 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | 2 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | 1 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | 1 | 18 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | 1 | | 0 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID | INV_ITEM_MASTER | 1 | 13 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | INV_ITEM_MASTER_U1 | 1 | | 0 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | INV_ITEM_MASTER_TL_U1 | 1 | 8 | 0 (0)| 00:00:01 |
|* 46 | FILTER | | | | | |
| 47 | NESTED LOOPS OUTER | | 6780 | 847K| 91265 (1)| 00:18:16 |
|* 48 | HASH JOIN | | 6780 | 794K| 91265 (1)| 00:18:16 |
| 49 | NESTED LOOPS | | 6780 | 708K| 90849 (1)| 00:18:11 |
| 50 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_STEP | 1 | 19 | 1 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | WIP_MOVE_STEP_U2 | 1 | | 0 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_TRANSACTIONS_KHK | 6780 | 582K| 90848 (1)| 00:18:11 |
|* 53 | INDEX RANGE SCAN | WIP_MOVE_TRANSACTIONS_KHK_N4 | 134K| | 10552 (1)| 00:02:07 |
| 54 | NESTED LOOPS | | 1 | 34 | 3 (0)| 00:00:01 |
| 55 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | 2 (0)| 00:00:01 |
|* 56 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | 1 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | 1 | 18 | 1 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | 1 | | 0 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | INV_ITEM_MASTER_N14 | 72642 | 922K| 415 (0)| 00:00:05 |
|* 60 | INDEX UNIQUE SCAN | INV_ITEM_MASTER_TL_U1 | 1 | 8 | 0 (0)| 00:00:01 |
|* 61 | FILTER | | | | | |
| 62 | HASH GROUP BY | | 1 | 137 | | |
| 63 | CONCATENATION | | | | | |
|* 64 | FILTER | | | | | |
|* 65 | FILTER | | | | | |
| 66 | NESTED LOOPS | | 97 | 13289 | 2614 (1)| 00:00:32 |
| 67 | NESTED LOOPS | | 97 | 13289 | 2614 (1)| 00:00:32 |
| 68 | NESTED LOOPS | | 97 | 12028 | 2517 (1)| 00:00:31 |
| 69 | NESTED LOOPS | | 1 | 40 | 2 (0)| 00:00:01 |
| 70 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_STEP | 1 | 19 | 1 (0)| 00:00:01 |
|* 71 | INDEX UNIQUE SCAN | WIP_MOVE_STEP_U2 | 1 | | 0 (0)| 00:00:01 |
|* 72 | TABLE ACCESS BY INDEX ROWID | EAPP_USER | 1 | 21 | 1 (0)| 00:00:01 |
|* 73 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | 0 (0)| 00:00:01 |
|* 74 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_TRANSACTIONS_KHK | 97 | 8148 | 2515 (1)| 00:00:31 |
|* 75 | INDEX RANGE SCAN | WIP_MOVE_TRANSACTIONS_KHK_N13 | 6179 | | 571 (1)| 00:00:07 |
| 76 | NESTED LOOPS | | 1 | 34 | 3 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID| SDM_STANDARD_RESOURCE | 1 | 16 | 2 (0)| 00:00:01 |
|* 78 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | 1 (0)| 00:00:01 |
|* 79 | TABLE ACCESS BY INDEX ROWID| SDM_STANDARD_WORKCENTER | 1 | 18 | 1 (0)| 00:00:01 |
|* 80 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | 1 | | 0 (0)| 00:00:01 |
|* 81 | INDEX UNIQUE SCAN | INV_ITEM_MASTER_U1 | 1 | | 0 (0)| 00:00:01 |
|* 82 | TABLE ACCESS BY INDEX ROWID | INV_ITEM_MASTER | 1 | 13 | 1 (0)| 00:00:01 |
| 83 | NESTED LOOPS | | 1 | 40 | 3 (0)| 00:00:01 |
| 84 | NESTED LOOPS OUTER | | 1 | 26 | 2 (0)| 00:00:01 |
| 85 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | 2 (0)| 00:00:01 |
|* 86 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | 1 (0)| 00:00:01 |
|* 87 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | 0 (0)| 00:00:01 |
|* 88 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | 1 (0)| 00:00:01 |
|* 89 | FILTER | | | | | |
|* 90 | FILTER | | | | | |
|* 91 | HASH JOIN | | 6780 | 907K| 91266 (1)| 00:18:16 |
| 92 | NESTED LOOPS | | 6780 | 821K| 90850 (1)| 00:18:11 |
| 93 | NESTED LOOPS | | 1 | 40 | 2 (0)| 00:00:01 |
| 94 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_STEP | 1 | 19 | 1 (0)| 00:00:01 |
|* 95 | INDEX UNIQUE SCAN | WIP_MOVE_STEP_U2 | 1 | | 0 (0)| 00:00:01 |
|* 96 | TABLE ACCESS BY INDEX ROWID | EAPP_USER | 1 | 21 | 1 (0)| 00:00:01 |
|* 97 | INDEX UNIQUE SCAN | EAPP_USER_U1 | 1 | | 0 (0)| 00:00:01 |
|* 98 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_TRANSACTIONS_KHK | 6780 | 556K| 90848 (1)| 00:18:11 |
|* 99 | INDEX RANGE SCAN | WIP_MOVE_TRANSACTIONS_KHK_N4 | 134K| | 10552 (1)| 00:02:07 |
| 100 | NESTED LOOPS | | 1 | 34 | 3 (0)| 00:00:01 |
| 101 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | 2 (0)| 00:00:01 |
|*102 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | 1 (0)| 00:00:01 |
|*103 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_WORKCENTER | 1 | 18 | 1 (0)| 00:00:01 |
|*104 | INDEX UNIQUE SCAN | SDM_STANDARD_WORKCENTER_U1 | 1 | | 0 (0)| 00:00:01 |
|*105 | INDEX RANGE SCAN | INV_ITEM_MASTER_N14 | 72642 | 922K| 415 (0)| 00:00:05 |
| 106 | NESTED LOOPS | | 1 | 40 | 3 (0)| 00:00:01 |
| 107 | NESTED LOOPS OUTER | | 1 | 26 | 2 (0)| 00:00:01 |
| 108 | TABLE ACCESS BY INDEX ROWID | SDM_STANDARD_RESOURCE | 1 | 16 | 2 (0)| 00:00:01 |
|*109 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_U1 | 1 | | 1 (0)| 00:00:01 |
|*110 | INDEX UNIQUE SCAN | SDM_STANDARD_RESOURCE_TL_U1 | 1 | 10 | 0 (0)| 00:00:01 |
|*111 | INDEX RANGE SCAN | WIP_USER_CONTROL_N1 | 1 | 14 | 1 (0)| 00:00:01 |
|*112 | INDEX UNIQUE SCAN | WIP_MOVE_TRANSACTIONS_KHK_U1 | 1 | | 1 (0)| 00:00:01 |
|*113 | TABLE ACCESS BY INDEX ROWID | WIP_MOVE_TRANSACTIONS_KHK | 1 | 61 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(SUM(CASE "WMT"."MOVE_TRX_TYPE" WHEN 'RUN_END' THEN "WMT"."UOM_QTY" WHEN 'CANCEL_RUN_END' THEN
(-1)*"WMT"."UOM_QTY" END )<>0)
8 - filter("T"."WIP_READ_CONTROL"='UNLIMITED' OR "T"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B1 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B2 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID"))
9 - filter(NULL IS NOT NULL AND TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH
24MISS'))
11 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID")
12 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
15 - filter("T"."ORG_ID"=901 AND "T"."SOB_ID"=90)
16 - access("T"."USER_ID"=2)
17 - filter("TL"."USER_ID"(+)=2)
18 - access("TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
19 - filter(("WMT"."MOVE_TRX_TYPE"='RUN_END' AND "WMT"."UOM_QTY"<>0 OR "WMT"."MOVE_TRX_TYPE"='CANCEL_RUN_END' AND
"WMT"."UOM_QTY"<>0) AND "WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
"WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS'))
20 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID" AND
"IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
24 - access("T"."RESOURCE_ID"=:B1)
25 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
26 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
27 - filter(SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY"
ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' AND "WMS"."MOVE_STEP"='TOMOVE') THEN
"WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0)
30 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
35 - access("WMS"."SOB_ID"=90 AND "WMS"."ORG_ID"=901 AND "WMS"."MOVE_STEP"='TOMOVE')
36 - filter("WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
"WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND "WMT"."UOM_QTY"<>0 AND
"WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
37 - access("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE')
filter( EXISTS (SELECT 0 FROM "SDM_STANDARD_WORKCENTER" "SW","SDM_STANDARD_RESOURCE" "SR" WHERE
"SR"."RESOURCE_ID"=:B1 AND "SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID" AND "SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE'))
40 - access("SR"."RESOURCE_ID"=:B1)
41 - filter("SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE')
42 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
43 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
44 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID")
45 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID" AND
"IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
46 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
48 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID" AND "IIM"."SOB_ID"="WMS"."SOB_ID" AND
"IIM"."ORG_ID"="WMS"."ORG_ID")
51 - access("WMS"."SOB_ID"=90 AND "WMS"."ORG_ID"=901 AND "WMS"."MOVE_STEP"='WAIT_MOVE')
52 - filter("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND "WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND "WMT"."UOM_QTY"<>0 AND
(LNNVL("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE') OR LNNVL("WMS"."MOVE_STEP"='TOMOVE') OR LNNVL("WMT"."UOM_QTY"<>0)))
53 - access("WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
filter( EXISTS (SELECT 0 FROM "SDM_STANDARD_WORKCENTER" "SW","SDM_STANDARD_RESOURCE" "SR" WHERE
"SR"."RESOURCE_ID"=:B1 AND "SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID" AND "SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE'))
56 - access("SR"."RESOURCE_ID"=:B1)
57 - filter("SW"."OWNER_TYPE_LCODE"<>'FAR_OUTSIDE')
58 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
59 - access("IIM"."SOB_ID"=90 AND "IIM"."ORG_ID"=901)
60 - access("IIMT"."INVENTORY_ITEM_ID"(+)="IIM"."INVENTORY_ITEM_ID" AND
"IIMT"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
61 - filter(SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMS"."MOVE_STEP"='WAIT_MOVE') THEN "WMT"."UOM_QTY"
ELSE 0 END )+SUM(CASE WHEN ("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE' AND "WMS"."MOVE_STEP"='TOMOVE') THEN
"WMT"."UOM_QTY"*(-1) ELSE 0 END )<>0)
64 - filter("EU"."WIP_READ_CONTROL"='UNLIMITED' OR "EU"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B1 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B2 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID"))
65 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
71 - access("WMS"."SOB_ID"=90 AND "WMS"."ORG_ID"=901 AND "WMS"."MOVE_STEP"='TOMOVE')
72 - filter("EU"."ORG_ID"=901 AND "EU"."SOB_ID"=90)
73 - access("EU"."USER_ID"=2)
74 - filter("WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS') AND
"WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND "WMT"."UOM_QTY"<>0 AND
"WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
75 - access("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE')
filter( EXISTS (SELECT 0 FROM "SDM_STANDARD_WORKCENTER" "SW","SDM_STANDARD_RESOURCE" "SR" WHERE
"SR"."RESOURCE_ID"=:B1 AND "SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID" AND "SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE'))
78 - access("SR"."RESOURCE_ID"=:B1)
79 - filter("SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE')
80 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
81 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID")
82 - filter("IIM"."ORG_ID"=901 AND "IIM"."SOB_ID"=90)
86 - access("T"."RESOURCE_ID"=:B1)
87 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
88 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
89 - filter("EU"."WIP_READ_CONTROL"='UNLIMITED' OR "EU"."WIP_READ_CONTROL"='LIMITED' AND EXISTS (SELECT 0 FROM
APPS."SDM_STANDARD_RESOURCE_TL" "TL",APPS."SDM_STANDARD_RESOURCE" "T","WIP_USER_CONTROL" "UC" WHERE
"T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."USER_ID"=2 AND "UC"."READ_FLAG"='Y' AND "T"."RESOURCE_ID"=:B1 AND
"TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"() AND "TL"."RESOURCE_ID"(+)=:B2 AND
"TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID"))
90 - filter(TO_DATE('20200801075959','RRRRMMDDHH24MISS')>=TO_DATE('20200701080000','RRRRMMDDHH24MISS'))
91 - access("IIM"."INVENTORY_ITEM_ID"="WMT"."INVENTORY_ITEM_ID" AND "IIM"."SOB_ID"="WMS"."SOB_ID" AND
"IIM"."ORG_ID"="WMS"."ORG_ID")
95 - access("WMS"."SOB_ID"=90 AND "WMS"."ORG_ID"=901 AND "WMS"."MOVE_STEP"='WAIT_MOVE')
96 - filter("EU"."ORG_ID"=901 AND "EU"."SOB_ID"=90)
97 - access("EU"."USER_ID"=2)
98 - filter("WMT"."MOVE_TRX_TYPE"='TOMOVE' AND "WMT"."MOVE_TRX_DATE">=TO_DATE('20200701080000','RRRRMMDDHH24MISS')
AND "WMT"."MOVE_TRX_DATE"<=TO_DATE('20200801075959','RRRRMMDDHH24MISS') AND "WMT"."UOM_QTY"<>0 AND
(LNNVL("WMT"."MOVE_TRX_TYPE"='CANCEL_TOMOVE') OR LNNVL("WMS"."MOVE_STEP"='TOMOVE') OR LNNVL("WMT"."UOM_QTY"<>0)))
99 - access("WMT"."FROM_STEP_ID"="WMS"."MOVE_STEP_ID")
filter( EXISTS (SELECT 0 FROM "SDM_STANDARD_WORKCENTER" "SW","SDM_STANDARD_RESOURCE" "SR" WHERE
"SR"."RESOURCE_ID"=:B1 AND "SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID" AND "SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE'))
102 - access("SR"."RESOURCE_ID"=:B1)
103 - filter("SW"."OWNER_TYPE_LCODE"='FAR_OUTSIDE')
104 - access("SW"."WORKCENTER_ID"="SR"."WORKCENTER_ID")
105 - access("IIM"."SOB_ID"=90 AND "IIM"."ORG_ID"=901)
109 - access("T"."RESOURCE_ID"=:B1)
110 - access("TL"."RESOURCE_ID"(+)=:B1 AND "TL"."LANG_CODE"(+)="USERENV_G"."GET_TERRITORY_S_F"())
filter("TL"."RESOURCE_ID"(+)="T"."RESOURCE_ID")
111 - access("UC"."USER_ID"=2 AND "T"."WORKCENTER_ID"="UC"."WORKCENTER_ID" AND "UC"."READ_FLAG"='Y')
filter("UC"."READ_FLAG"='Y')
112 - access("ACT_RUN_MOVE_TRX_ID"="WMT"."MOVE_TRX_ID")
113 - filter("WMT"."SOB_ID"=90 AND "WMT"."ORG_ID"=901)
by 냥냥펀치원투
[2020.08.14 14:41:41]
80만건 조회 하는데 8분 정도 걸립니다.
굉장히 만족 하고 있습니다.
부쉬맨님 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.