gpt4 book ai didi

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

转载 作者:太空狗 更新时间:2023-10-30 01:49:41 24 4
gpt4 key购买 nike

我有以下查询。由于其中的子查询,这会降低性能。我尝试了很多来添加 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');

请参阅下面的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 |
------------------------------------------------------------------------------------------------------

最佳答案

这样做效果更好吗?

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

关于sql - 需要调整此 sql 查询以提高性能,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31130378/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com