Deletes -- performance issues 1

[toddyl]

Hi,

I have several tables where data is being loaded to all day/every day. About once a week I need to remove certain entries from these tables and I have created a procedure that selects the entries to delete and then steps through each table and deletes the appropriate records.

These tables are huge -- millions of rows each -- and the delete appears to be absolutely crawling. The delete is doing a simple:

delete from my_table where my_column = X;

Is there any way I can improve the performance of my delete statement?

Thanks,

toddyl

 

[Dagon]

Is there an index on my_column ? How many rows are being deleted as a proportion of the whole table ?

 

[toddyl]

Dagon,

Thanks for the reply.
You are indeed correct, there was no index on my_column as in a lot of tables this is a minor entry. It was a convenient way for me to identify the same record in all tables so I used this instead of the primary keys.

I have added indexes to all tables for my_column and the performance has increased.

Thanks,

Tom

 

[Beilstwh]

You also need to answer Dagon's second question. What percentage of the rows are deleted. If you are (for example) deleteing 90% of the rows, a MUCH faster way to delete would be to do the following.

create temp_table as
select *
from my_table
where my_column <> x;

truncate table my_table;

insert into my_table
select * from temp_table;

drop table temp_table;

Bill
Oracle DBA/Developer
New York State, USA