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

Thread: procedure to delete several records from several tables

Hybrid View

  1. #1
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22

    procedure to delete several records from several tables

    First of all I would like to say Hi to everyone here.

    Second thing:

    I have a procedure which I want to delete several records from several tables. Those tables from were I want to delete rows have names stored in another table in database along with the column name that is used for condition.
    Main table DB_CLEANUP_PARAMETERS (the one that stores tables names for other) is like this:

    table_name varchar2(30)
    date_field_name varchar2(30)
    retention_days number

    I wrote the following procedure to do the things
    Code:
    CREATE OR REPLACE PROCEDURE DB_CLEANUP_PROCEDURE_2 IS
      i INTEGER;
      TYPE j IS TABLE OF ROWID;
      row_id j;
      v_table_name VARCHAR2(30);
      v_column_name VARCHAR2(30);
      v_retention_days NUMBER(9);
      v_chunk_size INTEGER := 1000;
    BEGIN
     FOR rec IN (SELECT dcp.table_name, dcp.date_field_name, dcp.retention_days
                    FROM db_cleanup_parameters dcp) LOOP
        v_table_name     := rec.table_name;
        v_column_name    := rec.date_field_name;
        v_retention_days := rec.retention_days;
    
        EXECUTE IMMEDIATE 'SELECT ROWID FROM ' || v_table_name || ' WHERE ' ||
                          v_column_name || ' < (SYSDATE - ' || v_retention_days || ')'
                          BULK COLLECT INTO row_id;
        FORALL i IN 1 .. row_id.COUNT()
        EXECUTE IMMEDIATE 'DELETE FROM '||v_table_name||' WHERE ROWID = '''|| row_id(i)||''' AND ROWNUM <='||v_chunk_size;
    
        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
      END LOOP;
    COMMIT;
    END DB_CLEANUP_PROCEDURE_2;
    Main problem is that I get the following error:

    Code:
    Compilation errors for PROCEDURE ONAIR.DB_CLEANUP_PROCEDURE_2
    
    Error: PLS-00801: internal error [*** ASSERT at file pdw4.c, line  589; Unknown expression Expr = 283.; DB_CLEANUP_PROCEDURE_2__ONAIR__P__287291[20, 5]]
    Line: 20
    Text: EXECUTE IMMEDIATE 'DELETE FROM '||v_table_name||' WHERE ROWID = '''|| row_id(i)||''' AND ROWNUM <='||v_chunk_size;
    Purpose of this procedure is to delete rows from the tables stored in DB_CLEANUP_PARAMETER table, but not one by one. Delete rows in chunks.
    What is wrong ? can I get some help ?

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    What's the purpose of doing twice the I/O?
    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
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by PAVB View Post
    What's the purpose of doing twice the I/O?
    I really don't understand what you want to say.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Posted procedure does - for each row to be deleted...
    ... 1st I/O : select looking for rowid
    ... 2nd I/O : delete based on rowid
    e.g. two I/Os to delete a row.
    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.

  5. #5
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by PAVB View Post
    Posted procedure does - for each row to be deleted...
    ... 1st I/O : select looking for rowid
    ... 2nd I/O : delete based on rowid
    e.g. two I/Os to delete a row.


    Now I understand... What will be a solution for if you don't mind.

    Thanks

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would do one statement per table - executing a dynamic sql statement like...
    Code:
    'DELETE ' || v_table_name || ' WHERE ' || v_column_name || ' < (SYSDATE - ' || v_retention_days || ')'
    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.

  7. #7
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by PAVB View Post
    I would do one statement per table - executing a dynamic sql statement like...
    Code:
    'DELETE ' || v_table_name || ' WHERE ' || v_column_name || ' < (SYSDATE - ' || v_retention_days || ')'

    The idea was to delete rows in chunks (let's say 10000 records once and commit)... not all records once, since there might be table with millions records.

    I thought about one statement per table but it would block the database.

  8. #8
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Innitially the procedure was like:

    Code:
    CREATE OR REPLACE PROCEDURE DB_CLEANUP_PROCEDURE IS
      i INTEGER;
      TYPE j IS TABLE OF ROWID;
      row_id j;
      v_table_name VARCHAR2(30);
      v_column_name VARCHAR2(30);
      v_retention_days NUMBER(9);
      k NUMBER := 0;
    BEGIN
     FOR rec IN (SELECT dcp.table_name, dcp.date_field_name, dcp.retention_days
                    FROM db_cleanup_parameters dcp) LOOP
        v_table_name     := rec.table_name;
        v_column_name    := rec.date_field_name;
        v_retention_days := rec.retention_days;
      
        EXECUTE IMMEDIATE 'SELECT ROWID FROM ' || v_table_name || ' WHERE ' ||
                          v_column_name || ' < (SYSDATE - ' || v_retention_days || ')' 
                          BULK COLLECT INTO row_id;
                          
        FOR i IN 1 .. row_id.COUNT LOOP
          EXECUTE IMMEDIATE 'DELETE FROM '||v_table_name||' WHERE ROWID = '''|| row_id(i)||''''; 
          k := k + 1;
          IF k = 1000 THEN
            COMMIT;
            k := 0;
          END IF;
        END LOOP;
      END LOOP;
    COMMIT;
    END DB_CLEANUP_PROCEDURE;
    But seems that this one makes a delete statement for each row which is not good when you have millions rows to delete

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Two questions...


    1- Are tables partitioned by v_column_name ?

    If yes... truncating or switching partitions are good options to take into consideration.


    2- What's the percentage of rows being deleted?

    A high percentage purge works faster doing a reverse purge, instead of deleting what you don't want anymore, bulk insert what you want in a new incarnation of the same table.
    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.

  10. #10
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by PAVB View Post
    Two questions...


    1- Are tables partitioned by v_column_name ?

    If yes... truncating or switching partitions are good options to take into consideration.


    2- What's the percentage of rows being deleted?

    A high percentage purge works faster doing a reverse purge, instead of deleting what you don't want anymore, bulk insert what you want in a new incarnation of the same table.
    1. No

    In DB_CLEANUP_PARAMETERS I have the following entries:

    TABLE_NAME DATE_FIELD_NAME RETENTION_DAYS
    ------------------------------------------------------------
    PH_EVENT TO_DATE 100
    PH_ACCOUNT END_DATE 150
    PH_CONTACT DATE_TO 50

    PH_EVENT, PH_ACCOUNT, PH_CONTACT are tables from database which have around 8 million records. From these table I have to delete all the records where the corresponding column is greater than (SYSDATE + RETENTION_DAYS).

    2. Between 70 and 90%

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