Database Reference
In-Depth Information
Local Indexes
Each partition in a local index is associated with exactly one partition of the table. This enables Oracle to
automatically keep the index partitions in sync with the table partitions. Database maintenance operations performed
by the DBA on one partition, like rebuilding of a partition, reorganization of a partition, etc., only affect the partition
where the maintenance is being performed.
Because of the one-to-one mapping between the index partition and the physical data partition, local indexes
can be more efficient for a distributed workload when there is a clear access path based on the partition key. However,
if the partition key is missing in the SQL statement, the optimizer will perform a full table scan across all the partitions.
Global Indexes
Global indexes are opposite to the locally partitioned indexes; a global index is associated with more than one
partition of the table. For non-primary key/non-partitioned key based indexes that are created to optimize query
performance, global indexes will be highly efficient for index lookups. However, if the index is used based on the
partitioned key, it allows for partitioning elimination and only partitions containing the relevant index entry are
accessed. The drawback of using the global index is the difficulty with managing individual partitions. Because a
global index contains data across all partitions, dropping and rebuilding of partitions could be difficult.
Benefits of Partitioning
When multiple users access the data from multiple instances, while there is no guarantee that users will be accessing
data that reside in different partitions, it provides the benefit of affinity of workloads to instances and partitions. This
is more visible when using parallel queries. Parallel queries tend to use direct reads. The optimizer will generate
parallel query execution plans to access data based on the partitions and submit slaves across instances. Efficiency of
queries is improved because Oracle manages to lock information locally on the respective instances, eliminating the
need to share them between clusters and thereby reducing interconnect traffic. Starting with Oracle Database
11g Release 2 (11.2.0.2) for buffered parallel queries, the parallel execution layer creates a partition to slave mapping.
Partitioning provides the following general performance benefits that are common to both a single instance
configuration and a RAC configuration:
•
Partition pruning
•
Partition-wise joins
•
Parallel DML
Partition Pruning
Partition pruning is one of the most effective and intuitive ways in which partitioning improves performance. Based
on the queries optimization plan, it can eliminate one or more unnecessary partitions or sub-partitions from the
queries execution plan, focusing directly on the partition or sub-partition where the data resides.
For example, if the optimizer determines that the selection criteria used for pruning is satisfied by all the rows
in the accessed partition or sub-partition, it removes those criteria from the predicate list (WHERE clause) during
evaluation in order to improve performance. However, there are certain limitations on using certain features; the
optimizer cannot prune partitions if the SQL statement applies a function to the partitioning column. Similarly,
the optimizer cannot use an index if the SQL statement applies a function to the indexed column, unless it is a
function-based index.
Partition pruning occurs when there is an appropriate predicate on the partitioning key of a partitioned table.
For range and list partitioning, partition pruning occurs for both equality/inequality predicates and range scan
conditions. However, in the case of hash partitioning, partition pruning will occur only for equality predicates.
Search WWH ::
Custom Search