RelationalDBDesign RelationalDBDesign 

Partitioned Tuning   «Prev  Next»
Lesson 5 Local partitioned Indexes
Objective Define a local partitioned index.

Local Partitioned Indexes using Oracle

Defining a local partitioned index

In a local partitioned index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index handles data that resides in only one partition. An index is defined as local only if the LOCAL attribute is specified when the index is created. An index can be created with only one LOCAL clause.
The SlideShow below explains the structure of a local partitioned index.

  1. A master index node makes the separate indexes appear as a single index.
  2. Each local partition has a separate index tree.

Oracle Local Index

Advantages of local partitioned indexes

For data warehouse and DSS applications, local indexes have several advantages over global indexes. The features of local partitioned indexes are described in the table below:

Features of local partitioned Indexes

Features of local partitioned indexes Each local partition can be maintained independent of the whole index
Parallel range query Many index partitions can be scanned in parallel by range queries on the index key
Equi-partitioning of local indexes Oracle is able to generate better query access plans because the local index is equi-partitioned with the underlying table
Intra-partition parallelism You can build a single local index partition in parallel by using the BUILD_PART_INDEX procedure in the DBMS_PCLXUTL package.
Partition values You can partition an index according to the partition values, regardless of the index key value. For example, you can create a CITY index on our partitioned sales_data table, and partition the index according to the YEAR column.

The well skilled DBA knows to always create a local partitioned index with the same key as the partitioned table. That way, all of the index keys in the local index point to values within the corresponding table partition.
In summary, partitioned indexes work very well in conjunction with partitioned tables, and these constructs can lead to some very real performance benefits.
Once you create index partitions, you will need to maintain them. The next lesson explores index maintenance with partitioned indexes.