DELETE statement performance problem

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. Received on Thu Feb 21 2002 - 15:47:00 CST