How to optimize delete query (with subquery) in Oracle?(如何优化 Oracle 中的删除查询(带子查询)?)
问题描述
I have query like:
delete from tableA
where tableA.fk in (select id
from tableB
where tableB.column1='somevalue'
and tableB.date between date1 and date2)
;
Table tableB contains near 100,000,000 records. So
select id
from tableB
where tableB.column1='somevalue'
and tableB.date between date1 and date2
returns near 1,000,000 records. As result - delete doesn't work at all - problem with size of rollback segment. I cannot increase size of segment.
How it can be executed?
If you are filling the rollback segment it is probably because the amount of data involved in your transaction.
I'd split into several chunks and commit each of them, something like :
delete from tableA where tableA.fk in (
select id from (
select id from tableB where
tableB.column1='somevalue' and tableB.date between date1 and date2 and id in (select distinct fk from tableA.fk)
)
where rownum < XXXX
)
give XXXX the value you want (10000) or whatever, and then loop executing this statement and don't forget to commit in each iteration.
You have to loop until the delete statement returns 0 (number of affected rows).
UPDATED QUERY As noticed, this will not give the best performance, but will solve the rollback segment problem
Hope it helps
这篇关于如何优化 Oracle 中的删除查询(带子查询)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:如何优化 Oracle 中的删除查询(带子查询)?
基础教程推荐
- SQL Server 2016更改对象所有者 2022-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01