Range partitioning

From Oracle FAQ

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 emp1, 
  partition e2 values less than (2000)     tablespace emp2, 
  partition e3 values less than (MAXVALUE) tablespace emp3
); 

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)
  );