Difference between Oracle global index and local index

In my previous article we have seen about partitioning in oracle with detailed example. In this article we can see the difference between oracle global index and local index with examples. In most of the interviews they have been asked the oracle global index and local index difference. When you are dealing with oracle partitions we need to use the “Global” or “Local” keyword to create the indexes. The local indexes are faster than global indexes. In this article we will see how it differs from each other.

What we will see in this article?

1. What is oracle global index and local index?
2. Difference between global and local index with example.

What is Oracle global index and local index?

In this section we will see the pointwise description of Oracle global and local index in detail.

Oracle Global Index :

1. The oracle global index is index which has one to many relationship between the partitioned table.

2. We can apply the partition to global table or partitioned table where partition will be global or local.

3. When you create the index on non partitioned table as well.

4.The main word you need to remember is it has globally created to all partitions and it will be slower than the local index.

5.The global indexes are created to every partition of the partitioned table.

6. We can use the global partitions in OLTP or online transaction processing data and not on OLAP.

7.The maintainace of global partitioned index is also difficult to manage. When you drop partition the global index is applied to the specified table.

Example :

Lets consider that the order_items table has applied with range partition on Cost column. We can create global index on the Order_items table.

CREATE INDEX Order_item_idx
on Order_item (Cost)
GLOBAL
(PARTITION Cost_1 VALUES LESS THAN (10010)),
(PARTITION Cost_1 VALUES LESS THAN (20020)),
(PARTITION Cost_1 VALUES LESS THAN (30030)),
(PARTITION Cost_1 VALUES LESS THAN (40040)),
(PARTITION Cost_1 VALUES LESS THAN (50050));

The above statement will create global partitioned index on order_item table.

Oracle Local Index :

1.The oracle local indexes are indexes which has been created with one to one relationship between partitioned tables and partitions as well.

2.The local index approach is the “Divide and conquer” approach where indexes are applied to the local partitions from the table.

3. The local partitioned indexes must create the local partitions and it will be faster than global indexes.

4. If you compare the Oracle explain plan of local and global indexes the local indexes explain plan value is less than global and it will be better plan than global index plan.

5. The key value of local partition and value of index is identical in case of Local indexes.

6. You can create index for particular partition only not on whole table.

Example of Local Partition and explanation :

If we have created one list partition on Order table and if you want to create the index on the table columns you require to use following syntax :

CREATE INDEX Index_name
on Table_name (Column_name)
LOCAL
(PARTITION paritionname_indexname),
(PARTITION paritionname_indexname),
(PARTITION paritionname_indexname);

You need to make sure that create index statement will fail in case of any non equal partition. Lets consider that we have created partition for one of the table of states in India and need to create index on it.

CREATE INDEX IDX_States
on Order(State)
LOCAL
(PARTITION Maharashtra_idx),
(PARTITION Gujrath_idx),
(PARTITION Punjab-idx);

This will create the local index on multiple partitions on order table and when you will call the data from the states column it will internally hit the partition.

I hope you get clear idea about local and global index in detail. If you have any issues or concerns kindly comment in comments section.