1 2 Previous Next 21 Replies Latest reply: Nov 25, 2012 10:54 AM by rp0428 Go to original post RSS
      • 15. Re: Delete millions of records
        JohnWatson
        NB wrote:
        and this will not affect performance during the drop partition ?
        of course it will, you can't delete millions of index entries instantaneously.
        >
        Since my indexes will be used by other select and update statements, indexes will not be affected during the drop?
        The description in the VLDB and Partitioning Guide is clear on this.
        • 16. Re: Delete millions of records
          Neo-b
          Ok then the best way is to delete and then drop, yes?
          • 17. Re: Delete millions of records
            JohnWatson
            NB wrote:
            Ok then the best way is to delete and then drop, yes?
            That would be a definition of "best" with which I am not familiar :)
            Really, the index maintenance clauses were added in release 10.x and enhanced in 11.x precisely because what you are doing is so awkward and inefficient.
            • 18. Re: Delete millions of records
              Neo-b
              Sorry please can you clarify ?

              Technically I am using Oracle 11.2.0.3.
              My table is partitioned by date. the indexes are not partitioned, on each day i need to delete data of sysdate - 30, I need to delete about 5m records.
              What I am currently doing is deleting the data and then dropping the corresponding partition.

              So you suggested to use update indexes to directly drop the partition without deleting the data
              My question does that impact the performance of other sessions ?? does that impact the application that is doing select, update on this table?

              Which is better to delete then drop ? or drop and adding the clause update indexes?

              Regards,
              • 19. Re: Delete millions of records
                Girish Sharma
                Ok then the best way is to delete and then drop, yes?
                But what is difference between delete and drop? What you are deleting and dropping?
                I think you should try to give perfect answer to the questions of Sir Jonathan Lewis; which he has asked from you above. If you are not able to answer them, ask again "how do I answer your questions; so that you can help me".

                Regards
                Girish Sharma
                • 20. Re: Delete millions of records
                  JohnWatson
                  NB wrote:
                  Sorry please can you clarify ?

                  Technically I am using Oracle 11.2.0.3.
                  My table is partitioned by date. the indexes are not partitioned, on each day i need to delete data of sysdate - 30, I need to delete about 5m records.
                  What I am currently doing is deleting the data and then dropping the corresponding partition.

                  So you suggested to use update indexes to directly drop the partition without deleting the data
                  yes
                  My question does that impact the performance of other sessions ??
                  why would it?
                  does that impact the application that is doing select, update on this table?
                  of course not
                  Which is better to delete then drop ? or drop and adding the clause update indexes?
                  I've already told you! Why donlt you just try it? And do a bit of reading?
                  Regards,
                  Trust me: I'm a DBA.
                  • 21. Re: Delete millions of records
                    rp0428
                    >
                    What I am currently doing is deleting the data and then dropping the corresponding partition.

                    Which is better to delete then drop ? or drop and adding the clause update indexes?

                    So you suggested to use update indexes to directly drop the partition without deleting the data
                    >
                    Yes - that is generally the recommendation. See the doc quotes below - there are some extreme cases where that might not apply.
                    >
                    My question does that impact the performance of other sessions ?? does that impact the application that is doing select, update on this table?
                    >
                    ABSOLUTELY! And that is a GOOD thing since it impacts them in two, very important POSITIVE ways.

                    First, those user WILL NOT get the data that is being dropped in the result sets of there queries.

                    Second, when you drop the partition Oracle WILL NOT include that partition in any full table scans that it performs for other users that are doing DML. That means that the performance will be improved for those other users because there is less data to scan.

                    It is your delete operation that is likely affecting other users DML results and their performance.

                    See 'Considerations when Updating Indexes Automatically' in the VLDB and Partitioning Guide
                    http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin002.htm

                    In particular see the rule of thumb in the first bullet below.
                    >
                    The following implications are worth noting when you specify UPDATE INDEXES:

                    •The partition DDL statement takes longer to execute, because indexes that were previously marked UNUSABLE are updated. However, you must compare this increase with the time it takes to execute DDL without updating indexes, and then rebuild all indexes. A rule of thumb is that it is faster to update indexes if the size of the partition is less that 5% of the size of the table.

                    •The DROP, TRUNCATE, and EXCHANGE operations are no longer fast operations. Again, you must compare the time it takes to do the DDL and then rebuild all indexes.

                    •When you update a table with a global index:

                    ◦The index is updated in place. The updates to the index are logged, and redo and undo records are generated. In contrast, if you rebuild an entire global index, you can do so in NOLOGGING mode.

                    ◦Rebuilding the entire index manually creates a more efficient index, because it is more compact with better space utilization.
                    >
                    And for the general process used see the 'Updating Indexes Automatically' section of the same doc
                    >
                    Before discussing the individual maintenance operations for partitioned tables and indexes, it is important to discuss the effects of the UPDATE INDEXES clause that can be specified in the ALTER TABLE statement.

                    By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then rebuild the entire index or, for a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the index at the time it executes the maintenance operation DDL statement. This provides the following benefits:

                    •The index is updated with the base table operation. You are not required to later and independently rebuild the index.

                    •The index is more highly available, because it does not get marked UNUSABLE. The index remains available even while the partition DDL is executing and it can access unaffected partitions in the table.

                    •You need not look up the names of all invalid indexes to rebuild them.
                    1 2 Previous Next