Database Reference
In-Depth Information
In the case of a globally partitioned index, note that the number of index partitions may be different from the
number of table partitions.
Since global indexes may be partitioned by range or hash only, you must use local indexes if you wish to have a
list or composite partitioned index. The local index will be partitioned using the same scheme as the underlying table.
Local Indexes vs. Global Indexes
In my experience, most partition implementations in data warehouse systems use local indexes. In an OLTP
system, global indexes are much more common, and we'll see why shortly. It has to do with the need to perform
partition elimination on the index structures to maintain the same query response times after partitioning as before
partitioning them.
■
Over the last couple of years, it has become more common to see local indexes used in OLtP systems, as such
systems have rapidly grown in size.
Note
Local indexes have certain properties that make them the best choice for most data warehouse implementations.
They support a more available environment (less downtime), since problems will be isolated to one range or hash
of data. On the other hand, since it can point to many table partitions, a global index may become a point of failure,
rendering all partitions inaccessible to certain queries.
Local indexes are more flexible when it comes to partition maintenance operations. If the DBA decides to move
a table partition, only the associated local index partition needs to be rebuilt or maintained. With a global index, all
index partitions must be rebuilt or maintained in real time. The same is true with sliding window implementations,
where old data is aged out of the partition and new data is aged in. No local indexes will be in need of a rebuild, but
all global indexes will be either rebuilt or maintained during the partition operation. In some cases, Oracle can take
advantage of the fact that the index is locally partitioned with the table and will develop optimized query plans based
on that. With global indexes, there is no such relationship between the index and table partitions.
Local indexes also facilitate a partition point-in-time recovery operation. If a single partition needs to be
recovered to an earlier point in time than the rest of the table for some reason, all locally partitioned indexes can be
recovered to that same point in time. All global indexes would need to be rebuilt on this object. This does not mean
“avoid global indexes”—in fact, they are vitally important for performance reasons, as you'll learn shortly—you just
need to be aware of the implications of using them.
Local Indexes
Oracle makes a distinction between the following two types of local indexes:
•
Local prefixed indexes
: These are indexes whereby the partition keys are on the leading
edge of the index definition. For example, if a table is range partitioned on a column named
LOAD_DATE
, a local prefixed index on that table would have
LOAD_DATE
as the first column in its
column list.
•
Local
nonprefixed indexes
: These indexes do
not
have the partition key on the leading edge of
their column list. The index may or may not contain the partition key columns.
Both types of indexes are able to take advantage of partition elimination, both can support uniqueness (as long
as the nonprefixed index includes the partition key), and so on. The fact is that a query that uses a local prefixed index
will always
allow
for index partition elimination, whereas a query that uses a local nonprefixed index might not. This
is why local nonprefixed indexes are said to be slower by some people—they do not
enforce
partition elimination
(but they do support it).
