Best way to delete records

[Nugpot]

Hi,
I have to delete records which are older than 6 months in a audit table. The number of rows older than 6months is 40 million.
A direct delete is running out of rollback segment space, though I have added alot of space and I don't know if it is the right way to do it.
In order to issue commits, I have to use cursors. I think it will take forever to complete.
The option of creating a temporary table, truncating the audit table and inserting from the temp back to audit is my last option.

Can anyone suggest a easier and faster way to do this?

Thanks
Vj

[Zaggy]

You want to distribute the deletes over many connections/transactions to reduce your freelists from becoming inbalanced. If you do not seperate the deletes one of your freelist's will become huge with old blocks and the others will be using mostly new blocks. So, you might want to create a PL/SQL procedure that uses automatous transactions or use a few SQL*Plus sessions using SQL, but spread out the load. If you are not using freelists then I am wasting my time.

[marist89]

Deleting with cursors is probably your best option. You could setup multiple cursors that delete different ranges of rows and let them run in parallel.

In the future, you can setup your audit table as a partitioned table. That way, you can truncate a partition at a time without affecting the rest of the table.

[pipo]

something like (made it in 30 seconds, there must be some mistakes) :

select trunc(sysdate - min(date)) into var_begin from table;
select trunc(sysdate - 180) into var_end from table;

for i in var_begin .. var_end
loop
delete table where trunc(date) = trunc(sysdate-i);
commit;
end loop;

idea = deleting day by day ...

I dunno if it would be faster than using a temp table

[Nugpot]

thanks guys...

[grjohnson]

You should really consider setting you audit table in a partitioned table. Prtitioned on Month, then after 11 months, you just truncate the oldest partition.

[sambavan]

That was a good one!!

Sam

[Nugpot]

ok, I will look into setting that up. thanks. By the way, could you tell me which query would be faster and eficient...

Hi,
Could you please tell me which query would be faster and efficient. I have 40 million rows to delete. I have a index on
modified_dtm

Thanks


declare
AMonth DATE;
begin

ROLLBACK;
SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS01;

-- within 6 month old for DMA, DA, and AH audit tables
AMonth := ADD_MONTHS(TRUNC(sysdate, 'MM'),-6);

loop
if (AMonth < to_date('19990101','yyyymmdd')) then
exit;
end if;

dbms_output.put_line( 'DELETE DMA audit table, AMonth ' || to_char(gasMonth, 'YYYYMMDD') );
DELETE Table
WHERE TRUNC(modified_dtm,'MM') = AMonth;
COMMIT ;
dbms_output.put_line( 'DONE .....' );
AMonth := ADD_MONTHS(AMonth, -1);
end loop;
end;

OR

begin
SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS01;
delete table where
modified_dtm < to_date('20-FEB-2000 23:59:59', 'DD-MON-YYYY hh24:mi;ss');
commit;
end;


Thanks

[tamilselvan]

I think You do not need to recover the table in case if the table data is accidently deleted.
So, Set the table in NOLOGGING mode.

ALTER TABLE AUDIT_TABLE NOLOGGING.

This will ensure that the minimum redo/undo info is generated againt any DML on that table.

DELETE FROM AUDIT_TABLE
WHERE DT_COLUMN BETWEEN (starting date) and (Ending Date);

COMMIT;

This method is faster than any other method I foresee.

[molonede]

One other option, depending on how many rows are left after the delete would be to

select rows into new table that you want to keep.
Truncate old table
insert saved rows back in the original table.