Index Partitioning

-   Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and    
     scalability. 
-   They can be of 2 types:
        a)  Global Indexes (partitioned independently) 
        b)  Local indexes (automatically linked to a table's partitioning method )


Local Partitioned Index
-   each partition of a local index is associated with exactly one partition of the table. 
-   You cannot explicitly add/drop a partition to a local index. Instead, new partitions are added/dropped to local indexes only when you add/drop a partition to the underlying table. 
-   A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.


Global Partitioned Index

-   Global partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

-   The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index.

-   Global prefixed indexes can be unique or nonunique.

-   You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

Maintenance Global Partitioned Index 

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH) 
COALESCE (HASH) 
DROP 
EXCHANGE 
MERGE 
MOVE 
SPLIT 
TRUNCATE 

These indexes can be maintained by appending the clause UPDATE GLOBAL INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:

• The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
• The index doesn't have to be rebuilt after the operation.

Ex:     ALTER TABLE DROP PARTITION P1 UPDATE GLOBAL INDEXES

Global Nonpartitioned Indexes

Global nonpartitioned indexes behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record.

Partition Index Example:

Underlying table:

CREATE TABLE employees

(employee_id NUMBER(4) NOT NULL,
 last_name VARCHAR2(10), 
 department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE part1, 
 PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE part2, 
 PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE part3);

Example of a Local Index Creation

CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;

Example of a Global Index Creation

CREATE INDEX employees_global_idx ON employees(employee_id);

Example of a Global Partitioned Index Creation

CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(5000),
 PARTITION p2 VALUES LESS THAN(MAXVALUE));