Hello ,
We have query which is processing millions of rows through sequential reads , index scan of xtable_id_IDX is processing around 20 million records , index ytable_pk processing fewer rows.
I believe as the first rowsource is processing a large set of data , nested loop is costlier, so wanted to try using USE_HASH hint to force the smaller ytable as build table and xtable as probe table , but optimizer is not picking the USE_HASH hint , not sure if it is due to not in/subquery.
Could you please assist if I can force the optimizer to use hash join with ytable(ytable_pk rowsource) as build table and probe the other one.
It is fine if we need to use full hints to full scan both tables instead of indexes, just wanted to see if we can force HASH join in this case.
PS: Modified the table names from actual plan
UPDATE Xtable t SET xtable_id = X
WHERE (xtable_id) NOT IN (SELECT xtable_id FROM ytable)
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | UPDATE | xtable | | | | |
| 2 | NESTED LOOPS ANTI | | 1 | 20 | 1 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | xtable_id_IDX | 1 | 13 | 0 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| ytable_PK | 1 | 7 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("xtable_id"="ytable_id")
Thanks for your time.