Friday, October 18, 2013

How to create an interval range partitioned table based on a function

CREATE TABLE SCOTT.MY_TABLE(
  PID                      NUMBER(12),
  PTDBAL                   NUMBER,
  BUSINESSDAY              VARCHAR2(255 BYTE),
  PTBALCCY                 NUMBER,
  CREATIONTIME             TIMESTAMP(6),
  COCODE                   VARCHAR2(4 BYTE),
  OPERATOR                 VARCHAR2(255 BYTE),
  VERSION                  TIMESTAMP(6),
  MAID                     NUMBER(12),
  LPER                     NUMBER(12),
  ARCHIVALFLAG             CHAR(1 BYTE),
  CREATIONDATE AS
  (
    CAST(CREATIONTIME as DATE)
  ) VIRTUAL
)
-- Partition by range on the virtual column
PARTITION BY RANGE (CREATIONDATE )
-- Use 11gR1 Interval Partitioning
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION P082013 VALUES LESS THAN (TO_DATE('01.09.2013','DD.MM.YYYY'))
)
TABLESPACE DATA;

No comments:

Post a Comment