Oracle9i Index Partitioning Options

Similar to table partitioning, index partitioning is a method of intelligently breaking larger indexes into smaller pieces across many tablespaces. The two types of index partitioning are local and global. Local-partitioned indexes have the same partitioning key values, number of tablespaces, and partitioning rules as the underlying table, whereas global-partitioned indexes have a PARTITION BY RANGE or PARITION BY LIST clause that enables the partitioning values, number of partitions, and tablespaces themselves to all be defined and vary from the underlying table partitioning structure. These indexes can either be prefixed (meaning they contain a leading part of the index key) or nonprefixed (meaning they're index partitioned on a value different from the indexing column).

Types of Index Partitioning

A local index is one in which a single index partition's key values reference table rows in a single table partition. Listing 15.6 is an index example based on the ST_SALES table range partitioning example from Listing 15.1. The range partition key on the underlying table, ST_SALES, is sales_ sale_date. A locally defined index is said to be equi-partitioned, meaning it has the same number of partitions with the same rules of partitioning. All local indexes are equi-partitioned by default. Notice that the locally defined index in Listing 15.6 has no PARTITION BY RANGE or PARTITION BY LIST clause because Oracle automatically uses the same number of partitions, as well as the same partitioning rules. In addition, the PARTITION clause is used in Listing 15.6 to enable you to control the names of the index partitions.

Listing 15.6. Sales Tracking ST_SALES Local Partition Index Example

CREATE INDEX st_sales_Date_Idx on st_sales (sales_sale_date) LOCAL (PARTITION st_i_q1_00 TABLESPACE ts_st_i_q1_00, PARTITION st_i_q2_00 TABLESPACE ts_st_i_q2_00, PARTITION st_i_q3_00 TABLESPACE ts_st_i_q3_00, PARTITION st_i_q4_00 TABLESPACE ts_st_i_q4_00 ); ok pm

A global-partitioned index has a partitioning structure (and probably partitioning keys) that differ from the table being indexed. Listing 15.7 illustrates the syntax for a global-partitioned index on the ST_SALES table, as previously discussed in Listing 15.1. Notice that the indexed column and the PARTITION BY RANGE columns are the same. Also notice that having the same number of partitions as the underlying table is unnecessary.

Listing 15.7. Sales Tracking ST_SALES Global Prefixed Partition Index Example

CREATE INDEX st_sales_Customer_ID_Idx on st_sales (customer_id) GLOBAL PARTITION BY RANGE(customer_id) (PARTITION st_i_p1 VALUES LESS THAN 1000 TABLESPACE ts_st_i_p1, PARTITION st_i_p2 VALUES LESS THAN 2000 TABLESPACE ts_st_i_p2, PARTITION st_i_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_st_i_p3 );

Prefixed Versus Non-prefixed Partitioned Indexes

A local-partitioned index can be created on a column other than the partitioning key of the underlying table. Listing 15.8 shows a nonprefixed index being created on the CUSTOMER_ID column. A local index cannot have a PARTITION BY RANGE clause (because it would not be a local-partitioned index), so the same partitioning rules from the underlying table apply here. The index will be created based on the Customer ID field, but the index values will be partitioned by sales_salp date as defined in the underlying table.

Listing 15.8. Sales Tracking ST_SALES Local, Nonprefixed Partition Index Example

CREATE INDEX st_sales_Customer_ID_Idx on st_sales (customer_id) LOCAL (PARTITION st_i_q1_00 TABLESPACE ts_st_i_q1_00, PARTITION st_i_q2_00 TABLESPACE ts_st_i_q2_00, PARTITION st_i_q3_00 TABLESPACE ts_st_i_q3_00, PARTITION st_i_q4_00 TABLESPACE ts_st_i_q4_00 );

Evaluating the Index Partitioning Options

A local, prefix-partitioned index relationship to the base table is illustrated in Figure 15.4. Notice that a local partition is equi-partitioned and the index has the same partitioning structure and rules as the underlying table.

Local, prefixed-partitioned indexes are the most efficient of the partitioned indexes because the optimizer knows that the rows in the underlying table will be indexed in a single partition. Oracle therefore does not have to scan all the partitions to satisfy the SQL statement request.

A local, nonprefix-partitioned index is more work for Oracle because it must scan each of the partitions looking for values. In Figure 15.5, the index is created on the CUSTOMER_ID column, but the index leaves are still organized by the date field as defined by the underlying ST_SALES table. The CUSTOMER_ ID leaves can be in any of the partitions. This type of index is best suited for parallel-processing Oracle environments in which each processor working on the SQL statement can search a partition.

Global, prefix-partitioned indexes are best for any kind of range-scan–type processing. This kind of an index groups the rows together in the same partition, and the cost-based optimizer knows in which partition to look for the range of values being requested. Figure 15.6 illustrates the relationship of a global index to its underlying table.