You must tune this sql query to improve performance

Question

I have the following query. which slows down performance due to a subquery in it. I tried a lot to add Join instead of Subquery. but in vain. Can someone tell me how I can 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'); 

See below SELECT * FROM table(DBMS_XPLAN.Display);

 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 | ------------------------------------------------------------------------------------------------------`enter code here` 

Answer 1

Does it work better when you do it this way?

 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' ; 

Answer 2

Using the with statement and the Materialized tooltip, you can preload table_2 data into the global temporary table. This can improve the performance of your request.