Oracle Partitioning delivers significant
improvements in the manageability, availability, and query performance
of large tables and indexes. Oracle Partitioning is an option
to Enterprise Edition, and was first introduced with Oracle8.
In every subsequent release, Oracle Partitioning has been significantly
enhanced. This discussion covers one of the new partitioning
features in Oracle9i.
Oracle supports two types of partitioned indexes: local indexes
and global indexes. A local index is "equi-partitioned"
with the base table, so that every table partition has exactly
one corresponding index partition. Index maintenance is very
easy when modifying a base table with local indexes. When a
table partition is dropped, the corresponding index partitions
are dropped. When a table partition is added, corresponding
index partitions are added.
Unlike local indexes, a global index can be partitioned independently
of the base table. Hence there may not be a 1-to-1 correspondence
between global index partitions and the base table partitions.
Index maintenance is more complicated when using global indexes,
since dropping or adding a partition to the table may affect
all partitions of the index. In Oracle8i,
all global indexes of a partitioned table are marked UNUSABLE
after a partition-maintenance operation. These global indexes
then have to be rebuilt, which can be an expensive operation.
Oracle's partitioning capabilities have been enhanced in Oracle9i
with the addition of a feature that enables global indexes to
be maintained during partition maintenance operations. A new
clause, UPDATE GLOBAL INDEXES, can optionally be appended to
the maintenance commands. When this clause is present, Oracle
will automatically and efficiently maintain all global indexes
instead of making them UNUSABLE. For example, when dropping
a partition, the command to take advantage of this new feature
is: ALTER TABLE T1 DROP PARTITION P1 UPDATE GLOBAL INDEXES;
The following operations support this new clause:
DROP PARTITION
ADD PARTITION
ADD SUBPARTITION
EXCHANGE PARTITION
EXCHANGE SUBPARTITION
MOVE PARTITION
MOVE SUBPARTITION
SPLIT PARTITION
MERGE PARTITION
This features has two key benefits:
1. Performance - Rebuilding a global index can be an expensive
operation since it requires a scan of the entire table. Using
this clause can significantly reduce the amount of time required
to maintain global indexes.
2. Availability - When an index in marked UNUSABLE, applications
may become unusable since they depend on indexes to guarantee
performance. When using the UPDATE GLOBAL INDEXES clause, global
indexes are always available. Hence there is no impact on applications
that might be accessing the table.
With Oracle Partitioning, Oracle seeks to provide a complete
set of partitioning capabilities to address any requirement.
This enhanced index maintenance is just another example of how
Oracle continues to make its partitioning capabilities more
robust in every new release.
More
Info
Oracle9i
Database Daily Features
|
 |
Bookmark
