best way to delete

sheema · Post by **sheema** » Thu Oct 23, 2008 4:29 pm

Hi,

We have a table which has more than 100 million rows (Oracle),we get a weekly file of around 30 million rows.Our requirement is to see if there are any rows from the file which exist in the table and delete them.Delete is done depending on the key columns.Which would be a better approach to do this.
I have tried reading the file and delete rows by selecting Write Method of Delete rows in Oracle Enterprise stage,but it is very slow.Is there a better way of deleting the records.

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Oct 23, 2008 4:47 pm

No, given that the delete is conditional.

chulett · Post by **chulett** » Thu Oct 23, 2008 5:10 pm

What percentage of the 30M will be deletes? It could conceivably be more efficient to rebuild the table, copying over only the records to be kept. Then rename the tables to swap the new for the old.