Defining a global Partitioned Index

Oracle provides two types of indexes on partitioned tables,

1. global
2. 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.

Example:The following statement creates a global prefixed index cost_ix on the sample table sh.sales with three partitions that divide the range of costs into three groups:

CREATE INDEX cost_ix ON sales (amount_sold)
GLOBAL PARTITION BY RANGE (amount_sold)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

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.

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
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.

A global index is harder to maintain for two reasons. When data in an underlying table partition is moved or removed, all partitions of the global index are affected.

1. A global index requires partition maintenance in relation to table size, not partition size, and does not support independent partitions.
2. An underlying table partition can only be recovered by rebuilding the entire global index.

When an underlying table partition is recovered, all related global index entries must also be recovered. Because these records may be scattered across all partitions of the index, with other records for other partitions that are not being recovered, this can only be achieved by recreating the entire global index.

Global Index: A global index is a one-to-many relationship, allowing one index partition to map to many table partitions. The documentation says that a "global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or non-partitioned, table".

The following statement creates a global prefixed index cost_ix on the sample table sh.sales with three partitions that divide the range of costs into three groups:

CREATE INDEX cost_ix ON sales (amount_sold)
GLOBAL PARTITION BY RANGE (amount_sold)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));