Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 274

A partition is a segment of data with its own metadata. While logically a part of a table, partitions are actually
segments that can be addressed directly with most of the internal characteristics of the parent objects (i.e. the table
or index). This internal physical structure allows for independence and makes it a manageable target for backup,
recovery, and other dataset-related operations.

Partitioned Indexes
Oracle provides various indexing methods that can be utilized based on the data or column being indexed and the
access patterns on the columns. Selecting the right indexing strategy also depends on various factors, type of data
being stored, uniqueness of data, cardinality of data, etc.
Based on how the data will be accessed from the different partitions, indexes on a partitioned table can be a
local index and global index. While these are just types of indexes, they can be implemented using various indexing
methods, such as bitmap indexes and B-tree indexes. So the challenge is not in the selection of the type of index,
which is easier because it goes one-on-one with the type of implementation (e.g., partitioned indexes) but in the
selection of the method used in indexing the tables. The various indexing methods are

• B-tree index

• Reverse key index

• Bitmap index

• Index-organized tables

• Function-based index

If no type of index is explicitly specified, Oracle creates a B-tree index by default. Figure 9-1 illustrates a B-tree
index structure. Similar to Figure 9-1 , an index structure contains several branches and steps in its structure. Initially,
each index tree has one level. If the data in the table is very small, there may be only one index block. In that case,
the leaf block is the same as the branch block. As the data grows, the level increases and then there is a branch block
and a leaf block with a parent-child relationship. The maximum number of levels that the B-tree index can grow to is
24 (i.e., 0 to 23), which means that with two rows per index block it can hold approximately 18 billion leaf blocks.
When the index look up is about 4-5 levels deep, it's worth considering partitioning the index.

Root Block

< 6000 < 9000

< 6000 < 7000 < 8000 < 9000

Branch Block

key:8324 key:8325 key:8326 key:8327

Leaf Block

Figure 9-1. B-tree index structure

Once the type of index is identified, the type of implementation can be defined based on several factors such as
data volume and access patterns. Similar to a data partition, an index partitioning will help improve manageability,
availability, performance, and scalability. Partitioning of indexes can be done in one of two ways, either independent
of the data partition, where the index is of a global nature (global indexes), or dependent on the data partition by
directly linking to the partitioning method of tables where the indexing is of a local nature (local indexes).