Re: How do I speed up deleting from a very dynamic table

If you want to delete ALL of the rows in the table you can do truncate table blah;
or truncate table blah reuse storage;
You cannot do truncate table where ...
You could do delete from blah where rowid=...; That might be fastest. If you know what rows to delete.

If the nature of the table is that the rows could have a time on them and you could delete those rows that are older than X amount of time then you could kick off a job that would scan that table and delete rows that are older than X. As long as inserts would not generate collisions.

For example, lets say I have a table that holds "temporary" data like this: insert_time date,
client_number number,
some_data varchar2(...)...
I could have a dbms_job that just runs and deletes rows from this table where the insert_time is < (sysdate-1/96) It might be faster given that you are deleting more rows at a gulp.

Jim

Rod Stewart wrote in message <771ijd$q0s$1_at_platinum.sge.net>...
>Dear friends and colleagues,
>
>I have a table that is used as a dynamic log to record various types of
>search results. The data is very dynamic, with data moving in and out at a
>very rapid rate. Our problem lies with the speed of deletion. Is there
>some way to speed the deletion, perhaps by somehow skipping / circumventing
>all the rolling back etc which we don't require?
>
>Thanks in advance,
>
>Rod J. Stewart
>
>
Received on Thu Jan 07 1999 - 06:52:04 CST