1 2 3 Previous Next 32 Replies Latest reply: Aug 27, 2014 11:39 PM by Harmandeep Singh RSS

    how to improve delete performance?

    sam995972

      Hi All,

       

      Oracle 9i

      Solaris

       

      one of my table is having 1 billion records, i would like to delete approximately  90% of the record count. can any one suggest how to improve my delete operation performance?

       

      thanks,

      Sam.

        • 1. Re: how to improve delete performance?
          Bhanu.Oradba-Oracle

          HI,

           

          Delete also a dml statement, to improve its performance use parallel option.

          Also if db is not running force logging then enable nologging using alter table statement.

           

          Alter session enable parallel dml;

          alter table .... Noforce logging

           

          Thanks,

          Bhanu

          • 2. Re: how to improve delete performance?
            sam995972

            thanks Bhanu quick reply....

             

            does temp,undo,SGA,PGA & redo buffer etc...changes required? or no need?

            • 3. Re: how to improve delete performance?
              sol.beach

              1) move the records to be retained into new_table

              2) truncate & drop original table

              3) rename new_table to old_table

              • 4. Re: how to improve delete performance?
                Bhanu.Oradba-Oracle

                if nologging enabled then the statment generates minimal redo. delete operation with where clause requires sorting so check temp tablespace size.

                 

                if you are using parallel it requires more memory, configure large_pool_size to accommodate parallel operations so in memory sort finishes quickly.

                 

                I think no other changes required.

                 

                 

                thanks,

                Bhanu

                • 5. Re: how to improve delete performance?
                  Girish Sharma

                  sol.beach wrote:

                   

                  1) move the records to be retained into new_table

                  2) truncate & drop original table

                  3) rename new_table to old_table

                  Your approach have below flaws :

                  1. How about table privileges, dependent objects, indexes, etc. ?

                  2. What if deleted rows requires at any stage ?  Before step 2, I think there should be export and/or backup of deleted rows.

                  3. Why to first truncate and then drop original table, what if after coping retained rows in new_table, I says drop table original_table purge; ?

                   

                  Regards

                  Girish Sharma

                  • 6. Re: how to improve delete performance?
                    sam995972

                    thanks Girish for reply.

                     

                    actually we have 50+ tables,  800GB db size,  tables are having 50 to 100 lacs recod count, so we are planning to archive first then delete.

                    does temp,undo,SGA,PGA & redo buffer etc...changes required? or no need? please share your inputs/thought/suggestions....


                    thanks,

                    Sam.

                    • 7. Re: how to improve delete performance?
                      Girish Sharma

                      >does temp,undo,SGA,PGA & redo buffer etc...changes required?

                      No, not at all.  Just use Oracle bulk collection for bulk operations like insert, update and delete.  Example :

                      Best way to delete millions rows from hundred millions table | Oracle Logbook


                      And good article :

                      PLSQL 101

                       

                      Regards

                      Girish Sharma

                      • 8. Re: how to improve delete performance?
                        Antonio Navarro

                        Maybe the more faster way is create a new table, with the 10%, using CTAS, drop over the old table need more undo, redo(archive), index maintance,...



                        Good luck.

                        HTH - Antonio NAVARRO


                        • 9. Re: how to improve delete performance?
                          WadhahDaouehi

                          Hi,

                          There are many possible situations to do this. I think the first step is to create a copy/backup of your table.

                          $ exp username/password@database tables big_table file=big_table.dmp log=big_table.log

                           

                          Then, copy the 10% data/rows into an other table:

                          SQL> insert into smal_table select * from big_table where conditions(10%)

                           

                          The next step, truncate the big table, and the last step is to insert 10% rows into the truncated table.

                          SQL> insert into big_table select * from small_table;

                           

                          Best ragards

                          • 10. Re: how to improve delete performance?
                            rp0428

                            Just use Oracle bulk collection for bulk operations like insert, update and delete.  Example :

                            Best way to delete millions rows from hundred millions table | Oracle Logbook

                            NO! That is a TERRIBLE way to delete data for at least THREE reasons:

                            1. It uses PL/SQL

                            2. It uses collections

                            3. It uses a COMMIT after just a few rows

                             

                            That method is bad enough for deleting a small portion of a table.

                             

                            Perhaps you missed OPs requirement?

                            i would like to delete approximately  90% of the record count

                             

                            • 11. Re: how to improve delete performance?
                              rp0428
                              actually we have 50+ tables,  800GB db size,  tables are having 50 to 100 lacs recod count, so we are planning to archive first then delete.

                              does temp,undo,SGA,PGA & redo buffer etc...changes required? or no need? please share your inputs/thought/suggestions....

                              Please make up your mind what your question is.

                               

                              Your original post said you have ONE table and want to delete 90% of the rows in that one table.

                               

                              Now you say you have 50+ tables.

                               

                              Which is it?

                               

                              Start over and CLEARLY STATE what the problem is. Provide enough detail so people understand the scope of the problem.

                               

                              Deleting 90% of the rows from one table is VERY different than deleting 90% of the rows from 50+ tables.

                              • 12. Re: how to improve delete performance?
                                rp0428

                                The next step, truncate the big table, and the last step is to insert 10% rows into the truncated table.

                                Or, if you have the partitioning option you can just EXCHANGE that partitioned work table with the 'big table' and avoid both the initial backup AND that last insert:

                                 

                                1. create a ONE partition work table

                                2. INSERT 10% of the rows into the work table

                                3. EXCHANGE the work table segment with the 'big table' segment

                                • 13. Re: how to improve delete performance?
                                  Girish Sharma

                                  Hi Rp,

                                   

                                  Thanks for your post. I think it is not that much terrible as you are thinking, because that link is using bulk binding (use of FORALL statement for DML) to avoid context switching.  The only line which need to comment is commit in the loop, which is acceptable to me, because when I read below para from docs, I think it is not terrible though :

                                   

                                  A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.

                                   

                                  To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement.

                                  PL/SQL Collections and Records

                                   

                                  Obviously, if OP can use delete from table_name where <90% condition>; it is fine, not only fine; it the best, because delete is the DML, in the RDBMS DMLs should have no issue to run, if they have then environment and/or system need to be correct as per need, because if in RDBMS; DMLs are having issue (need to improve delete performance), then something is really need to tune.

                                   

                                  In the PL/SQL block it seems that context switching is happening but when I saw bulk binding, I thought it may be useful to OP for deleting 90% rows, rather than creating a new table, rename it because it may invalidate dependent objects, privileges issue and index re-create.  If all these sums up, then I think bulk collection is good option.  Kindly post your views.

                                   

                                  Regards

                                  Girish Sharma

                                  • 14. Re: how to improve delete performance?
                                    Harmandeep Singh

                                    Hi Girish,

                                     

                                    I agree with rp0428  point, that it is not good as context switch is high. As per Tom Kyte, general rule, if something can be done in SQL do it there rather than in upper layer (PL/SQL).

                                    https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:60122715103602

                                     

                                    Context switches  for the bulk size of 1000 will be 50,000,000/1,000 = 500K , which is quite huge. 

                                     

                                    In below link , Elkin, gives the time comparative time difference analysis

                                    [Oracle-l] Measuring PLSQL-SQL context switches - Grokbase

                                     

                                    Exchange partition concept seems best to me, just switching the pointers and you are done

                                     

                                    Regards,

                                    Harman

                                    1 2 3 Previous Next