procedure to delete several records from several tables - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: procedure to delete several records from several tables

  1. #11
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    P.S.: can't do bulk insert since rows are created in those tables non-stop. If I do bulk insert I'll lose rows that are created when create a new table --> move the desired records --> delete old table --> rename new tables

  2. #12
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by crysu View Post
    1. No
    2. Between 70 and 90%
    This is a text book case where you may want to consider deleting data by doing inserts instead of doing deletes.

    Here is the scenario...
    You want to delete 70%+ of the rows sitting on table_a
    1- Rename table_a as table_b
    2- Create table_a as select * from table_b where v_column_name > sysdate - v_retention_days
    3- Build indexes
    4- Gather fresh stats.
    5- After business approval drop table_b

    This is faster, generates less redo and ensures tables and indexes are not fragmented at the end of the process.

    Can you afford a maintenance window to do it?
    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. #13
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by crysu View Post
    P.S.: can't do bulk insert since rows are created in those tables non-stop. If I do bulk insert I'll lose rows that are created when create a new table --> move the desired records --> delete old table --> rename new tables
    Okay ... when everything fails you have to rely on "delete" to "delete" rows.

    Please find below a template I foiund years ago in Metalink, feel free to customize as needed.

    Code:
    ------------------------------------------------------  
    -- Create procedure
    --
    -- Remember to replace empno=' || empno
    -- by your own purge condition
    CREATE OR REPLACE PROCEDURE delete_tab (tablename IN VARCHAR2, 
                                                empno IN NUMBER  , 
                                                nrows IN NUMBER    ) IS
     sSQL1    VARCHAR2(2000);
     sSQL2    VARCHAR2(2000);
     nCount    NUMBER; 
    BEGIN
      nCount := 0;
      sSQL1:='delete from '|| tablename || 
             ' where ROWNUM < ' || nrows || ' and empno=' || empno; 
      sSQL2:='select count(ROWID) from ' || tablename || 
             ' where empno= ' || empno;
      LOOP
        EXECUTE IMMEDIATE sSQL1;
        EXECUTE IMMEDIATE sSQL2 INTO nCount;
        DBMS_OUTPUT.PUT_LINE('Existing records: ' || to_char(ncount) );
        commit;    
        EXIT WHEN nCount = 0;
      END LOOP;
    END delete_tab;
    /
    ------------------------------------------------------  
    -- Execute above created procedure
    --
    -- In this example we are asking
    -- to delete from table BIG_EMP,
    -- all rows belonging to empno=7369
    -- in batches of 5000 rows
    SQL> execute delete_tab('big_emp',7369,5000)
    Existing records: 60537
    Existing records: 55538
    Existing records: 50539
    Existing records: 45540
    Existing records: 40541
    Existing records: 35542
    Existing records: 30543
    Existing records: 25544
    Existing records: 20545
    Existing records: 15546
    Existing records: 10547
    Existing records: 5548
    Existing records: 549
    Existing records: 0
    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.

  4. #14
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by PAVB View Post
    This is a text book case where you may want to consider deleting data by doing inserts instead of doing deletes.

    Here is the scenario...
    You want to delete 70%+ of the rows sitting on table_a
    1- Rename table_a as table_b
    2- Create table_a as select * from table_b where v_column_name > sysdate - v_retention_days
    3- Build indexes
    4- Gather fresh stats.
    5- After business approval drop table_b

    This is faster, generates less redo and ensures tables and indexes are not fragmented at the end of the process.

    Can you afford a maintenance window to do it?
    This will be the perfect case, but it's about a production database that can't be stopped or to interrupt activity to allow me a maintenance window.

    I know that recreating the table with only the data that has to remain it is faster and more reliable but I'm afraid of losing rows.

  5. #15
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Check previous post sync issue
    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.

  6. #16
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by PAVB View Post
    Check previous post sync issue
    I'll give it a try.

    Thanks for sharing this ... and also thanks for your time

    Best regards

  7. #17
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Glad to help.
    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.

  8. #18
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by PAVB View Post
    Glad to help.
    I tested the script and seems to do the job excellent until now.

    Thanks again

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