4 Replies Latest reply: May 9, 2013 4:11 PM by rp0428 RSS

    Question about Global index and Table Partitions

    983554
      I have created a global index for a partitioned table now in the future the partitions will be dropped in the table. Do I need to do anything to the global index? Does it need to be rebuilt or would it be ok if partitions get dropped in the table?
        • 1. Re: Question about Global index and Table Partitions
          sb92075
          980551 wrote:
          I have created a global index for a partitioned table now in the future the partitions will be dropped in the table. Do I need to do anything to the global index? Does it need to be rebuilt or would it be ok if partitions get dropped in the table?
          every DROP will change the state of the Global Index to be UNUSABLE.
          • 2. Re: Question about Global index and Table Partitions
            983554
            does that mean it need to be rebuilt every time a partition is dropped in a table?
            • 3. Re: Question about Global index and Table Partitions
              sb92075
              980551 wrote:
              does that mean it need to be rebuilt every time a partition is dropped in a table?
              INDEX needs to be rebuilt if you want it to be USABLE.
              • 4. Re: Question about Global index and Table Partitions
                rp0428
                >
                I have created a global index for a partitioned table now in the future the partitions will be dropped in the table. Do I need to do anything to the global index? Does it need to be rebuilt or would it be ok if partitions get dropped in the table?
                >
                You can use the UPDATE INDEXES clause. That allows users to keep using the table and Oracle will keep the global indexes updated.

                Otherwise, as already stated all global indexes will be marked UNUSABLE.

                See 'Dropping Partitions' in the VLDB and Partitioning Guide
                http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1007479
                >
                If local indexes are defined for the table, then this statement also drops the matching partition or subpartitions from the local index. All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE unless either of the following is true:

                You specify UPDATE INDEXES (Cannot be specified for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)

                The partition being dropped or its subpartitions are empty