Oracle Database Tips by Donald BurlesonMay 14, 2015
from: http://www.dba-oracle.com/t_global_local_partitioned_index.htm
Oracle Global Index vs. Local Index
Question: What is the difference between a oracle global index and a local index?
Answer: 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 says 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”.
-
Local Index: A local index is a one-to-one mapping between a 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.
Global and Local Index partitioning with Oracle
The first partitioned index method is called a LOCAL partition. A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.
The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.
Local partitioned indexes
Local partitioned indexes allow the DBA to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table.
In a local partitioned index, the key values and number of index partitions will match the number of partitions in the base table.
CREATE INDEX year_idx
on all_fact (order_date)
LOCAL
(PARTITION name_idx1),
(PARTITION name_idx2),
(PARTITION name_idx3);
Oracle will automatically use equal partitioning of the index based upon the number of partitions in the indexed table. For example, in the above definition, if we created four indexes on all_fact, the CREATE INDEX would fail since the partitions do not match. This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.
Global partitioned indexes
A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes. In the global index partition scheme, the index is harder to maintain since the index may span partitions in the base table.
For example, when a table partition is dropped as part of a reorganization, the entire global index will be affected. When defining a global partitioned index, the DBA has complete freedom to specify as many partitions for the index as desired.
Now that we understand the concept, let’s examine the Oracle CREATE INDEX syntax for a globally partitioned index:
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));
Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning.
Local Index和表分区一一对应,若修改了基表分区，则oracle自动维护对应的索引分区。
Global Index和表分区不存在一一对应，要删除父表的一个分区必须更新Global Index ,否则索引信息不正确，语法为：
ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes
唯一索引必须是global的。
如果为了满足查询速度的话，那就看查询是单个分区查询还是整张表的查询，单分区 local 比较好，全表 global 比较好。