delete takes too long

Hi,

I have a problem with a delete taking a really long time.

here is the situation:

three tables linked with foreign key constraints

lbk_trip

     |
      ---< lbk_tow
                |
                -----< lbk_catch

the row counts of the tables are about

lbk_trip 60,000
lbk_tow  320,000
lbk_catch 1,500,000

I just issue a delete on lbk_trip and let it cascade through the foreign key constraint.

The time it takes to complete that delete not only depends on the total number of records deleted, but also on the overall size of the tables. A typical delete would delete 4000 records from lbk_trip. At its current size that takes about 2-3 hrs, as opposed to ca. 5-10 minutes when it only had 10,000 records. Now I can see that it will take longer to find the associated child records to be deleted, if the tables are bigger, but assuming Oracle uses the available indexes to do that, I wouldn't expect it to grow that dramatically.

It's now almost as time consuming to delete 5% of the records as it is to drop the tables and refill 95% from text files, performing a bunch of data validation in the process.

Is there a way to do this more efficiently?

Thanks

     Christian Received on Wed Mar 10 1999 - 00:37:06 CST