如何优化 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