Overview

Partition Index

Indexes can also be partitioned, and database performance can be improved through partitioning.

Indexes can be partitioned as global indexes or local indexes, and each method has differences in structure and operational characteristics.

Method

Global Index

This method sets partitions on the index independently of the table, so you can create a global partition index regardless of whether the table is partitioned or not.

A partition of a global partition index can point to rows in any partition of the table.

Example of Creating a Global Index

SQL> CREATE INDEX GLOBAL_IDX ON DEPT_PART_R (DEPTNO)
     GLOBAL PARTITION BY RANGE(DEPTNO) (
     PARTITION GLOBAL_IDX_PART1 VALUES LESS THAN (16) ,
     PARTITION GLOBAL_IDX_PART2 VALUES LESS THAN (31) ,
     PARTITION GLOBAL_IDX_PARTMAX VALUES LESS THAN (MAXVALUE));

Index 'GLOBAL_IDX' created.

Local Index

This method partitions the index by the key that goes into the table partition when the table is partitioned, and you must declare it simply as LOCAL without entering any information for each partition.

The names are generated automatically, and other information is set to default values.

An index set as a local partition corresponds one-to-one with a partition of the table, and a partition of a local index points only to rows in one partition of the table.

Example of Creating a Local Index

SQL> CREATE INDEX LOCAL_IDX ON DEPT_PART_R (MGRNO) LOCAL;

Index 'LOCAL_IDX' created.

Caution

The following are conditions under which partition indexes become Unusable. When performing partition operations as shown below, the partition index may become Unusable and affect query execution.

Partition Operation	Local Index	Global Index
ADD	-	-
DROP	-	Unusable
SPLIT	Unusable	Unusable
RENAME	-	-
MOVE	Unusable	Unusable
TRUNCATE	-	Unusable