select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
SQL
DELETE FROM A
WHERE TR_STATUS IN ('C', 'R')
OR A.TAX_AUDIT_RECORD_ID IN ( SELECT B.TAX_AUDIT_RECORD_ID FROM B WHERE A.TAX_AUDIT_RECORD_ID = B.TAX_AUDIT_RECORD_ID);
Current execution plan
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 5639K| 123M| 14156 (1)| 00:02:50 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ20002 | 5639K| 123M| 14156 (1)| 00:02:50 | | | Q2,02 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | A | | | | | | | Q2,02 | PCWP | |
| 4 | PX RECEIVE | | 5639K| 123M| 14156 (1)| 00:02:50 | | | Q2,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ20001 | 5639K| 123M| 14156 (1)| 00:02:50 | | | Q2,01 | P->P | RANGE |
| 6 | DELETE | A | | | | | | | Q2,01 | PCWP | |
| 7 | BUFFER SORT | | | | | | | | Q2,01 | PCWC | |
| 8 | PX RECEIVE | | 5639K| 123M| 14156 (1)| 00:02:50 | | | Q2,01 | PCWP | |
| 9 | PX SEND HASH (BLOCK ADDRESS)| :TQ20000 | 5639K| 123M| 14156 (1)| 00:02:50 | | | | S->P | HASH (BLOCK|
|* 10 | FILTER | | | | | | | | | | |
| 11 | PX COORDINATOR | | | | | | | | | | |
| 12 | PX SEND QC (RANDOM) | :TQ10000 | 5639K| 123M| 14156 (1)| 00:02:50 | | | Q1,00 | P->S | QC (RAND) |
| 13 | PX BLOCK ITERATOR | | 5639K| 123M| 14156 (1)| 00:02:50 | 1 | 32 | Q1,00 | PCWC | |
| 14 | TABLE ACCESS FULL | B | 5639K| 123M| 14156 (1)| 00:02:50 | 1 | 32 | Q1,00 | PCWP | |
| 15 | PARTITION RANGE ALL | | 1 | 21 | 3 (0)| 00:00:01 | 1 | 8 | | | |
|* 16 | INDEX RANGE SCAN | PK_B | 1 | 21 | 3 (0)| 00:00:01 | 1 | 8 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - filter("A"."TR_STATUS"='C' OR "A"."TR_STATUS"='R' OR EXISTS (SELECT 0 FROM "B" WHERE
"B"."TAX_AUDIT_RECORD_ID"=:B1))
16 - access("B"."TAX_AUDIT_RECORD_ID"=:B1)
There is no relationship (pk/fk) between both of these tables. PK are being populated by an oracle sequence in both the tables. There are lot of matching ids in both the tables though (1Million).
I dont know why Oracle is using FILTER instead of using join methods. I tried using USE_HASH, USE_NL, USE_SJ, but no luck.
Join column (TAX_AUDIT_RECORD_ID) is NOT NULL in both table definition.
I even tried UNNEST hint in sub-query, but again, i did not get any success.