Databases Reference
In-Depth Information
•
Globally partitioned index scans may offer significant query performance benefits.
During database design, your application team decides it's not possible to include
the table partitioning column as part of the table primary key (therefore primary
key index would need to be non-partitioned).
See Table 6-1 for a quick synopsis of the effect table-level partition operations have on the different
types of indexes, whether it be non-partitioned, locally partitioned, or globally partitioned. You'll see
clearly that locally partitioned indexes are the easiest to maintain.
•
Table 6-1. Index Partition Maintenance Comparison on Table-Level Partition Operations
Table-Level
Partition
Non-Partitioned Index
Locally Partitioned Index
Globally Partitioned Index
Operation
Add partition
Index is unaffected.
Index is unaffected.
Index is unaffected.
Split partition
Entire index marked
UNUSABLE.
Index for affected
partitions from split
operation marked
UNUSABLE.
All partitions of index marked
UNUSABLE.
Move partition
Entire index marked
UNUSABLE.
Index for partition being
moved marked
UNUSABLE.
All partitions of index marked
UNUSABLE.
Exchange partition
Entire index marked
UNUSABLE.
Index for partition being
exchanged marked
UNUSABLE.
All partitions of index marked
UNUSABLE.
Merge partition
Entire index marked
UNUSABLE.
Index for affected
partitions from merge
operation marked
UNUSABLE.
All partitions of index marked
UNUSABLE.
Truncate partition
Entire index marked
UNUSABLE.
Index is unaffected.
All partitions of index marked
UNUSABLE.
Drop partition
Entire index marked
UNUSABLE.
Local index partition is
dropped, remaining index
partitions are unaffected.
All partitions of index marked
UNUSABLE.
Making data
Not possible unless
entire table is static
(no DML activity ever
on table).
Can make partition-level
index data read-only via
tablespace isolation.
Conceptually possible to
make partition-level index
data read-only. Practically
speaking, not possible unless
entire table is static.
read-only
