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.

Write a procedure which collects the
session statistics (redo generated ,roll back ghenerated) before
the start of delete and then run delete and then collect the statistics
after that .
Also run a trace to find if there are any waits.

Post the results.