Deleting large records in oracle sql

Question

I have KPI_LOG table which contains 600000 record and the table is growing is everytime. I have TIME_STAMP coulmn in this table which specify when the record is created and i have to delete records which is older than 2 days from this table. I am using below query to perform deletion :

delete from KPI_LOG where SYSDATE - TIMESTAMP > 2;

But as the number of records is too large it will take lot of time if i use simply Delete query for this case. I am looking for faster deletion for this purpose but didn't find any solution.

Answer 1

delete from KPI_LOG where SYSDATE - TIMESTAMP > 2;

If you are deleting more number of rows then you are keeping in the table, then you could do a CTAS i.e. create table as select and then drop the old able and rename the new table.

Make sure, you have an index on the timestamp column.

For example,

CREATE INDEX tmstmp_indx ON KPI_LOG(TIMESTAMP )
/

CREATE TABLE KPI_LOG_NEW 
  AS 
 SELECT * FROM KPI_LOG WHERE TIMESTAMP > SYSDATE -2
/

DROP TABLE KPI_LOG
/

ALTER TABLE KPI_LOG_NEW RENAME TO KPI_LOG
/

Make sure you create all the necessary indexes and constraints on the new table.

Deleting rows doesn't reset the HIGH WATERMARK, by doing CTAS you have a fresh new table. Therefore, you don't have to scan all those rows below the high watermark which you would do in case of deletion.