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.
For complete details,
see my tips for
Oracle partitioning.
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.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
