问题描述
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 中的删除查询(带子查询)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！