1 2 Previous Next 20 Replies Latest reply on Dec 12, 2012 11:45 PM by onedbguru Go to original post
      • 15. Re: Procedure to delete mutiple table records with 1000 rows at a time
        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 you mind explaining that?

        Why would you need to just arbitrarly keep a minimum of 28 million rows in a table? Why don't you tell us exactly what you are trying to accomplish?

        The only way you can maintain the same number of rows in a table is to either not add or delete any or to add and delete exactly the same number. So something now sounds a little unusual about some of your statements.

        What does this data represent? Why do you need to delete data every day just because you add some data every day?

        Why is it ok to delete just 1000 rows at a time from the child tables? That means that ANY query of that child data MUST BE incorrect since it will not include all of the data.
        • 16. Re: Procedure to delete mutiple table records with 1000 rows at a time
          Sorry to not made it clear already.

          Those are backend tables of ATG based retail's web application. All the online customer profile informations(3 million rows already) and information about customer who just visiting the website(approx 500k new rows daily) being stored in the master and child tables. Though refrential integrity constraints are enabled between those tables, "cascade delete option" are disabled from database level based on the specific requirement from front end application. (as if any data's get deleted from database end using cascade delete option then still application would remain uninformed about data's deleted and it would cause messup in application). So all should be manual delete if it happens from db end.

          Now the requirement is to delete 45 days old unregistered user profiles (who just visting the website) on daily basis which is 500k records daily, approx.

          Hence table would always maintains, 3million (registered user profiles) + 500k*45=23million unregistered profiles (45 days count of unregistered profiles) = 26million (total) will be available in table always. 500k should be purged on daily basis for the unregistered profiles which is 45 days old.

          This condition will be first matched with master table as only this table has date column and take out the another column value (ID) from matched profiles.
          I = select ID from master_table where (unregistered profile condition) and registered_date <= (sysdate) -45;

          Further compare it with child tables ID column and delete the matched columns of child tables
          Delete from child_table1 where child_table.ID = I.ID
          Delete from child_table4 where child_table.ID = I.ID

          Atlast delete the matched columns from master table.
          Delet from master table where master_table.id= I.ID

          But it should not delete registered profiles of 45 days old,
          should not use huge undo
          should not generate more redo and
          should complete in less time as well.
          Morever, I couldn't go for partition option as each table size is not even crossed 2GB. I have been breaking my head to prepare the procedure for this work, for couple of days. Any help on this would be really appreciated.

          Hope you got the requirement I am having. Many Thanks for your help,

          Edited by: 975654 on 12-Dec-2012 10:44
          • 17. Re: Procedure to delete mutiple table records with 1000 rows at a time
              cursor t_c
                select id
                from master_table
                where 1 = 1; -- your condition
              type tp_ids is table of master_table.id%type;
              t_ids tp_ids;
              open t_c;
                fetch t_c 
                bulk collect
                into t_ids limit 22; -- change according to resources
            dbms_output.put_line( t_ids.count );
                exit when t_ids.count = 0;
                forall i in t_ids.first .. t_ids.last
                  delete from child_table where fk_id = t_ids( i );  
                forall i in t_ids.first .. t_ids.last
                  delete from master_table where id = t_ids( i );
                exit when t_c%notfound;
              end loop;
              close t_c;
            Edited by: ascheffer on Dec 12, 2012 9:26 PM
            • 18. Re: Procedure to delete mutiple table records with 1000 rows at a time
              Just briefly reviewed the long panel discussion by Tom (Regular Delete vs. Bulk Delete).


              One concern here is how many redo entries are generated from FORALL. Although it switches from PL/SQL engine to SQL engine only once for all the deletes, its impact on redo log growth still needs on check. Also, how many child records are deleted for each parent ID (inefficient for low cardinality)? For such reasons, regular delete would be more flexible to keep both redo and undo usage under control. But wait and see how test shows.

              Below is by regular delete. Create a view first to ease the coding
              create or replace view delete_id_list
              select ID from master_table 
              where (unregistered profile condition) and registered_date <= (sysdate) -45
              --run pl/sql block now for delete
              i NUMBER :=1000 ;
              --delete child1
              delete child_table1 
              where ID in (select id from delete_id_list)
              and rownum<i ;
              exit when sql%rowcount=0;
              commit ;
              end loop ;
              end ;
              --delete child2
              --delete child3
              --delete child4
              delete delete_id_list
              where rownum<i ;
              exit when sql%rowcount=0;
              commit ;
              end loop ;
              when others then 
                   rollback ;
              end ;
              • 19. Re: Procedure to delete mutiple table records with 1000 rows at a time
                Morever, I couldn't go for partition option as each table size is not even crossed 2GB
                Now there's another cryptic statement that appears to be based on the 'if table is greater than 2GB consider partitioning' myth. ;)

                There is no such size condition or requirement for partitioning. You should partition based on the benefits you will derive. Those benefits are usually performance, partition maintenance or both.

                For you use case partitioning is definitely indicated. You never did provide your 4 digit Oracle version (or maybe I missed it) but the 11g versions support interval partitioning where you partition by day and let Oracle create the partitions automatically.

                When you want to get rid of old data you simply DROP the partition that it is in and that is the end of it. No muss, no fuss, no messing with redo/undo.

                You should test using an interval partitioned table.

                Here is simple code
                  ID       NUMBER(10),
                  MY_DATE  DATE
                PARTITION BY RANGE (MY_DATE)
                  PARTITION P_EXISTING_20120401 VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
                That one partition definition ensures that all of the 'old' data has somewhere to go; use any date you want. For any new data (MY_DATE later than that partition boundary) Oracle will automatically create a partition for that one day.

                So every day you will get one new partition created automatically and eveyr day you can drop the oldest partition if you want. Or drop a week at a time.
                Poof - it's gone. Since the drop is DDL you have no undo/redo. And partitions do NOT go into the recyclebin. Just drop the child table partitions first and then the parent.
                • 20. Re: Procedure to delete mutiple table records with 1000 rows at a time
                  You should always include the version... I did not see it, but could have overlooked it.

                  If you are using 11gR2, look at DBMS_PARALLEL_EXECUTE. It handles very large DML very easy.

                  I have used this in situations where I must update or delete very large volumes of data with not-so-easy selectivity. Search the docs for the procedure. TEST, TEST and TEST some more.

                  This guarantees, you will never see an ORA-01555

                  Edited by: onedbguru on Dec 12, 2012 3:45 PM
                  1 2 Previous Next