Composite partitioning

Composite partitioning is a partitioning technique that combines some of the other partitioning methods. The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method.

Composite partitioning methods

The following composite partitions are available:

• Range-hash partitioning was introduced in Oracle 8i
• Range-list partitioning was introduced in Oracle 9i
• Range-range partitioning was introduced in Oracle 11g
• List-range partitioning was introduced in Oracle 11g
• List-hash partitioning was introduced in Oracle 11g
• List-list partitioning was introduced in Oracle 11g
• Interval-range partitioning was introduced in Oracle 11g
• Interval-list partitioning was introduced in Oracle 11g
• Interval-hash partitioning was introduced in Oracle 11g
• Hash-hash partitioning was introduced in Oracle 11gR2

Example

Here is an example of an range-hash composite partitioning:

CREATE TABLE orders(
 ord#       NUMBER,
 orderdate  DATE,
 prod#      NUMBER,
 quantity   NUMBER)
PARTITION    BY RANGE(orderdate)
SUBPARTITION BY HASH(prod#) SUBPARTITIONS 4 -- STORE IN(ts1, ts2, ts3, ts4)
( PARTITION q1 VALUES LESS THAN (TO_DATE('01-APR-2009', 'DD-MON-YYYY')),
  PARTITION q2 VALUES LESS THAN (TO_DATE('01-JUL-2009', 'DD-MON-YYYY')),
  PARTITION q3 VALUES LESS THAN (TO_DATE('01-OCT-2009', 'DD-MON-YYYY')),
  PARTITION q4 VALUES LESS THAN (MAXVALUE)
);