Re: DELETE statement performance problem

tanya8g_at_yahoo.com (Tanya) wrote in message news:<1e0a87eb.0202211346.5fb7cd3_at_posting.google.com>...
> Hi,
>
> We have a table in PROD
> CREATE TABLE address
> (
> address_id NUMBER(12) NOT NULL,
> street_line1 VARCHAR2(40),
> street_line2 VARCHAR2(40),
> city VARCHAR2(40),
> state VARCHAR2(2),
> phone_area VARCHAR2(3),
> phone_num VARCHAR2(7),
> phone_ext VARCHAR2(6),
> zip_code VARCHAR2(9)
> )
> /
>
> 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.

Just a guess. If the address_id is referenced by some foreign keys in other tables and the index is missing on some of those foreign keys column, deleting from address table will cause full table scans on the child tables. It might be the reason why delete is running slow. I guess when you tried update, you were not updating the address_id column.

hth,
Mike Received on Fri Feb 22 2002 - 09:23:41 CST