What are Global and Local Indexes?

LOCAL INDEXES

Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments.

In the local prefixed index the partition key is specified on the left prefix. Local keyword tells oracle to create a separte index for each partition.Local prefixed indexes can be unique or non unique and is easier to manage

Ex:CREATE INDEX invoices_idx ON invoices (invoice_date)

LOCAL (PARTITION invoices_q1 TABLESPACE users,  

PARTITION invoices_q2 TABLESPACE users);

GLOBAL INDEXES

A global Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and this the global clause allows you to create a non-partitioned index.

Global indexes may perform uniqueness checks faster than local (partitioned) indexes.Also you cannot create global indexes for hash partitions or subpartitions.

Ex:

SQL> CREATE INDEX invoices_idx

ON COST_TABLE(invoice_date) 

GLOBAL PARTITION BY RANGE (invoice_date)

(PARTITION invoices01 VALUES LESS THAN 

(TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION invoices02 VALUES LESS THAN 

(TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users);

Similar to table partitions, it is possible to move them from one device to another. But unlike table partitions, movement of index partitions requires individual reconstruction of the index or each partition (only in the case of global index).

Ex:

SQL> alter index stud_ind rebuild partition p2

Index partitions cannot be dropped manually.They are dropped implicitly when the data they refer to is dropped from the partitioned table.