This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Dec 12, 2012 3:45 PM by onedbguru RSS

Procedure to delete mutiple table records with 1000 rows at a time

978657 Newbie
Currently Being Moderated
Hello Champs,

I am having requirement to create procedure to achive the deletes from 5 big tables. 1 master table and 4 child tables. Each table has 28 millions records. Now I have to schedule a procedure to run it on daily basis to delete approximately 500k records from each tables. But the condition is: Seperate procedures for each tables and delete only 1000 rows at a time. The plan is below. Since I am new for writing complicate procedure, I don't have much idea how to code the procedures, can some one help me to design the procedure queries. Many Thanks

1. Fetch 1000 rows from master table with where clause condition.
2. Match them with each child tables and delete the records.
3. Atlast delete those 1000 rows from master table.
4. over all commit for all 5 X 1000 records.
5. Repeat the steps 1 to 4 till no rows to fetch from master table.


Below are detailed procedure plan:----


----- Main procedure to fetch 1000 rows at a time and providing them as input to 5 different procedures to delete records, repeating same steps till no rows to fetch i.e. (i=0)-----
Create procedure fetch_record_from_master_table:
loop
i = Select column1 from mastertable where <somecondition> and rowcount <= 1000;
call procedure 1 and i rows as input;
call procedure 2 and i rows as input;
call procedure 3 and i rows as input;
call procedure 4 and i rows as input;
call procedure 5 and i rows as input;
commit;
exit when i=0
end loop;


----- Sepereate procedure's to delete records from each child tables------
Create procedure 1 with input:
delete from child_table1 where column1=input rows of master table;

Create procedure 2 with input:
delete from child_table2 where column1=input rows of master table;

Create procedure 3 with input:
delete from child_table3 where column1=input rows of master table;

Create procedure 4 with input:
delete from child_table4 where column1=input rows of master table;


--- procedure to delete records from master table atlast----
Create procedure 5 with input:
delete from Master_table where column1=input rows of master table;
  • 1. Re: Procedure to delete mutiple table records with 1000 rows at a time
    sb92075 Guru
    Currently Being Moderated
    975654 wrote:
    Hello Champs,

    I am having requirement to create procedure to achive the deletes from 5 big tables. 1 master table and 4 child tables. Each table has 28 millions records. Now I have to schedule a procedure to run it on daily basis to delete approximately 500k records from each tables. But the condition is: Seperate procedures for each tables and delete only 1000 rows at a time.
    Who is the idiot that provided the 1000 row limit?
    It is GREAT way to make process slower & to possibly throw ORA-01555; Snapshot too old error.
  • 2. Re: Procedure to delete mutiple table records with 1000 rows at a time
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
    >
    But the condition is: Seperate procedures for each tables and delete only 1000 rows at a time.
    >
    Well you should find out whose idea that was and see if you can find some other kind of work for them. If they make recommendations (or worse issue orders) to do the deletes this way then they clearly aren't qualified in the DBA or developer area.

    That is about the worst possible way you could delete data.

    Just use SQL and delete the data. Your method will use more resources and can cause problems for your DB users by doing this piecemeal operation.

    You can count on several other responders expressing similar sentiments once they see your thread.
  • 3. Re: Procedure to delete mutiple table records with 1000 rows at a time
    978657 Newbie
    Currently Being Moderated
    Thanks for your comments. Yeh, that was the requirment from application team, where I confused too. Can you please suggest the best way to write the procedure instead of following approach posted earlier (or) by using the approach by increasing the fetching and deleting limit?
  • 4. Re: Procedure to delete mutiple table records with 1000 rows at a time
    stratmo Newbie
    Currently Being Moderated
    Hi,

    you may take a look at [url http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2345591157689] AskTom - Deleting many rows from a big table, some strategies are described there. It's quite a long but very interesting entry.
    So give it a try please.

    Bye

    stratmo
  • 5. Re: Procedure to delete mutiple table records with 1000 rows at a time
    sb92075 Guru
    Currently Being Moderated
    Yeh, that was the requirment from application team
    They need to be replaced by folks who actually know Oracle.
  • 6. Re: Procedure to delete mutiple table records with 1000 rows at a time
    ascheffer Expert
    Currently Being Moderated
    what about
    begin
      truncate table child_table1;
      truncate table child_table2;
      truncate table child_table3;
      truncate table child_table4;
      for r_fk in ( select table_name, constraint_name
                    from user_constraints
                    where table_name = 'MASTER_TABLE'
                    and   constraint_type = 'R'
                  )
      loop
        execute immediate 'ALTER TABLE ' || r_fk.table_name || ' MODIFY CONSTRAINT ' || r_fk.constraint_name || ' DISABLE';
      end loop; 
      truncate table master_table;
      for r_fk in ( select table_name, constraint_name
                    from user_constraints
                    where table_name = 'MASTER_TABLE'
                    and   constraint_type = 'R'
                  )
      loop
        execute immediate 'ALTER TABLE ' || r_fk.table_name || ' MODIFY CONSTRAINT ' || r_fk.constraint_name || ' ENABLE';
      end loop; 
    end;
    /
  • 7. Re: Procedure to delete mutiple table records with 1000 rows at a time
    BluShadow Guru Moderator
    Currently Being Moderated
    ascheffer wrote:
    what about
    begin
    truncate table child_table1;
    truncate table child_table2;
    truncate table child_table3;
    truncate table child_table4;
    for r_fk in ( select table_name, constraint_name
    from user_constraints
    where table_name = 'MASTER_TABLE'
    and   constraint_type = 'R'
    )
    loop
    execute immediate 'ALTER TABLE ' || r_fk.table_name || ' MODIFY CONSTRAINT ' || r_fk.constraint_name || ' DISABLE';
    end loop; 
    truncate table master_table;
    for r_fk in ( select table_name, constraint_name
    from user_constraints
    where table_name = 'MASTER_TABLE'
    and   constraint_type = 'R'
    )
    loop
    execute immediate 'ALTER TABLE ' || r_fk.table_name || ' MODIFY CONSTRAINT ' || r_fk.constraint_name || ' ENABLE';
    end loop; 
    end;
    /
    Not woken up yet anton? ;)
    Doing truncate statements in PL/SQL.... you know that won't work.
  • 8. Re: Procedure to delete mutiple table records with 1000 rows at a time
    ascheffer Expert
    Currently Being Moderated
    Oops, but this will work, won't it?
    begin
      execute immediate 'truncate table child_table1';
      execute immediate '  truncate table child_table2';
      execute immediate '  truncate table child_table3';
      execute immediate '  truncate table child_table4';
      for r_fk in ( select table_name, constraint_name
                    from user_constraints
                    where table_name = 'MASTER_TABLE'
                    and   constraint_type = 'R'
                  )
      loop
        execute immediate 'ALTER TABLE ' || r_fk.table_name || ' MODIFY CONSTRAINT ' || r_fk.constraint_name || ' DISABLE';
      end loop; 
      execute immediate '  truncate table master_table';
      for r_fk in ( select table_name, constraint_name
                    from user_constraints
                    where table_name = 'MASTER_TABLE'
                    and   constraint_type = 'R'
                  )
      loop
        execute immediate 'ALTER TABLE ' || r_fk.table_name || ' MODIFY CONSTRAINT ' || r_fk.constraint_name || ' ENABLE';
      end loop; 
    end;
    / 
    Or
      truncate table child_table1;
      truncate table child_table2;
      truncate table child_table3;
      truncate table child_table4;
    begin
      for r_fk in ( select table_name, constraint_name
                    from user_constraints
                    where table_name = 'MASTER_TABLE'
                    and   constraint_type = 'R'
                  )
      loop
        execute immediate 'ALTER TABLE ' || r_fk.table_name || ' MODIFY CONSTRAINT ' || r_fk.constraint_name || ' DISABLE';
      end loop; 
    end;
    /
    truncate table master_table;
    begin
      for r_fk in ( select table_name, constraint_name
                    from user_constraints
                    where table_name = 'MASTER_TABLE'
                    and   constraint_type = 'R'
                  )
      loop
        execute immediate 'ALTER TABLE ' || r_fk.table_name || ' MODIFY CONSTRAINT ' || r_fk.constraint_name || ' ENABLE';
      end loop; 
    end;
    / 
  • 9. Re: Procedure to delete mutiple table records with 1000 rows at a time
    BluShadow Guru Moderator
    Currently Being Moderated
    ascheffer wrote:
    Oops, but this will work, won't it?
    much better. ;)
  • 10. Re: Procedure to delete mutiple table records with 1000 rows at a time
    Purvesh K Guru
    Currently Being Moderated
    975654 wrote:
    Hello Champs,

    I am having requirement to create procedure to achive the deletes from 5 big tables. 1 master table and 4 child tables. Each table has 28 millions records. Now I have to schedule a procedure to run it on daily basis to delete approximately 500k records from each tables. But the condition is: Seperate procedures for each tables and delete only 1000 rows at a time. The plan is below. Since I am new for writing complicate procedure, I don't have much idea how to code the procedures, can some one help me to design the procedure queries. Many Thanks

    1. Fetch 1000 rows from master table with where clause condition.
    2. Match them with each child tables and delete the records.
    3. Atlast delete those 1000 rows from master table.
    4. over all commit for all 5 X 1000 records.
    5. Repeat the steps 1 to 4 till no rows to fetch from master table.
    Since you mention Master and Child Tables, you should be having a Foreign Key relationships established. You will have to ensure that the Foreign Key has ON DELETE CASCADE specified.

    And if you have, then you can save a lot of troubles by just deleting the data from Master Table and the Child records shall be wiped off without wasting efforts to Fetch/an Explicit delete statement.

    Since, many experts like Anton, Blu, SB, RP are already participating in this thread, they must have thought about this solution and discarded it, but the only drawback I can see is having a sufficient UNDO and REDO space considering you will be deleting approx 2.5 Million records each day. Or maybe I am missing something.

    And I echo their views to avoid Step wise delete.
  • 11. Re: Procedure to delete mutiple table records with 1000 rows at a time
    BluShadow Guru Moderator
    Currently Being Moderated
    Purvesh K wrote:
    Since you mention Master and Child Tables, you should be having a Foreign Key relationships established. You will have to ensure that the Foreign Key has ON DELETE CASCADE specified.

    And if you have, then you can save a lot of troubles by just deleting the data from Master Table and the Child records shall be wiped off without wasting efforts to Fetch/an Explicit delete statement.

    Since, many experts like Anton, Blu, SB, RP are already participating in this thread, they must have thought about this solution and discarded it, but the only drawback I can see is having a sufficient UNDO and REDO space considering you will be deleting approx 2.5 Million records each day. Or maybe I am missing something.

    And I echo their views to avoid Step wise delete.
    Well, I hadn't really got into this particular issue myself, and you're right that a cascading delete is an option.
    Personally though, if I had a table with 28 million rows in it (and child table data), and if appropriate, I'd be using partitions, disabling FK contraints and just truncating or dropping partitions as appropriate.
  • 12. Re: Procedure to delete mutiple table records with 1000 rows at a time
    978657 Newbie
    Currently Being Moderated
    Thanks for the suggestions. However, cascade delete option was not enabled in database level due to the dependency from front end application (ATG) as db should not delete relational data's but ATG should own it. Hence whatever get delete from database end that should happen by manual query.

    Moreover, I couldn't use truncate as I need 28 million data's to be in table always and I am just deleting records based on where condition.


    would this below idea works?

    First procedure to delete entire 0.5 million rows from all child tables with 10,000 rows on each table at a time and commit, then call seperate procedure to delete master table in same 10,000 rows freequency?

    I will deleting 500k records daily.

    Many Thanks,
  • 13. Re: Procedure to delete mutiple table records with 1000 rows at a time
    BluShadow Guru Moderator
    Currently Being Moderated
    975654 wrote:
    Thanks for the suggestions. However, cascade delete option was not enabled in database level due to the dependency from front end application (ATG) as db should not delete relational data's but ATG should own it. Hence whatever get delete from database end that should happen by manual query.

    Moreover, I couldn't use truncate as I need 28 million data's to be in table always and I am just deleting records based on where condition.


    would this below idea works?

    First procedure to delete entire 0.5 million rows from all child tables with 10,000 rows on each table at a time and commit, then call seperate procedure to delete master table in same 10,000 rows freequency?

    I will deleting 500k records daily.
    We don't know enough about your tables and data to say whether it would work or not. There could certain be issues with deleting data in batches especially with intermediate commits, and generally it's considered a bad way to do it.

    Deleting 500,000 rows daily... not that much.... why not just do a single delete and be done with it? Have you actually tried that and found any issue?
  • 14. Re: Procedure to delete mutiple table records with 1000 rows at a time
    978657 Newbie
    Currently Being Moderated
    Yeh i tried , Actually I have started constructing procedure with single delete for each table and commit at end. But it taking hell lots of UNDO and generates more REDO's as well, hence it's disapproved by DBA.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points