 |
|
11g Composite Partitioning Enhancements
Oracle 11g New Features Tips by Burleson
Consulting
June 27, 2008 |
Oracle 11g SQL New Features Tips
With each subsequent release of the Oracle
database, additional composite partitioning, or subpartitioning,
options have been made available. Partitioning data with finer
granularity provides the database with substantial advantages in
manageability and performance. Composite partitioning involves
the partitioning of data using a primary criterion and then
subpartitioning each partition based on a secondary criterion.
In Oracle 10g, the available composite partitioning choices were
range-hash and range-list. Oracle 11g introduces a number of
new composite partitioning strategies including: range-range,
list-list, list-hash, and list-range. These partitioning
strategies are also available with interval partitioning and the use
of virtual columns in the partitioning key.
|
|
Range |
List |
Hash |
|
Range |
11g |
10g |
10g |
|
Interval |
11g |
11g |
11g |
|
List |
11g |
11g |
11g |
Table 1:
Composite Partitioning Options
As the chart above exhibits, in Oracle 11g there
are many more composite partitioning strategies available allowing
data to be divided to its finest possible granularity.
Enhanced Partition Pruning Capabilities
Partition pruning occurs when a SQL statement
involving a partitioned object is executed and the database
recognizes that the selection criteria are only specific partitions.
This allows the database to access only the relevant partitions and
ignore all partitions that are not necessary for the SQL statement.
In other words, partition pruning is the act of eliminating, or
ignoring, partitions that are irrelevant to the SQL statement’s
selection criteria.
Beginning with Oracle 11g, partition pruning uses bloom filtering
instead of subquery pruning. A bloom filter essentially tests
if an element is a member of a set or not. In deciding whether
to use partition pruning, a bloom filter uses partition pruning
whenever a partitioned object is detected in the SQL statement.
This enhances the performance of partition pruning because bloom
filtering does not require additional resources. Also, it is not
evaluated as a cost based decision. Instead, bloom filtering
is constantly active as well as automatically activated for any join
with a partitioned object. This transparent enhancement
improves the performance of partition pruning.
 |
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off. |
