Re: delete takes too long

If the elapse time is proportional to the size of the tables the it seems almost certain one of the cascaded deletes is not using an index. Assuming the indexes are being used is no good, use explain and confirm it or show us the plan.

Best of luck

christian B wrote:

> 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 - 07:54:08 CST