Local Index Versus Global Index on Partition Table

Most of the interviewer ask these question on Partition index...

1. What is local index?
2. What is global index?
3. When would you force to create Global index on Partition table?
4. When would you recommend to create global index versus local index?

To answer these question....

1. What is local index?
Local Partitioned indexes are easier to manage and each partition of local indexes are associated with that partition. They also offer greater availability and are common in DSS environments. When we take any action(MERGE, SPLIT,EXCHANGE etc) on local partition, it impacts only that partition and other partition will be available. We can not explicity add local index to new partition. Local index will be added implicitly to new partition when we add new partition on table. Likewise, we can not drop the local index on specific partition. It can be dropped automatically when we drop the partition from underlying table. Local indexes can be unique when partition key is part of the composit index. Unique local indexes are useful for OLTP environment. We can can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.
SQL> CREATE TABLE employees
2 (employee_id NUMBER(4) NOT NULL,
3 last_name VARCHAR2(10),
4 department_id NUMBER(2))
5 PARTITION BY RANGE (department_id)
6 (PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE ODS_STAGE_DATA,
7 PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE ODS_STAGE_DATA,
8 PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE ODS_STAGE_DATA);

Table created.

SQL> declare
2 v_no number :=1;
3 begin
4 delete employees;
5 for i in 1..10 loop
6 insert into employees values(v_no,'name...',v_no);
7 v_no := v_no+1;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> create index idx_local on employees(last_name) local;

Index created.

SQL>
2. What is global index?
Global index used in OLTP environments and offer efficient access to any individual record. We have two types of Global index. They are global Non-partitioned index and Global partitioned index. Global nonpartitioned indexes behave just like a nonpartitioned index.
Global partitioned index partition key is independent of Table partition key. The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. If you want to add new partition, always, you need to split the MAX partition. 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.
Example of Global Non-partitioned index.
SQL> CREATE INDEX employees_global_idx ON employees(employee_id);

Index created.

SQL>
Example of Global Partitioned index .
SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
2 GLOBAL PARTITION BY RANGE(employee_id)
3 (PARTITION p1 VALUES LESS THAN(3),
4 PARTITION p2 VALUES LESS THAN(6),
5 PARTITION p3 VALUES LESS THAN(9));
PARTITION p3 VALUES LESS THAN(9))
*
ERROR at line 5:
ORA-14021: MAXVALUE must be specified for all columns

SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
2 GLOBAL PARTITION BY RANGE(employee_id)
3 (PARTITION p1 VALUES LESS THAN(3),
4 PARTITION p2 VALUES LESS THAN(6),
5 PARTITION p3 VALUES LESS THAN(11),
6 PARTITION p4 VALUES LESS THAN(20),
7 PARTITION p5 VALUES LESS THAN(MAXVALUE));

Index created.
Now the partition p4 is empty. Let us drop the empty partition and see the status.
SQL> select count(*) from employees where
2 employee_id between 12 and 20;

COUNT(*)
----------
0
SQL> ALTER index employees_global_part_idx drop partition p4;

Index altered.
SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';

PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P3 USABLE
P5 USABLE
Now we will drop the partition P3 and see status. When we drop this partition, it should invalidate the next highest partition. Here, P5 is next highest partition.
SQL> alter index employees_global_part_idx drop partition p3;

Index altered.

SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';

PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 UNUSABLE
SQL> alter index employees_global_part_idx rebuild;
alter index employees_global_part_idx rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> alter index employees_global_part_idx rebuild partition p5;

Index altered.
SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';

PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 USABLE
Partition index can be maintained by using UPDATE GLOBAL INDEXES. Index will be available during the maintenance and it is available online. We do not need to rebuilt the index after the index maintenance.
For example,
SQL> alter table employees drop partition employees_part3
2 update global indexes;

Table altered.

SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';

PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 USABLE

SQL>
3. When would you force to create Global index on Partition table?

When you create a Primary key, you will be forced to create Global index. When you create unique index, you are forced to create global index. Enforcing unqiueness is most common reason for global indexes. If you try to create local index on unique key, you would get the below error.

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

So, we can create local index on unique key when we add the partition key as part of composit key in the index. Let me demonstrate this.

The below example, unique index failed since, partition key is not part of index composit key.

SQL> create unique index idx_emp_id on employees(employee_id) local;
create unique index idx_emp_id on employees(employee_id) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

The below example, it allowed to create global index.
SQL> create unique index idx_emp_id on employees(employee_id);

Index created.

SQL> drop index idx_emp_id;

Index dropped.

The below case, unique index is successfully created, since partition key department_id is part of composit keys.
SQL> create unique index idx_emp_id on employees(employee_id,department_id) local;

Index created.

SQL>

4. When would you recommend to create global index versus local index?
We can use Global index if Query that return a SMALL number of rows from a potentially LARGE number of partitions.

Source: http://myorastuff.blogspot.com/2008/08/local-index-versus-global-index-on.html