Re: "Delete From <table>" taking all day

David P wrote:
>
> On 20 Aug 1996 12:41:13 GMT, meskillw_at_ncr.disa.mil (Bill Meskill)
> wrote:
> If you use ORACLE7 and above, use TRUNCATE
> >Hi,
> >I have a 4000 row table that's taking quite a long
> >time to delete. What's going on?
> >
> >Bill Meskill
> >
> >FYI. I have my data and indexes separated on lots of
> >different disks and have allocated 80M for the
> >SGA (a full database export is 10M). There are only
> >2 people on the server.
> >
>
> "The opinions expressed here are my own, and are
> not necessarily that of Oracle Corporation or my employer"

Have you many FK's on this table? I delete 100,000 rows in each of three FK-related tables, cascade delete on the FK's , with pre-delete triggers on the tables to copy the data for archiving. A total of 300,000 rows takes about an hour to delete with all this activity.

It used to take much longer without the cascade delete. ORACLE will check your FKs. Deleting hits your rollback segments as noted by a previous reply. It also hits your indexes. On the above three tables (each have over 25 million rows in them) I have a total of 3 PK's and 7 indexes. Lots of work for ORACLE to do this deleting.

But in a 4000 row table? That should go in a flash by comparison. We are running an AS2100, 1Gig memory, 256 Meg SGA, twin CPUs, 45 Gig production database. Are you perhaps running under-configured hardware?

Wayne Linton
Database Manager,
Shell Canada Ltd. Received on Mon Aug 26 1996 - 00:00:00 CEST