Хочу разобраться с медленным выполнением запроса, до этого не работал с оптимизацией
SELECT R.CLAIM_ID,
PROD_SRC.PROMO_WID,
R.SUBMIT_FOR_ORG,
PROD_SRC.PRODUCT_WID,
R.RESOLUTION_ID,
R.DT,
R.SUBMIT_BY_ORG,
R.RESOLUTION_TYPE,
R.GROSS_AMOUNT,
R.GST_AMOUNT,
R.NET_AMOUNT,
R.STATUS,
R.DEAL_TYPE,
R.COMMENTS,
R.RES_PEND_APPR_NET_GST,
R.RES_UNRESOLVED_NET_GST,
R.RES_RESOLVED_NET_GST,
R.CREATED,
R.CREATED_BY,
R.LAST_UPD,
R.LAST_UPD_BY,
R.CLAIM_AMOUNT,
R.CUST_CLAIM_ID,
R.GL_BUCKET,
R.SOURCE_SYSTEM,
R.RES_START_DT,
R.RES_END_DT,
PROD_SRC.SRC_CODE
FROM WC_RESOLUTION_FS R,
W_PLAN_PROMO_D PROM,
( SELECT PROMO.ROW_WID AS PROMO_WID,
VOL.PRODUCT_WID AS PRODUCT_WID,
VOL.PRODUCT_SOURCE_SYSTEM AS SRC_CODE
FROM W_PLAN_PROMO_D PROMO,
WC_PLANNED_VOLUMES_F VOL,
WC_BUSINESS_DATE_D DT,
WC_ACTUAL_VOLS_SHIP_F SHIP
WHERE VOL.ORG_WID = SHIP.ORG_WID(+)
AND VOL.PRODUCT_WID = SHIP.PRODUCT_WID(+)
AND VOL.DT_WID = SHIP.DT_WID(+)
AND PROMO.X_PLANNING_DATA_TYPE = 'Shipment'
AND VOL.DT_WID <= DT.BUSINESS_DT_WID
AND PROMO.X_ORG_WID = VOL.ORG_WID
AND PROMO.ROW_WID = VOL.PROMO_WID
AND VOL.PRODUCT_SOURCE_SYSTEM = SHIP.PRODUCT_SOURCE_SYSTEM
AND VOL.PROMO_TYPE IN ('TTI', 'TTB', 'TTV')
AND PROMO.PROMO_STATUS IN
('Planned',
'Line Manager Submitted',
'Price Update',
'Approved',
'Completed')
GROUP BY PROMO.ROW_WID,
VOL.PRODUCT_WID,
VOL.PRODUCT_SOURCE_SYSTEM) PROD_SRC
WHERE PROD_SRC.SRC_CODE = R.SOURCE_SYSTEM
AND PROM.X_PROMO_PROD_CAT_ROW_ID = R.PRODUCT_ID
AND PROM.X_INTEGRATION_ID_PROMO = R.PROMO_ID
AND PROD_SRC.PROMO_WID = PROM.ROW_WID
AND PROM.X_SPEND_REASON = 'Trade Terms'
PLAN_TABLE_OUTPUT
Plan hash value: 2525984878
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 83 | 26477 | 79313 (2)| 00:07:40 | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 83 | 26477 | 79313 (2)| 00:07:40 | | |
| 3 | HASH GROUP BY | | 83 | 26477 | 79313 (2)| 00:07:40 | | |
| 4 | PX RECEIVE | | 83 | 26477 | 79312 (2)| 00:07:40 | | |
| 5 | PX SEND HASH | :TQ10004 | 83 | 26477 | 79312 (2)| 00:07:40 | | |
|* 6 | HASH JOIN | | 83 | 26477 | 79312 (2)| 00:07:40 | | |
| 7 | PX RECEIVE | | 96 | 12960 | 77708 (2)| 00:07:31 | | |
| 8 | PX SEND HASH | :TQ10003 | 96 | 12960 | 77708 (2)| 00:07:31 | | |
|* 9 | HASH JOIN | | 96 | 12960 | 77708 (2)| 00:07:31 | | |
| 10 | PX RECEIVE | | 14 | 420 | 775 (1)| 00:00:05 | | |
| 11 | PX SEND BROADCAST LOCAL | :TQ10002 | 14 | 420 | 775 (1)| 00:00:05 | | |
|* 12 | TABLE ACCESS BY LOCAL INDEX ROWID| WC_PLANNED_VOLUMES_F | 14 | 420 | 775 (1)| 00:00:05 | | |
| 13 | NESTED LOOPS | | 27005 | 3032K| 77270 (2)| 00:07:28 | | |
| 14 | NESTED LOOPS | | 1934 | 160K| 4243 (1)| 00:00:25 | | |
| 15 | MERGE JOIN CARTESIAN | | 1934 | 100K| 2306 (1)| 00:00:14 | | |
| 16 | SORT JOIN | | | | | | | |
| 17 | PX BLOCK ITERATOR | | 1 | 6 | 2 (0)| 00:00:01 | | |
| 18 | TABLE ACCESS FULL | WC_BUSINESS_DATE_D | 1 | 6 | 2 (0)| 00:00:01 | | |
| 19 | BUFFER SORT | | 1934 | 90898 | 2304 (1)| 00:00:14 | | |
| 20 | BUFFER SORT | | | | | | | |
| 21 | PX RECEIVE | | 1934 | 90898 | 2302 (1)| 00:00:14 | | |
| 22 | PX SEND BROADCAST | :TQ10000 | 1934 | 90898 | 2302 (1)| 00:00:14 | | |
|* 23 | TABLE ACCESS FULL | W_PLAN_PROMO_D | 1934 | 90898 | 2302 (1)| 00:00:14 | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | W_PLAN_PROMO_D | 1 | 32 | 1 (0)| 00:00:01 | | |
|* 25 | INDEX UNIQUE SCAN | W_PLAN_PROMO_D_P1 | 1 | | 0 (0)| 00:00:01 | | |
| 26 | PARTITION RANGE ITERATOR | | 90896 | | 21 (0)| 00:00:01 | 1 | KEY |
|* 27 | INDEX RANGE SCAN | WC_PLANNED_VOLUMES_F_M2 | 90896 | | 21 (0)| 00:00:01 | 1 | KEY |
| 28 | PX BLOCK ITERATOR | | 687K| 13M| 435 (3)| 00:00:03 | 1 | 37 |
| 29 | TABLE ACCESS FULL | WC_ACTUAL_VOLS_SHIP_F | 687K| 13M| 435 (3)| 00:00:03 | 1 | 37 |
| 30 | BUFFER SORT | | | | | | | |
| 31 | PX RECEIVE | | 209K| 36M| 1603 (2)| 00:00:10 | | |
| 32 | PX SEND HASH | :TQ10001 | 209K| 36M| 1603 (2)| 00:00:10 | | |
| 33 | TABLE ACCESS FULL | WC_RESOLUTION_FS | 209K| 36M| 1603 (2)| 00:00:10 | | |
Predicate Information (identified by operation id):
6 - access("VOL"."PRODUCT_SOURCE_SYSTEM"="R"."SOURCE_SYSTEM" AND "PROM"."X_PROMO_PROD_CAT_ROW_ID"="R"."PRODUCT_ID" AND
"PROM"."X_INTEGRATION_ID_PROMO"="R"."PROMO_ID")
9 - access("VOL"."DT_WID"="SHIP"."DT_WID" AND "VOL"."ORG_WID"="SHIP"."ORG_WID" AND "VOL"."PRODUCT_WID"="SHIP"."PRODUCT_WID"
AND "VOL"."PRODUCT_SOURCE_SYSTEM"="SHIP"."PRODUCT_SOURCE_SYSTEM")
12 - filter(("VOL"."PROMO_TYPE"='TTB' OR "VOL"."PROMO_TYPE"='TTI' OR "VOL"."PROMO_TYPE"='TTV') AND
"VOL"."DT_WID"<="DT"."BUSINESS_DT_WID" AND "PROMO"."X_ORG_WID"="VOL"."ORG_WID")
23 - filter("PROM"."X_SPEND_REASON"='Trade Terms')
24 - filter("PROMO"."X_PLANNING_DATA_TYPE"='Shipment' AND ("PROMO"."PROMO_STATUS"='Approved' OR
"PROMO"."PROMO_STATUS"='Completed' OR "PROMO"."PROMO_STATUS"='Line Manager Submitted' OR "PROMO"."PROMO_STATUS"='Planned' OR
"PROMO"."PROMO_STATUS"='Price Update'))
25 - access("PROMO"."ROW_WID"="PROM"."ROW_WID")
27 - access("PROMO"."ROW_WID"="VOL"."PROMO_WID")
56 rows selected.
Что вообще можно сделать?
И такой вопрос зачем нужен Group By в подзапросе? 
