Range partitioning

From Oracle FAQ

Jump to: ,

Range partitioning is a 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 greater than all other values, except MAXVALUE.

[] History

Range partitioning was introduced in .

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

Retrieved from ""

: |

 

 

 CREATE INDEX AUTH_DELIVER_LOG_CTIME_IDX  ON auth_deliver_log (ctime) LOCAL
　　 (PARTITION P201010 TABLESPACE authlog_idex,
　　 PARTITION P201011 TABLESPACE authlog_idex,
　　 PARTITION P201012 TABLESPACE authlog_idex
　　 );