如何在Oracle中优化删除查询(带有子查询)? [英] How to optimize delete query (with subquery) in Oracle?

问题描述

我有类似的查询

 delete from tableA 
 where tableA.fk in (select id 
                     from tableB 
                     where tableB.column1='somevalue' 
                     and tableB.date between date1 and date2)
   ;

表tableB包含近100,000,000条记录.所以

Table tableB contains near 100,000,000 records. So

select id 
from tableB 
where  tableB.column1='somevalue' 
and tableB.date between date1 and date2 

返回近1,000,000条记录.结果-删除根本不起作用-回滚段的大小存在问题.我无法增加细分的大小.

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.

如何执行?

推荐答案

如果要填充回滚段，则可能是因为事务中涉及的数据量.

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 
    )

为XXXX提供所需的值(10000)或其他值，然后循环执行此语句，并且不要忘记在每次迭代中提交.

give XXXX the value you want (10000) or whatever, and then loop executing this statement and don't forget to commit in each iteration.

您必须循环执行，直到delete语句返回0(受影响的行数)为止.

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

希望有帮助

这篇关于如何在Oracle中优化删除查询(带有子查询)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！