2 Replies Latest reply: Jun 25, 2013 9:49 AM by spajdy RSS

    Periodic Table Clean


      Please,I has a table with 1.7 billion records...

      I want to create a process to create a periodic clean for this table....delete the records...

      what is the best way to I create this periodic clean?

      My database is in the version: Oracle Database 10g Enterprise Edition Release - 64bits


      Edited by: Fernando Soares on 23/05/2013 14:40
        • 1. Re: Periodic Table Clean

          First, determine the set you want to delete.  Is that set defined by date? by expired jobs?  Whatever is relevant to you.


          Then you can use that sql in a scheduler job.  Or you could package it in a shell script and run it from cron.


          You could even write a pl/sql procedure with appropriate error handling, and built in limiters (time, # of rows, etc.) and schedule that in a job, or call it from a cron.


          Performance may be the biggest issue in a table this size, so make sure your delete statement uses an appropriate index.  For example, if you are ageing out records by an event_date, make sure there is an index on event_date.





          • 2. Re: Periodic Table Clean

            If you always need delete all record then

            truncate table <tablename>.

            If you can define partitions in the way that partition hold data you want to purge then simply drop or truncate partition.

            Those operations are very fast.