Deleting large number of tuples from Oracle 10g Db.

[sudheer0886]

Hi,
I have one issue in the like I need to delete a large number of (2 million) tuples from a table of 5 million based on some criteria.The criteria(condit ion) is fetched from one base table,based on the fetched data tuple is deleted.similar ly I have to delete from 30 similar tables.

Like "Delete from employees where id IN(select id from empinfo where empinid=100)"

I tried some options
-Writing all the queries in a file and running the script
-Using a cursor (creating a index table) and deleting particular tuple from the table

Both giving me a large response time.

Main concern here is time. I need to delete in very quick time.
So I need a optimised and efficient solution for the issue.

Thanks In Anticipation.

[Dilip1983]

You can try using EXIST inplace of IN.

This might give some more performance.

Thanks
Dilip

[Dave44]

for large deletes it can be faster to create a new table of the data you want to keep and rename it rather than deleting the old data.