I have the below query. which is slowing down the performance because of the subquery in it. I tried a lot to add Join instead of Subquery. but in vain. Can anybody tell me how can I rewrite this query using JOIN?
update Table_1
set status = 'Status_2'
where status ='status_1' and (col_1, col_2, col_3, nvl(col_4,0), col_5) in (
select col_1, col_2, col_3, nvl(col_4,0), col_5 from Table_2 where status ='Status_0');
Please see the SELECT * FROM table(DBMS_XPLAN.Display); below
Plan hash value: 1290346170
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 376 | 456 (3)| 00:00:06 |
| 1 | UPDATE | Table_1 | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 376 | 456 (3)| 00:00:06 |
| 4 | SORT UNIQUE | | 1 | 316 | 454 (3)| 00:00:06 |
|* 5 | TABLE ACCESS FULL | Table_2 | 1 | 316 | 454 (3)| 00:00:06 |
|* 6 | INDEX RANGE SCAN | Table1_INDEX | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| Table_1 | 1 | 60 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Does it work better when you do it like this?
update
(select Table_1.status
from Table_1
join Table_2 on
Table_1.col_1 = Table_2.col_1
and Table_1.col_2 = Table_2.col_2
and Table_1.col_3 = Table_2.col_3
and nvl(Table_1.col_4, 0) = nvl(Table_2.col_4, 0)
and Table_1.col_5 = Table_2.col_5
where Table_1.status = 'status_1'
and Table_2.status = 'Status_0')
set status = 'Status_2' ;