Indexing Partitions in Oracle

By: Bob Bryla, Kevin Loney


When you create a partitioned table, you should create an index on the table. The index may be partitioned according to the same range values used to partition the table. In the following listing, the CREATE INDEX command for the BOOKSHELF_LIST_PART (list-partitioned) table is shown. The index partitions are placed in the PART1_NDX_TS and PART2_NDX_TS tablespaces.

p0349-02

Notice the LOCAL keyword. In this CREATE INDEX command, no ranges are specified. Instead, the LOCAL keyword tells Oracle to create a separate index for each partition of the BOOKSHELF_LIST_PART table. Two partitions are created on BOOKSHELF_LIST_PART. This index will create two separate index partitions—one for each table partition. Because there is one index per partition, the index partitions are “local” to the table partitions.

You can also create “global” indexes, as shown next. A global index contains values from all partitions.

p0349-03

The GLOBAL clause in this CREATE INDEX command allows you to create a nonpartitioned index (as shown here) or to specify ranges for the index values that are different from the ranges for the table partitions. Local indexes may be easier to manage than global indexes; however, global indexes may perform uniqueness checks faster than local (partitioned) indexes perform them.

Leave a Reply