************************************************************************************************************************** Before hash hint::: ************************************************************************************************************************** SQL> explain plan for MERGE /*+ parallel (rq, 16) */ INTO DWH_BILL_DET rq USING (SELECT rated_que_rowid, detail_rerate_flag_code, rerate_sel_key, rerate_adj_inv_code FROM prd_rerate_chg_que PARTITION(SYS_P62532) WHERE rerate_adj_inv_code >= 0 AND rerate_sel_key NOT IN (SELECT rerate_sel_key FROM prd_rerate_sel WHERE status = 'Cancelled' AND rerate_batch_key = 24)) rr ON (rq.rowid = rr.rated_que_rowid and trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11') WHEN MATCHED THEN UPDATE SET rerate_flag_code = rr.detail_rerate_flag_code, rerate_sel_key = rr.rerate_sel_key, rerate_adj_inv_code = rr.rerate_adj_inv_code WHEN NOT MATCHED THEN INSERT (file_key) VALUES (NULL); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 ; Explained. SQL> set linesize 135 SQL> / PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 933001228 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1 | 481 | 25433 (2) | 00:05:06 | | | | 1 | MERGE | DWH_BILL_DET | | | | | | | | 2 | VIEW | | | | | | | | |* 3 | HASH JOIN ANTI SNA | | 1 | 487 | 25433 (2) | 00:05:06 | | | | 4 | NESTED LOOPS OUTER | | 1 | 471 | 25428 (2) | 00:05:06 | | | | 5 | PARTITION HASH SINGLE | | 1 | 29 | 25427 (2) | 00:05:06 | 1 | 1 | PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------- |* 6 | TABLE ACCESS FULL | PRD_RERATE_CHG_QUE | 1 | 29 | 25427 (2)| 00:05:06 | 1 | 1 | |* 7 | TABLE ACCESS BY USER ROWID| DWH_BILL_DET | 1 | 442 | 1 (0)| 00:00:01 | ROWID | ROWID | |* 8 | TABLE ACCESS FULL | PRD_RERATE_SEL | 1 | 16 | 5 (0)| 00:00:01 | | | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("RERATE_SEL_KEY"="RERATE_SEL_KEY") 6 - filter("RERATE_ADJ_INV_CODE">=0) 7 - filter(TRUNC(INTERNAL_FUNCTION("RQ"."CALL_START_DTTM"(+)))<='31-JAN-11' AND PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------- TRUNC(INTERNAL_FUNCTION("RQ"."CALL_START_DTTM"(+)))>='01-JAN-11') 8 - filter("RERATE_BATCH_KEY"=24 AND "STATUS"='Cancelled') Note ----- - dynamic sampling used for this statement (level=6) 28 rows selected. SQL> select /*+ parallel(a,32) */ count(1) from prd_rerate_chg_que a; COUNT(1) ---------- 103080202 SQL> select /*+ parallel(a,32) */ count(1) from dwh_bill_det a; COUNT(1) ---------- 113643844 SQL> select /*+ parallel(a,32) */ count(1) from prd_rerate_chg_que PARTITION(SYS_P62532); COUNT(1) ---------- 1609859 ************************************************************************************************************************** After hint::: ************************************************************************************************************************** MERGE /*+ hash(rq) parallel(rq 8) */ INTO DWH_BILL_DET rq USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) */ rated_que_rowid, detail_rerate_flag_code, rerate_sel_key, rerate_adj_inv_code FROM prd_rerate_chg_que PARTITION(SYS_P62532) WHERE rerate_adj_inv_code >= 0 AND rerate_sel_key NOT IN (SELECT /*+ unnest use_hash(prd_rerate_sel) parallel(prd_rerate_sel 8) */ rerate_sel_key FROM prd_rerate_sel WHERE status = 'Cancelled' AND rerate_batch_key = 24)) rr ON (rq.rowid = rr.rated_que_rowid and trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11') WHEN MATCHED THEN UPDATE SET rerate_flag_code = rr.detail_rerate_flag_code, rerate_sel_key = rr.rerate_sel_key, rerate_adj_inv_code = rr.rerate_adj_inv_code WHEN NOT MATCHED THEN INSERT (file_key) VALUES (NULL) SQL> set linesize 200; SQL> / PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1439365171 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1 | 481 | 3532 (1) | 00:00:43 | | | | | | | 1 | MERGE | DWH_BILL_DET | | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 487 | 3532 (1) | 00:00:43 | | | Q1,01 | P->S | QC (RAND) | | 4 | VIEW | | | | | | | | Q1,01 | PCWP | | |* 5 | HASH JOIN ANTI SNA | | 1 | 487 | 3532 (1) | 00:00:43 | | | Q1,01 | PCWP | | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 6 | NESTED LOOPS OUTER | | 1 | 471 | 3526 (1) | 00:00:43 | | | Q1,01 | PCWP | | | 7 | PX BLOCK ITERATOR | | 1 | 29 | 3526 (1) | 00:00:43 | 1 | 1 | Q1,01 | PCWC | | |* 8 | TABLE ACCESS FULL | PRD_RERATE_CHG_QUE | 1 | 29 | 3526 (1) | 00:00:43 | 1 | 1 | Q1,01 | PCWP | | |* 9 | TABLE ACCESS BY USER ROWID | DWH_BILL_DET | 1 | 442 | 1 (0) | 00:00:01 | ROWID | ROWID | Q1,01 | PCWP | | | 10 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 11 | PX RECEIVE | | 1 | 16 | 5 (0) | 00:00:01 | | | Q1,01 | PCWP | | | 12 | PX SEND BROADCAST | :TQ10000 | 1 | 16 | 5 (0) | 00:00:01 | | | | S->P | BROADCAST | |* 13 | TABLE ACCESS FULL | PRD_RERATE_SEL | 1 | 16 | 5 (0) | 00:00:01 | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- 5 - access("RERATE_SEL_KEY"="RERATE_SEL_KEY") 8 - filter("RERATE_ADJ_INV_CODE">=0) 9 - filter(TRUNC(INTERNAL_FUNCTION("RQ"."CALL_START_DTTM"(+)))<='31-JAN-11' AND TRUNC(INTERNAL_FUNCTION("RQ"."CALL_START_DTTM"(+)))>='01-JAN-11') 13 - filter("RERATE_BATCH_KEY"=24 AND "STATUS"='Cancelled') Note ----- - dynamic sampling used for this statement (level=6) 33 rows selected. SQL>