Composite partitioning

From Oracle FAQ
Jump to: navigation, search

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.

[edit] 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

[edit] 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)
);