Oracle provides two types of indexes on partitioned tables, global and local.
In a global partitioned index, a single B-tree structure is created to index all of the rows in the entire partition.
The global index organizes data that resides on more than one partition.
In a global partitioned index, all of the partitions are in a single index, like a non-partitioned table.
A single global index for all table partitions
At the table level, the partitions are separate entities
A partitioned index is an index that, like a partitioned table, has been divided into smaller and more manageable pieces.
Global indexes are partitioned independently of the table on which they are created, whereas local indexes are automatically linked to the partitioning method for a table. Like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability.
The following graphic shows index partitioning options.
Advantages of global partitioned indexes
Because there is only a single index tree in a global partitioned index, there are fewer index partition probes. Therefore, for high-speed (OLTP) Online Transaction Processing applications, global partitioned indexes are preferred. Although global indexes often provide better throughput and improved performance compared to local non-prefixed indexes, a global partitioned index is
more difficult to maintain.
For data warehouse applications and DSS(Decision Support System) (DSS) applications, local partitioned indexes are a better choice. The next lesson looks at the second method for the partitioning of indexes, the local partitioned index. We will discuss the maintenance of partitioned indexes in a later in the course.