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.
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.
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://oracle-base.com
My blog: http://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 10 guests