Hello,
Can someone help me out with this. I'm running this SQL in 10.2.0.4.
Table volume:
TBL1 - 723K currently ( this can go up to 1-2 million )
TGT2 - 723K currently ( this can go upto 60M )
Stats are up-to-date on both the tables ( used 100% as estimate% Just to eliminate the stat quality here )
Have following SQL which involve 2 list partitioned table on the same column PKEY (i.e. Region) and running a merge statement and it's ignoring the hints I specified. Especially to use hash join. How to force the hash join in here?
Please note that ‘NONE' in "PKEY IN" is a non-existent partition and part of application logic that can't be changed.
MERGE
/*+ FULL(TGT2) USE_HASH(TGT2) PARALLEL(TGT2, 2) pq_distribute(tgt2, broadcast, broadcast) */*
*INTO TGT2*
*USING (SELECT /*+ MERGE FULL(TBL1) USE_HASH(TBL1) PARALLEL(TBL1,2) */
*FROM TBL1 TBL1*
*WHERE*
*TBL1.PKEY IN ('CA' , 'NONE','NONE','NONE')) TGT1*
*--TBL1.PKEY = 'CA' )TGT1*
*ON ( TGT2.srvc_id = TGT1.srvc_id*
*AND TGT2.accnt_id = TGT1.accnt_id*
*AND TGT2.PKEY = TGT1.PKEY*
*AND TGT2.PKEY in ('CA' , 'NONE','NONE','NONE') )*
*--AND TGT2.PKEY = 'CA' )*
*WHEN MATCHED THEN*
*UPDATE*
*SET TGT2.code1 = TGT1.code1,*
*TGT2.code2 = TGT1.code2,*
*TGT2.code2_old = TGT1.code2_old,*
*WHEN NOT MATCHED THEN*
*INSERT (TGT2.srvc_id, TG2.accnt_id, TGT2.code2, TGT2.code2_old TGT2.PKEY )*
*VALUES (TGT1.srvc_id, TGT1.accnt_id, TGT1.code2, TGT1.code2_old, TGT1.PKEY )*
*------------------------------------------------------------------------------------------------------------------------------------------------*
*| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |*
*------------------------------------------------------------------------------------------------------------------------------------------------*
*| 0 | MERGE STATEMENT | | 723K| 433M| 231K (1)| 00:46:17 | | | | | |*
*| 1 | MERGE | TGT2 | | | | | | | | | |*
*| 2 | PX COORDINATOR | | | | | | | | | | |*
*| 3 | PX SEND QC (RANDOM) | :TQ10000 | 723K| 737M| 231K (1)| 00:46:17 | | | Q1,00 | P->S | QC (RAND) |*
*| 4 | VIEW | | | | | | | | Q1,00 | PCWP | |*
*| 5 | NESTED LOOPS OUTER | | 723K| 737M| 231K (1)| 00:46:17 | | | Q1,00 | PCWP | |*
*| 6 | PX BLOCK ITERATOR | | 723K| 369M| 10 (80)| 00:00:01 |KEY(I) |KEY(I) | Q1,00 | PCWC | |*
*| 7 | TABLE ACCESS FULL | TBL1 | 723K| 369M| 10 (80)| 00:00:01 |KEY(I) |KEY(I) | Q1,00 | PCWP | |*
*| 8 | VIEW | | 1 | 441 | | | | | Q1,00 | PCWP | |*
*|* 9 | FILTER | | | | | | | | Q1,00 | PCWP | |
| 10 | PARTITION LIST INLIST| | 1 | 441 | 12392 (2)| 00:02:29 |KEY(I) |KEY(I) | Q1,00 | PCWP | |
|* 11 | TABLE ACCESS FULL | TGT2 | 1 | 441 | 12392 (2)| 00:02:29 |KEY(I) |KEY(I) | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("TBL1"."PKEY"='NONE' OR "TBL1"."PKEY"='CA')
11 - filter("TGT2"."accnt_id"="TBL1"."accnt_id" AND "TGT2"."srvc_id"="TBL1"."srvc_id" AND
"TGT2"."PKEY"="TBL1"."PKEY")
If I change IN clause with equality in both Main and Subquery as following , it does consider the hints and generate plan with hash join.
--AND TGT2.PKEY in ('CA' , 'NONE','NONE','NONE') )
AND TGT2.PKEY = 'CA' )
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 520 | 12433 (2)| 00:02:30 | | | | | |
| 1 | MERGE | TGT2 | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 1128 | 12433 (2)| 00:02:30 | | | Q1,02 | P->S | QC (RAND) |
| 4 | VIEW | | | | | | | | Q1,02 | PCWP | |
|* 5 | HASH JOIN OUTER | | 1 | 1128 | 12433 (2)| 00:02:30 | | | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 1 | 687 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10001 | 1 | 687 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 1 | 687 | 2 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWC | |
| 9 | TABLE ACCESS FULL | TBL1 | 1 | 687 | 2 (0)| 00:00:01 | 2 | 2 | Q1,01 | PCWP | |
| 10 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 11 | PX RECEIVE | | 723K| 368M| 12428 (2)| 00:02:30 | | | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10000 | 723K| 368M| 12428 (2)| 00:02:30 | | | | S->P | HASH |
| 13 | PARTITION LIST SINGLE| | 723K| 368M| 12428 (2)| 00:02:30 | KEY | KEY | | | |
| 14 | TABLE ACCESS FULL | TGT2 | 723K| 368M| 12428 (2)| 00:02:30 | 2 | 2 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TGT2"."PKEY"(+)="TBL1"."PKEY" AND "TGT2"."accnt_id"(+)="TBL1"."accnt_id" AND
"TGT2"."srvc_id"(+)="TBL1"."srvc_id")
Edited by: max71 on Feb 28, 2009 4:07 PM