5 Replies Latest reply: Nov 29, 2012 7:04 PM by Max Seleznev RSS

    When do Global and Local Indexes becoming UNUSABLE ?

    9730283
      RDMS Version : 11.2.0.2
      Platfomrm : AIX 6.1

      For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ? I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ?

      For partitioned tables with Global Index, the global index will be listed in DBA_INDEXES . Right ?
        • 1. Re: When do Global and Local Indexes becoming UNUSABLE ?
          sb92075
          ExpansiveMind wrote:
          RDMS Version : 11.2.0.2
          Platfomrm : AIX 6.1

          For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ?
          INSERT /*+ APPEND */
          • 2. Re: When do Global and Local Indexes becoming UNUSABLE ?
            Marcus Rangel
            If you move a table partition, indexes become unusable.
            I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ?
            I think this may happen if your parameter SKIP_UNUSABLE_INDEXES is set to FALSE.
            • 3. Re: When do Global and Local Indexes becoming UNUSABLE ?
              Max Seleznev
              Pretty much any operation that changes ROWIDs will make indexes or index partitions unusable. That includes split, merge, move etc. Dropping table partition will make global index unusable.
              An advantage of local indexes is that most of the operations affect single index partition leaving the rest of the index in good state. Global index is affected as a whole even though it could also be partitioned.

              SELECT will not work only in the case it uses that unusable index and even then you can use SKIP_UNUSABLE_INDEX parameter to change this behavior.

              Any index will be listed in DBA_INDEXES no matter local or global. Please keep in mind that non-partitioned index on a partitioned table is also considered global.

              You can find more information in this [url http://docs.oracle.com/cd/E11882_01/server.112/e25523/toc.htm] document.
              • 4. Re: When do Global and Local Indexes becoming UNUSABLE ?
                rp0428
                >
                Pretty much any operation that changes ROWIDs will make indexes or index partitions unusable. That includes split, merge, move etc. Dropping table partition will make global index unusable.
                >
                That is only true for the default operation. If you specify the UPDATE INDEXES clause for those operations the indexes are not made unusable

                The very document you cited for OP would have told you that in the 'Updating Indexes Automatically' section
                >
                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 indexes at the time it executes the maintenance operation DDL statement. This provides the following benefits:

                The indexes are updated with the base table operation. You are not required to update later and independently rebuild the indexes.

                The global indexes are more highly available, because they are not marked UNUSABLE. These indexes remain available even while the partition DDL is executing and can access unaffected partitions in the table.

                You need not look up the names of all invalid indexes to rebuild them.
                . . .
                The following operations support the UPDATE INDEXES clause:

                ADD PARTITION | SUBPARTITION
                COALESCE PARTITION | SUBPARTITION
                DROP PARTITION | SUBPARTITION
                EXCHANGE PARTITION | SUBPARTITION
                MERGE PARTITION | SUBPARTITION
                MOVE PARTITION | SUBPARTITION
                SPLIT PARTITION | SUBPARTITION
                TRUNCATE PARTITION | SUBPARTITION
                • 5. Re: When do Global and Local Indexes becoming UNUSABLE ?
                  Max Seleznev
                  >
                  That is only true for the default operation. If you specify the UPDATE INDEXES clause for those operations the indexes are not made unusable
                  >

                  Good catch.