PL/SQL script to delete millions of rows from a table based on date
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: PL/SQL script to delete millions of rows from a table based on date

  1. #1
    Join Date
    Jan 2012
    Posts
    2

    PL/SQL script to delete millions of rows from a table based on date

    HI All

    I am very new to the database stuff. Correct me if I am wrong.

    Is there a possiblity to write a script in oracle which deletes the rows from a table based on the age. i.e., I want to delete the rows which are >60 days. I have a table with millions of rows in it and I want to keep only the latest two months rows. I have the following table with column names as
    ERROR_IND
    FEED_ID
    QUEUE_ID
    FEED_DT_TM -date datatype .

    I have written few of the scripts to implement it.

    1.DELETE FROM table_name WHERE sysdate - feed_dt_tm > 60;

    2. declare
    begin
    create table hif_psfeed1
    as
    select *
    from hif_psfeed
    where feed_dt_tm > (sysdate-60);

    drop table hif_psfeed;
    alter table hif_psfeed1 rename to hif_psfeed;
    end;
    /


    3. declare
    job_delete in number;
    begin
    sys.dbms_job.submit
    (job=>job_delete,
    what=>'begin delete from hif_psfeed WHERE sysdate - feed_dt_tm >''60''; end;',
    next_date=>trunc(sysdate)+1,
    interval=>'trunc(sysdate)+1'
    );
    commit;
    end;
    /


    The first one and second seems to take long time and I am getting errors while running the 3rd one. Please advise me on this. I need to keep running this script everyday to keep only the latest 2 months of data in the table

    Thanks In advance

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    How many rows in the table?
    How many rows are expected to be deleted?
    Is this a one off process or are you planning to run it in a montly basis or so?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2012
    Posts
    2

    PL/SQL script to delete millions of rows from a table based on date

    @PAVB: There 19 million rows in the table. If I consider only the latest two months , it will be around 100,000 rows. The remaining rows are expected to be deleted. Yes, I am plannning it to perform weekly or monthly basis.

  4. #4
    Join Date
    Apr 2001
    Posts
    99
    Look at this site It gives lot of information about this problem
    http://asktom.oracle.com/pls/apex/f?...:2345591157689
    Naeem

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would suggest to do reverse purging at least for the first run where about 18.9 million rows have to be "deleted" out of a total of 19 million.

    In this context a version of Option #2 is the way to go.

    I would...
    1- Pre Create hif_psfeed1 as nologging, with no indexes
    2- Insert into it the 100,000 targetted-to-stay-alive rows probably using /*+ append */ hint.
    3- Rename table hif_psfeed as hif_psfeed_old and keep it alive for a couple of days (you never know).
    4- Rename table hif_psfeed1 as hif_psfeed.
    5- Create all needed indexes and fix referential integrity if needed.
    6- Alter table to logging.
    7- Gather fresh stats on table and related indexes.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width