What is Local and Global Indexes?

Local and Global Index

Local and Global index usage comes during the Partitioning of the table.

When using Oracle partitioning, you can specify the "global" or "local" parameter in the create index syntax.

Global Index

A global index is a one-to-many relationship, allowing one index partition to map to many table partitions.  The docs say 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".

SYNTAX: -

      CREATE INDEX item_idx

on all_fact (item_nbr)

GLOBAL

(PARTITION city_idx1 VALUES LESS THAN (100)),

(PARTITION city_idx1 VALUES LESS THAN (200)),

(PARTITION city_idx1 VALUES LESS THAN (300)),

(PARTITION city_idx1 VALUES LESS THAN (400)),

(PARTITION city_idx1 VALUES LESS THAN (500));

Local Index

A local index is a one-to-one mapping between an index partition and a table partition.  In general, local indexes allow for a cleaner "divide and conquer" approach for generating fast SQL execution plans with partition pruning.

SYNTAX:-

   CREATE INDEX year_idx

On all_fact (order_date)

LOCAL

(PARTITION name_idx1),

(PARTITION name_idx2),

(PARTITION name_idx3);

For more details on Indexes and it's Types, you can check the below post-

http://tipsfororacle.blogspot.in/2016/09/oracle-indexes.html