Oracle Partition - Index (Local, Global)


You can create two types of indexes on partitioned tables:



select * from dba_ind_partitions where status <> 'USABLE';
select * from dba_ind_subpartitions where status <> 'USABLE';


SELECT * FROM all_indexes where partitioned = 'NO'; -- Global indexes if the table is partitioned
select distinct locality from all_part_indexes;  -- Not sure what it's


The index is partitioned in exactly the same way as the base table. For instance, you can create a local index on the TRANS_AMOUNT column of the TRANS table as follows:

create index in_trans_01
on trans (trans_amount)

This creates a range-partitioned index, on the TRANS_DT column, the same way the TRANS table is partitioned. All the index entries for a specific partition, such as Y05Q1, will exist only inside the corresponding partition of the index.

When a partition is dropped, the corresponding partition of any local index is also dropped. If there is a global index, however, that index can become unusable when you drop the partition.


The index can span all partitions in the base table. For example, suppose there is a primary key on TRANS, on the TRANS_ID column. The primary key can be anywhere inside the table, across all the partitions. In this case, the index entries of a partition of the table may exist outside the corresponding partition of the index. For such a situation, create a global index, as follows:

alter table trans 
add constraint pk_trans
primary key (trans_id)
using index global;

If the index is global, the index can become unusable when you drop the partition. You need to rebuild it completely.


Rebuild as a separate operation


Partitioning is always introduced as a technique for managing large objects. Although it's applicable to objects of any size, the advantages are more obvious in large tables.

When you rebuild an index on a nonpartitioned table, your only option is to build the entire index in one statement. If the table is partitioned, however, you can rebuild partitions of local indexes one at a time.

For instance, in the case of the TRANS table, you can rebuild the Y05Q1 partition of the IN_TRANS_01 local index:

-- Partition without subpartition
alter index in_trans_01 rebuild partition y05q1        PARALLEL (DEGREE 2);
-- Subpartitions
alter index in_trans_01 rebuild subpartition y05q1  PARALLEL (DEGREE 2);

as opposed to rebuilding the IN_TRANS_01 index as a whole. The advantage of this approach is even more pronounced when you load a specific partition and the index needs to be rebuilt only on that partition, not on the rest of the index.

You can also finds and rebuilds any unusable local index partitions for table scubagear, partition p1.



You can rebuild global index partitions in two ways:

  • Rebuild each partition by issuing the ALTER INDEX … REBUILD PARTITION statement in parallel.
  • Drop the entire global index and re-create it. This method is more efficient because the table is scanned only one time.

Rebuild when moving segment

When moving segment, with an alter move, a partition exchange or a split, you may apply the following statement:

  • UPDATE GLOBAL INDEXES: to update global indexes
  • INCLUDING INDEXES: ONLY applies to local – not global – indexes. It is only useful for getting local indexes changed. will not affect global at all.

Powered by ComboStrap