Range partitioning

From Oracle FAQ
Jump to: navigation, search

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

Range partitioning was introduced in Oracle 8.

Examples[edit]

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