Re: DELETE statement performance problem

Deletes can generate a great deal more rollback activity than updates.

Have you checked the access path. If you analyzed the table before it was populated, you may find you're doing full table scans. Your figures is suspiciously close to my rule of thumb of about 2.5 seconds per million records.

I would expect a single row delete on a table this size to take less time than is reported by sqlplus set timing on e.g.

SQL> delete from fred where id = 6000000;

1 row deleted.

Commit complete.
Elapsed: 00:00:00.00

Interestingly, with 9i, I could not re-produce the problem although I have encountered it a few years ago with an earlier version.

In any case, try re-analyzing the table.

> with Primary Key on address_id.
> There are no any other Keys (unique or foreign) on this table.
> No any triggers also.
>
> table has about 2 500 000 records.
> we use sequence to generate address_id for this table. Now the next
> sequense value is about 4 500 000.
>
> there are a lot of DELETE and INSERT statements running (online txns)
> on this table - about 10 000 of each per day.
>
> We got a DELETE performance problem for this table.
>
> statement:
> DELETE *
> FROM address
> WHERE address_id = 12345;
>
> it takes about 5.5 sec to complete it (before we rebuilt index it was
> 10-12 sec per DELETE statement)
> it's too much for us.
> we can afford only 0.5 sec per statement.
>
> What I cannot understand is that the same INSERT/UPDATE/SELECT
> statements complete during 0.3 - 0.5 sec.
>
> The next we going to do is to partition this table (range partition on
> address_id).
> But first I'd like to find out why it is only DELETE course us such
> problems and INSERT/UPDATE/SELECY work just fine?
> Does anybody have any ideas/suggestions?
>
> yes, almost forgot, we have Oracle 8.1.7
>
> Thanks a lot,
> Tanya.
Received on Thu Feb 21 2002 - 17:34:16 CST