Speedy DML Operations

Hi !
We have a telephone billing database on Intel Xeon 2.4 GHz system and Oracle 8i. There is one table (holding details of customer's long distance calls) that accumulates over 14 million (100 bytes) record each month. We only need to keep data for 3 months in this table and then delete the oldest i.e. on average there are around 42 million records in this table.

1. Insert Problem
The table is properly indexed so quering is fine but new record insertion is a hell of slow job. There are 330 inserts per second (takes 11 hours to load new data).

2. Delete Problem
Data more than 3 months old is to be deleted. Here I really want speedy execution. Currently it is 450 delets per second(takes 8 hours to delete old data). Is it possable to delete the data without a rollback segment being filled? I don't need rollback feature , i just need speedy deletion.

Any Suggestion/Help shall be highly appreciated.

Thanks!

This from another previous thread on a related topic:

Check out "Oracle Performance Tuning" ISBN 1565922379 by Gurry and Corrigan and/or any information you can find regarding partitioned views and parallel queries. Recommends creating several smaller tables seperated by date and creating partitioned view across the tables. This should also help you with a purging strategy as a table with an out-of-date range can be dropped easily and a current table created with only minimal recoding. There is no in-depth discussion on that particular topic in this book, but it is a very good resource for performance tuning at the database, pl/sql and form level in general.

Your inserts and analyze would be much quicker on the smaller tables (1 per month or so). And the delete would be incredibly quicker because you just have to drop the oldest table

Thanks for your suggestion Kraeg! I have split the data of this huge table into 3 tables (one for each month, each holds around 14 million records now).

deletion of old data(by dropping the oldest table) is now a few seconds job .

I need to know how do I speed up the inserts (indexes are making them slow but they cann't be removed coz it will slow down the quering).

If you are doing your inserts as a batch, you could drop the indexes, load your data, then rebuild the indexes.