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';
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) local;
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.
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.
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.
ALTER TABLE scubagear MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;
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
- 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.