需要调整此SQL查询以提高性能

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?

我有以下查询。由于其中的子查询，这会降低性能。我尝试了很多来添加Join而不是Subquery。但是徒劳无功任何人都可以告诉我如何使用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

请参阅SELECT * FROM表（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`

2 个解决方案

#1

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

#2

With a with statement and the Materialized hint you can preload the data of table_2 in a global temporary table. This might improve the performance of your query.

使用with语句和Materialized提示，您可以在全局临时表中预加载table_2的数据。这可能会提高查询的性能。

#1