1 2 Previous Next 19 Replies Latest reply: Apr 16, 2014 12:26 AM by onkar.nath RSS

    How to fast track delete?

      Hi all,

       

      11.2.0.3

       

      We have a big transaction temp table with 3M rows. If I delete it one time it gets rollback error. So I am deleting it by 50,000.

      As in delete EMP where rownum<50000; l But it is still taking long???? Even If  I delete1000 rows, it seems doing full table scan?

      delete EMP where rownum<1001; is doin full table scan? How can I fast track this delete pleaseeeee..

       

      Even if I run > analyze table EMP compute statistics;  is it also hanging? what is happening to our table? is it locked out or our UNDOTS  is full?

       

      Please helpppp.

       

      Thanks,

      pK

        • 2. Re: How to fast track delete?
          rp0428
          11.2.0.3

           

          We have a big transaction temp table with 3M rows. If I delete it one time it gets rollback error. So I am deleting it by 50,000.

          As in delete EMP where rownum<50000; l But it is still taking long???? Even If  I delete1000 rows, it seems doing full table scan?

          delete EMP where rownum<1001; is doin full table scan? How can I fast track this delete pleaseeeee..

           

          Even if I run > analyze table EMP compute statistics;  is it also hanging? what is happening to our table? is it locked out or our UNDOTS  is full?

          Gee - I don't see any 'rollback error' in what you posted.

           

          Just TRUNCATE the table and be done with it.

           

          If it is a 'temp' table then create it as a TEMPORARY table and Oracle will take care of cleaning it up on a commit or at the end of the session.

           

          If you are deleting a large fraction of the rows then use CTAS to copy off the ones you want to keep and then use EXCHANGE PARTITION to put them back.

          • 3. Re: How to fast track delete?
            Martin Preiss

            some comments without a particular order:

            • analyze table ... compute statistics was the correct way to create statistics in release ... - I don't even recall the number; maybe 7. In 11.2 you should use dbms_stats procedures for the task.
            • deleting in chunks is not a recommended strategy - and rp0428 already mentioned some alternatives.
            • a delete with a rownum predicate should do a full table scan but stop after the specified number of rows (and the number of blocks that contain enough rows to be deleted; not talking about lock problems, referential integrity etc.)

            Just a quick example:

            -- 11.2.0.1

            create table t_del

            as

            select rownum id

                 , lpad('*', 100, '*') padding

              from dual

            connect by level <= 1000000;

             

            delete from t_del where rownum < 1000;

             

            ---------------------------------------------------------------------

            | Id  | Operation           | Name  | Rows  | Cost (%CPU)| Time     |

            ---------------------------------------------------------------------

            |   0 | DELETE STATEMENT    |       |   999 |   609   (0)| 00:00:04 |

            |   1 |  DELETE             | T_DEL |       |            |          |

            |*  2 |   COUNT STOPKEY     |       |       |            |          |

            |   3 |    TABLE ACCESS FULL| T_DEL |   866K|   609   (0)| 00:00:04 |

            ---------------------------------------------------------------------

             

            Predicate Information (identified by operation id):

            ---------------------------------------------------

               2 - filter(ROWNUM<1000)

            The COUNT STOPKEY makes sure that the Full Table Scan is not a problem in this example.

            • 4. Re: How to fast track delete?

              Thanks all,

               

              The concerned table is not partitioned. I can not deleted one time the old dated (2M+ rows) because this table is very active (approx 50 rows inserts per seconds) queue is building up once you delete larger rows, meaning transaction is lost. meaning client are being rejected or timeout, hence lossing revenue.

               

              Can you suggest how I can delete by 10,000 or  1,000 rows fast?

               

              Thanks

              • 5. Re: How to fast track delete?
                JustinCave

                If other sessions are simultaneously inserting rows, then I assume that you aren't really running a DELETE whose only predicate is the ROWNUM.  Presumably, you have some sort of predicate based on, for example, the date the row was inserted, so that you're not trying to delete a row immediately after the row gets inserted.  Is that date column indexed?

                 

                This is why it's really, really helpful to post correct information about the problem you're trying to solve.  Posting the actual statement, the actual query plan, etc. would make it much easier to help you. 

                 

                Rather than deleting rows in batches, why don't you fix whatever problem is causing whatever error you're getting with a single statement?

                 

                Justin

                • 6. Re: How to fast track delete?
                  rp0428
                  The concerned table is not partitioned.

                   

                  What does that have to do with anything? If the data you typically need to delete is old data based on some date then you could use a partitioned table on that date to make it easier in the future to roll off/delete the older data.

                  I can not deleted one time the old dated (2M+ rows) because this table is very active (approx 50 rows inserts per seconds) queue is building up once you delete larger rows, meaning transaction is lost. meaning client are being rejected or timeout, hence lossing revenue. 

                  So you want to delete 2 million out of 3 million rows - or two thirds of the table. You should probably do that offline by using CTAS and/or the partition exchange method I suggested.

                  Can you suggest how I can delete by 10,000 or  1,000 rows fast?

                  No - there is no 'fast' way to delete 2/3s of the table if you use a method that generates undo and redo for all of the data.

                   

                  You need to fix the underlying problem with the architecture or you will just keep having the 'delete old data' problem.

                  • 7. Re: How to fast track delete?
                    Hemant K Chitale

                    This suggests a pre-existing table, with a pre-existing application and code.  Code that was and is inserting data.  But no code to delete data until you suddenly started to run these deletes --- unplanned.

                    If the delete method wasn't built into the application, you may need to

                    a. stop the application

                    b. do a cleanup (aka 'delete') against the table

                    c. incorporate code that does periodic deletes as defined by requirements

                    d. restart the application

                     

                    if you can't do these steps,  you ARE stuck with the issue.

                    We can't speed up the delete for you.

                    Note that the table won't shrink until and unless you do an ALTER TABLE ... SHRINK <COMPACT>   --- which means that all full table scans will continue to read as many blocks as before irrespective of how many rows you've deleted.

                    So, you do need  to cleanup the table.

                     

                     

                    Hemant K Chitale


                    • 8. Re: How to fast track delete?

                      Thanks all,

                       

                      Can you help me create a pl/sql that will delete rows and commit 1 by 1 based on rowid? I think this will not build up queue since It will be a per row basis delete and commit?

                      • 9. Re: How to fast track delete?
                        sb92075

                        petra-K wrote:

                         

                        Thanks all,

                         

                        Can you help me create a pl/sql that will delete rows and commit 1 by 1 based on rowid? I think this will not build up queue since It will be a per row basis delete and commit?

                        You should resign since you are incapable & unqualified for anything but having others do your job for you.

                        • 10. Re: How to fast track delete?
                          Hemant K Chitale

                          A row-by-row delete would be the wrong thing to do.  A multi-row delete is what you need.

                           

                          We don't know the rows that qualify for the delete --- what is the WHERE clause.  This question has already been asked.

                           

                           

                          Hemant K Chitale


                          • 11. Re: How to fast track delete?

                            Thanks hemant,

                            I told you I can not do bulk delete as it result to transaction queueing

                            The delete command is just as simple as > DELETE FROM EMP WHERE SETTLEMENT_DATE='13-APR-14';

                             

                            Thanks

                            • 12. Re: How to fast track delete?
                              Hemant K Chitale

                              You have NO idea what a multi-row delete is, even after all these months.

                               

                              I pity your manager.

                               

                              Hemant K Chitale


                              • 13. Re: How to fast track delete?

                                Is it not that bulk delete is the same as multi row delete

                                • 14. Re: How to fast track delete?
                                  Hemant K Chitale

                                  A "bulk" delete is the same as a "multi-row" delete.  It does not "result to transaction queueing"  unless you have something peculiar (not normal) about the way rows are inserted and deleted.

                                   

                                   

                                  Hemant K Chitale


                                  1 2 Previous Next