Range partitioning

Range partitioning is a partitioning technique where ranges of data is stored separately in different sub-tables.

MAXVALUE is provided as a catch-all for values that exceed all ranges specified. Note that Oracle sorts NULLs greater than all other values, except MAXVALUE.

History

Range partitioning was introduced in Oracle 8.

Examples

Partition on a numeric value range:

CREATE TABLE emp (
   empno NUMBER(4), 
   ename VARCHAR2(30), 
   sal   NUMBER
) 
PARTITION BY RANGE(empno) (
  partition e1 values less than (1000)     tablespace ts1, 
  partition e2 values less than (2000)     tablespace ts2, 
  partition e3 values less than (MAXVALUE) tablespace ts3
); 

Partition on a VARCHAR2 string:

CREATE TABLE emp
( id        NUMBER(5)    PRIMARY KEY,
  name      VARCHAR2(50) NOT NULL,
  phone     VARCHAR2(15),
  email     VARCHAR2(100) )
PARTITION BY RANGE ( name )
     ( PARTITION p1 VALUES LESS THAN ('L')      TABLESPACE ts1,
       PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2 )

Time based range partitioning:

CREATE TABLE t1 (id NUMBER, c1 DATE)
PARTITION BY RANGE (c1)
  (PARTITION t1p1 VALUES LESS THAN (TO_DATE('2007-11-01', 'YYYY-MM-DD')),
   PARTITION t1p2 VALUES LESS THAN (TO_DATE('2007-12-01', 'YYYY-MM-DD')),
   PARTITION t1p3 VALUES LESS THAN (TO_DATE('2008-01-01', 'YYYY-MM-DD')),
   PARTITION t1p4 VALUES LESS THAN (MAXVALUE)
  );