Oracle Partition - Index (Local, Global)

Card Puncher Data Processing


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.

Discover More
Card Puncher Data Processing
Oracle - Partitions (Table and Indexes)

partition in Oracle. Partitioning enables you to store one logical object – a table or index – transparently in several independent segments. Partitioning can provide great performance improvements...
Oracle Segment Extent Data Block
Oracle Database - (Logical) Segment (Database Object Storage)

A segment is a logical storage structure that contains data structure (segment type) of database objects. For example: each table's data is stored in its own data segment, while each index's data...
Card Puncher Data Processing
Oracle Partition - Data Load

Data Loading with partitions. Taking advantage of partitioning for fact tables reduces index and statistics maintenance time during ETL processes. Because the majority of inserts and updates impact...
Card Puncher Data Processing
Oracle Partition - Drop a partition (instead of records)

The conventional method is to delete the records, but deletion causes undo and redo information to be generated, which impacts database performance. This can also be a very time-consuming operation, depending...
Oracle Range Partitions Syntax
Oracle Partition - Range Interval Partitioning

A range partitioning where the database automatically creates partitions for a specified . At least one range partition using the PARTITION clause. The partitioning key can only be asingle column...
Card Puncher Data Processing
Oracle Partition - Tablespace (Table Move|Index Rebuild)

When a table is partitioned, the physical logical entity (the segment) is no more on a 'table segment' but: on 'table partition' segment or 'table subpartition' segment The tablespace information...

Share this page:
Follow us:
Task Runner