2

I have Oracle 12c database and partitioned table. There is a old partition which has 68734MB which is 27% of total.

I need to drop this partition. This is a production database and I does not have a downtime window for this.

I want to know if I drop this partition, will the indexes of this table be unusable or rebuild ? What would be the best strategy to drop this partition with minimum impact on the live traffic?

  • 1
    Depends on the indexes. Global indexes will need to be rebuilt. – a_horse_with_no_name Mar 1 '17 at 7:12
1

When you drop one or more partitions, local indexes are also dropped. You need to rebuilt Global indexes unless the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause is specified in the ALTER TABLE DROP PARTITION statement.

For example, I have a table called TBL1 with range partition and global indexes.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i 
where i.index_name=p.index_name 
and table_name='TBL1'; 

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_INDEX    GI3        USABLE
T_INDEX    GI2        USABLE
T_INDEX    GI1        USABLE



SQL> alter table tbl1 drop partition p0 UPDATE GLOBAL INDEXES;

Table altered.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i 
where i.index_name=p.index_name 
and table_name='TBL1'; 

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_INDEX    GI3        USABLE
T_INDEX    GI2        USABLE
T_INDEX    GI1        USABLE

As you can see, if I specify UPDATE GLOBAL INDEXES clause, Global indexes don't become unusable.

After dropping the partition, indexes entries belongs to those partitions remain. When a query uses these indexes, they simply ignore such entries(orphaned). You can clean up such orphaned entries in your regular maintenance window. Details are also given in the following documentations.

The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.

In any case, the dropping or truncation of a partition completes immediately, the index is always available for use, and the maintenance of the index is deferred

Documentation:

  • Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
  • On Oracle Database 12c

    • This is very useful information. Do you have any idea about time duration of this operation, given that this partition has most amount of data ? Will it take several hours or partition drop happens reasonable time ? ( with in seconds or minutes) – Viraj Mar 1 '17 at 8:45
    • As per the last sentence quoted above the actual dropping of a partition is a meta data operation so will only involve updates to a handful of records. Basically it will be quick. – BriteSponge Mar 1 '17 at 10:05

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.