Indexes may be marked UNUSABLE for Index-organized table - Oracle Indexes and Partitioned Tables

Q.  Indexes may be marked UNUSABLE for which table type, the index behavior for adding partition is?

1) For local indexes, the behavior is the same as for heap tables.
2) All global indexes remain usable.

- Published on 12 Aug 15

a. Regular (Heap)
b. Index-organized
c. Both A & B
d. None of the above

ANSWER: Index-organized
 

    Discussion

  • Nirja Shah   -Posted on 28 Sep 15
    - Indexes may be marked UNUSABLE as explained in the following

    - Regular (Heap) - Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
    - The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked UNUSABLE and must be rebuilt.
    - All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

    - Index-organized
    - For local indexes, the behavior is identical to heap tables.
    - All global indexes remain usable.

Post your comment / Share knowledge


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)