Re: delete takes too long

Hi,

Look for truncate

but previously disable constraints.

Vladimir

In article <36E61307.33C2_at_eskimo.com>,
  christian B <parents_at_eskimo.com> 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
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 11 1999 - 01:55:21 CST