14 Replies Latest reply: Apr 3, 2008 6:37 AM by 599921 RSS

    commit after deleting records

    599921
      Hi All,

      I wrote one delete command .This command deletes 20 millions of records.But I want to give "commit" after deleting every 100000 records. How to give "commit" for this requirement.
      please guide me.

      Thank you.
        • 1. Re: commit after deleting records
          575729
          you are following a bad practice most sql database developer do. never commit to often in oracle.

          to delete 20 million records out of what?

          Tell me how many rows you have in the table and how many you want to delete
          • 2. Re: commit after deleting records
            Sven W.
            Depends on your delete statement.
            Sometime you can group the delets into logical units.

            Compare and consider the following three approaches.

            1) This deletes all data from all previous months.
            Delete from myTable where insertDate < trunc(sysdate,'mm'))
            2) Delete each month separately and commit in between.
            Delete from myTable where insertDate < trunc(sysdate,'year'));
            commit;
            Delete from myTable 
            where insertDate >= trunc(sysdate,'year')) 
            and insdate < add_months(trunc(sysdate,'year'),1) -- "january"
            and insdate < trunc(sysdate,'mm')) -- do not delete too much!
            commit;
            Delete from myTable 
            where insertDate >= trunc(sysdate,'year')) 
            and insdate < add_months(trunc(sysdate,'year'),2) -- "February"
            and insdate < trunc(sysdate,'mm')) -- do not delete too much!
            commit;
            Delete from myTable 
            where insertDate >= trunc(sysdate,'year')) 
            and insdate < add_months(trunc(sysdate,'year'),3) -- "March"
            and insdate < trunc(sysdate,'mm')) -- do not delete too much!
            commit;
            ...
            Delete from myTable 
            where insertDate >= trunc(sysdate,'year')) 
            and insdate < add_months(trunc(sysdate,'year'),12) -- "December"
            and insdate < trunc(sysdate,'mm')) -- do not delete too much!
            commit;
            3) Delete based on ID and logical groups
            Select min(id), max(id) into v_min, v_max
            from myTable where insertDate < trunc(sysdate,'mm'));
            
            for i in 1..trunc(v_max-v_min)/100000)+1 loop
              delete from myTable 
              where id >= v_min+((i-1)*100000)
              and id < v_min+(i*100000)
              and id <= v_max
              and insertDate < trunc(sysdate,'mm')) -- this line is not really needed, maybe remove it depending on the execution plan.
              ;
              commit;
            end loop;
            • 3. Re: commit after deleting records
              599921
              I have 50millions of records.So I want to delete 20 millions of records using Delete command. But I need to give "commit" after deleting every 100000 records.Please give me idea ASAP.

              Thank you.
              • 4. Re: commit after deleting records
                Sven W.
                Please give me idea ASAP.
                I suddenly regretted that I made any effort to answer this...
                • 5. Re: commit after deleting records
                  388131
                  Please give me idea ASAP.
                  I suddenly regretted that I made any effort to answer
                  this...
                  It seems you were - once again - too kind. ;)
                  • 6. Re: commit after deleting records
                    599921
                    Hi,
                    delete
                    from     emp e
                    where    exists    (select 1
                                        from   dept d
                                        where  d.dept_no      = e.dept_no
                                        and    d.d_type    = 'SIP'
                                        and    d.status      = 'C'
                                        and    d.create_date < (select v.join_date - 4
                                                                  from   vadjoin v))
                    Thank you.
                    • 7. Re: commit after deleting records
                      599921
                      HI I am so sorry to mention like this..
                      PLease give me idea ASAP
                      • 8. Re: commit after deleting records
                        599921
                        i wrote pl/sql blkock like
                         Begin
                         Loop
                         delete from     emp ewhere    exists    (select 1   from   dept d
                                                                              where  d.dept_no   = e.dept_no                    
                                                                               and    d.d_type    = 'SIP'   
                                                                               and    d.status      = 'C'                     
                                                                         and    d.create_date < (select v.join_date -4      from   vadjoin v)) and rownum<=100000
                        delete from emp e
                        where not exists (select 'X' from emp e
                                          where e.dept_no      = d.dept_no);
                          if SQL%rowcount=0 then
                          exit;
                          end loop;
                        commit;
                          end;
                        But I am getting errors as
                        ERROR at line 15:
                        ORA-06550: line 13, column 41:
                        PL/SQL: ORA-00933: SQL command not properly ended
                        ORA-06550: line 4, column 65530:
                        PL/SQL: SQL Statement ignored
                        ORA-06550: line 18, column 7:
                        PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
                        if
                        please correct and guide me.
                        Thank u
                        • 9. Re: commit after deleting records
                          523861
                          read your error message, you have a syntax error:

                          this:
                           if SQL%rowcount=0 then
                            exit;
                            end loop;
                          should be:
                           if SQL%rowcount=0 then
                            exit;
                          end if;
                            end loop;
                          • 10. Re: commit after deleting records
                            599921
                            Am changed .still am getting error
                            ERROR at line 15:
                            ORA-06550: line 13, column 41:
                            PL/SQL: ORA-00933: SQL command not properly ended
                            ORA-06550: line 4, column 65530:
                            PL/SQL: SQL Statement ignored
                            • 11. Re: commit after deleting records
                              523861
                              This is another syntax error.

                              the problem is in this line:
                              delete from     emp ewhere    exists    (select 1   from   dept d
                              see if you can pick it yourself this time
                              • 12. Re: commit after deleting records
                                RadhakrishnaSarma
                                Begin
                                Loop
                                   delete from     emp e
                                   where    exists    (select 1   
                                                       from   dept d
                                                       where  d.dept_no   = e.dept_no                    
                                                       and    d.d_type    = 'SIP'   
                                                       and    d.status      = 'C'                     
                                                       and    d.create_date < (select v.join_date -4      
                                                                               from   vadjoin v)
                                                       ) 
                                   and rownum<=100000;
                                   
                                   
                                   if SQL%ROWCOUNT = 0 then
                                      exit;
                                   end if;
                                   
                                   commit;
                                end loop;
                                end;
                                Cheers
                                Sarma.
                                • 13. Re: commit after deleting records
                                  Sven W.
                                  I think one could even get away with:
                                    ...
                                    commit;
                                    if SQL%ROWCOUNT < 100000 then
                                        exit;
                                    end if;
                                    ...
                                  Hm... not sure if sql%rowcount is still correctly available after commit.
                                  One should check that. If not then include a commit also inside the if clause or at the very end of the block.

                                  Message was edited by:
                                  Sven W.
                                  • 14. Re: commit after deleting records
                                    599921
                                    Hi All,

                                    I am very much thankful to all of you...I need ur guidence in the same way...

                                    Thank u.