Delete Performance

contact info: [email protected]

Deleting rows from a smaller table (200 million rows x
200 byte record) was 10 times slower than deleting
rows from a bigger table (200 million rows x 800 byte
record).

The time to delete from the bigger table was about 3
minutes, while the time to delete from the smaller
table was about 48 minutes.

Below are some relevant information.

Context: AIX box with 18 CPUs, 32 GB of memory.
Oracle version: 8.

Two tables: T_hdr - 200 million rows, 200 byte record,


12 indexes, No foreign key.

T_dtl - 200 million rows, 800 byte record,
2 indexes, No foreign key.

Deletion of 100,000 records from each table. The
deletion criteria is based on a field that is the same

for both tables.

The code is in PL/SQL.

It took 3 min 40 secs to delete from bigger table
T_dtl and it took 48 min 36 secs to delete from the
smaller table T_hdr.

In terms of CPU consumption, it is at 3 percent or
less. The writes are about twice as much as the reads.



These deletes are done on a daily basis.

We thought that the B-Tree could be the problem
because delete leaves "holes" in the tree, and the
tree level starts to be deeper with insert. We rebuild

the tree but did not see any performance improvement
at all. In fact, it seems to be worse.

Would the number of indexes on the smaller table could
have affected the performance of deletes?

What's happening with this delete? The deletion of the

bigger table (with 2 indexes) is ten times faster than

the deletion of the rows from the smaller table (with
12 indexes).

Any help would be appreciated.