Big Tables Purging

[mahi626]

Hi Tim,

In my Production database 3 big tables are have millions of records . One of the table inserts per day around 50k+ records into table and presently its hold the 300 days data, and its take ~4 mins to delete 1 day data. These tables are without partition and having one date column. Need to enable purge policy as daily basis and maintain 30 days datain all 3 tables. DB should be 100% availability no down time. Please suggest me better and safe solution.

Thanks in Advance.

[Tim...]

Hi.

You really don't have many options.

1) Switch to partitioning so you can drop a partition.
2) Use DBMS_REDEFINITION to do an online table redefinition, effectively creating a new table as a select of the rows you want to keep.

That's about it.

Large scale deletes/updates are really inefficient.

Cheers

Tim...