Delete statement was very slow in Oracle

Obviously, a delete operation will take longer than a select, but that doesn't account for the difference you see.

It sounds like additional code is being run upon the delete, which indecates there may be triggers on the table that are also running. Can you check this?

There can be many reasons:

• Server load (unlikely because the SELECT is fast)
• Triggers (see here how to list them for a table).
• Foreign keys (List of foreign keys and the tables they reference)
• A lot of data in each row (LOBs, many columns).
• Someone is locking rows in the table that you'd like to delete (or the whole table). See this blog post how to list locks. This discussion might also help.

If the foreign keys are the problem, the usual solution is to add indexes on the foreign column: For each delete, Oracle needs to check whether this would violate a foreign key relation.

To delete means to change the table's content. And this means, that after each deleted row all indexes must be updated and all foreign-key references must be checked. This can take a very long time!

Maybe this helps:

Make a copy of that table without any references, triggers and additional indexes. Then do this:

insert into new_table (field1, field2, ...) values (
    select field1, field2, ...
    from daily_au_by_service_summary 
    where summary_ts < to_date('09-04-2012','dd-mm-yyyy') 
);

If the fields in the tabels are defined in identical order, this might work too:

insert into new_table values (
    select *
    from daily_au_by_service_summary 
    where summary_ts < to_date('09-04-2012','dd-mm-yyyy') 
);

After that:

truncate daily_au_by_service_summary

and then:

insert into daily_au_by_service_summary (field1, field2, ...) values (
    select field1, field2, ...
    from new_table; 
);

New Table is not needed any longer:

drop new_table;