Question: How does composite
partitioning work in Oracle? When would I choose
composite partitioning over regular partitioning?
Answer:
Composite
partitioning partitions the data rows using the range
method, and within each partition, Oracle subpartitions the
data using many methods.
Oracle experts note that composite partitioning supports
historical operations, such as adding new range partitions,
but also provides higher degrees of parallelism for DML
operations and finer granularity of data placement through
sub-partitioning.
Range-Hash composite partition:
(as
of Oracle 8i)This composite partitioning uses
range partitioning first, then spreads data into a
hash partition.
Possible usage: range partition by date of birth then hash
partition by name; store the results into the hash
partitions. Composite range-hash partitioning
provides the improved manageability of range partitioning
and the data placement, striping, and parallelism advantages
of hash partitioning.
Range-List composite partitioning:
(as
of Oracle9i) This composite partitioning uses
range partitioning
first, then spreads data into
list partitioning.
Possible usage: range partition by date of birth then list
partition by state, then store the results into the list
partitions. Composite range-list partitioning
provides the manageability of range partitioning and the
explicit control of list partitioning for the subpartitions.
Starting in Oracle11g we see these
additional composite partitioning methods:
Range-Range
composite partitioning:
(as of 11g) This composite partitioning uses
range partitioning first, and then spreads the
data again based on range.
List-Range
composite partitioning:
(as of 11g)
This composite partitioning uses list partition first, and
then spreads the data again based on
range partitioning .
List-Hash
composite partitioning:
(as of 11g)
This composite partitioning uses
list partitioning
first, and then spreads the data again based on hash.
List-List
composite partitioning:
(as of 11g)
This composite partitioning uses
list partitioning
first, and then spreads the data again based on
list partitioning.
Interval-Hash
composite partitioning:
Interval partitioning is an enhancement to
range partitioning in Oracle 11g and interval partitioning
automatically creates
time-based partitions as new data is added.
This composite partitioning uses
interval partitioning first, and then spreads the
data again based on a hash
partition.
Interval-List
composite partitioning:
(as of 11g)
This composite partitioning uses
interval partitioning first, and then spreads the data
again based on a sub list.
Interval-Range
composite partitioning:
(as of 11g)
This composite partitioning uses
interval partitioning first, and then spreads the data
again based on range.
Also see my notes on
partition pruning to improve response time.
Please see
related partitioning pages:
Justifying Oracle partitioning
Oracle Partitioning
Oracle interval partitioning tips
Oracle Partition Key Statistics & tuning
