In a global partitioned index, the keys in a particular index partition may refer to rows stored in more than one underlying table partition or subpartition. A global index can be range or hash partitioned, though it can be defined on any type of partitioned table.
A global index is created by specifying the GLOBAL attribute. The database
administrator is responsible for defining the initial partitioning of a global index at creation and for maintaining the partitioning over time. Index partitions can be
merged or split as necessary.
Normally, a global index is not equipartitioned with the underlying table. There is
nothing to prevent an index from being equipartitioned with the underlying table, but
Oracle does not take advantage of the equipartitioning when generating query plans
or executing partition maintenance operations. So an index that is equipartitioned with the underlying table should be created as LOCAL.
A global partitioned index contains a single B-tree with entries for all rows in all
partitions. Each index partition may contain keys that refer to many different
partitions or subpartitions in the table.
The highest partition of a global index must have a partition bound all of whose values are MAXVALUE. This insures that all rows in the underlying table can be represented in the index.
Prefixed and Nonprefixed Global Partitioned Indexes
A global partitioned index is prefixed if it is partitioned on a left prefix of the index columns. See Following Figure for an example. A global partitioned index is nonprefixed if it is not partitioned on a left prefix of the index columns. Oracle does not support global nonprefixed partitioned indexes.
Global prefixed partitioned indexes can be unique or nonunique.Nonpartitioned indexes are treated as global prefixed nonpartitioned indexes.
Management of Global Partitioned Indexes
Global partitioned indexes are harder to manage than local indexes:
■ When the data in an underlying table partition is moved or removed (SPLIT,
MOVE, DROP, or TRUNCATE), all partitions of a global index are affected.
Consequently global indexes do not support partition independence.
■ When an underlying table partition or subpartition is recovered to a point in time,
all corresponding entries in a global index must be recovered to the same point in
time. Because these entries may be scattered across all partitions or subpartitions
of the index, mixed in with entries for other partitions or subpartitions that are not being recovered, there is no way to accomplish this except by re-creating the entire global index.
Summary of Partitioned Index Types
Following summarizes the types of partitioned indexes that Oracle supports. The key
points are:
■ If an index is local, it is equipartitioned with the underlying table. Otherwise, it is
global.
■ A prefixed index is partitioned on a left prefix of the index columns. Otherwise, it is nonprefixed.
Big Data Analytics
Global Partitioned Indexes
9:51 PM
divjeev
