5.7 How do I…Partition an index?

Problem

I am creating a very large index and want to improve access to the table by using a partitioned index. Also, I want to perform index maintenance such as generating statistics, restoring index parts, and data loading on one part of an index, while the rest of the index is available for use by users. How do I partition an index?

Technique

Use the PARTITION clause of the CREATE INDEX or ALTER INDEX command to create a partitioned index. A partitioned index, like a partitioned table, allows for multiple partitions with potentially different storage parameters, including tablespaces. There are two methods to define a partitioned index: LOCAL and GLOBAL. A LOCAL partitioned index is based upon the underlying partitioned table. It is partitioned with the same ranges of the table, and repartitions itself automatically as the underlying table is repartitioned. This is the preferred method in most cases, as the partition logic is driven by the underlying table. The syntax for creating a local partitioned index is shown in Figure 5.14. A GLOBAL partitioned index allows for flexibility in defining partitioning ranges. If you want to partition your index more finely or differently than the underlying table, define your index as GLOBAL. The syntax for creating a GLOBAL partitioned index is shown in Figure 5.15.

The PARTITION BY RANGE clause, specified only with the global index, defines which columns are to be used to base the partition ranges upon. As with a partitioned table, the column list specifies the columns on which the index is partitioned.

The PARTITION clauses specify the individual partition names, their storage parameters, and their tablespaces. For LOCAL indexes, each index partition must correspond one-to-one with the underlying table’s partitions.

The VALUES LESS THAN clause specifies the upper bounds for the partitions. This can only be defined in a GLOBAL index. All LOCAL indexes automatically are assigned to the range of the corresponding table partition. Be sure to include a sufficiently large MAXVALUE for the last partition.

Steps

1. Connect to SQL*Plus as the WAITE user account. Create a partitioned table that will be used as a basis for the partitioned index by running CH5_14.SQL, as shown in Figure 5.16. For more information on partitioned tables, refer to How-To 4.10.

2. The EMP05 table, with partitions stored in the USER, TEMP, and SYSTEM tablespaces. Each partition of the table will also have its own storage parameters. Now, run the CHP5_15.SQL script, shown in Figure 5.17, to create a LOCAL partitioned index. The EMP05 table has four partitions, and so the index must have four partitions. Note that for this LOCAL index, the ranges are not specified, as they will inherit the ranges for the underlying EMP05 table.

How It Works

The CREATE INDEX command has an optional PARTITION clause to create partitioned indexes. Step 1 describes the difference between local and global partitioned indexes. Step 2 creates the EMP05 partitioned table to be used as a basis for the partitioned index. Step 3 creates the EMP05_PART_INDEX local partitioned index using the underlying value ranges from the underlying EMP05 partitioned table.

Partitioned indexes have several advantages over regular indexes. As with partitioned tables, if one part of the partition is unavailable, the other parts are available for select, insert, update, and delete. Only those records that are contained within the down partition will be unavailable. Also, index partitions may be rebuilt one at a time and structures validated. Index partitions may also be spread across multiple tablespaces, with varying storage parameters. All of these features greatly improve the availability and maintenance of index.

Comments

In the example, the SYSTEM and TEMP tablespaces were chosen for storage of partition parts. In reality, you would not want to put any objects in these tablespaces. They were included to show a simple example of how to put partition parts into separate tablespaces.

You cannot truncate an index partition; however, the ALTER TABLE TRUNCATE PARTITION statement truncates the matching partition in each local index.

As with partitioned tables, the indexes columns may not be of the LONG, RAW, or ROWID datatypes.