Bagi Chandrakasan
Oracle Interval partitions

Oracle Interval Partitions


SQL Script to create interval partitioned tables.

Partition by HOURS. Use NUMTODSINTERVAL to partition by hour. In this case, a new partition is created with 6 hour interval.


CREATE TABLE SALES_ORDER_BY_HOUR
(
  EXTRACT_TM   DATE              NOT NULL,
  CUSTOMER_ID  VARCHAR2(20)      NOT NULL,
  SO_ID        VARCHR2(20)       NOT NULL,
  ORDER_DT     DATE,
  PRC_AMT      NUMBER,
  TOT_QTY      NUMBER(5),
  UPDATE_DT    DATE
)
PARTITION BY RANGE (extract_tm)
INTERVAL(NUMTODSINTERVAL(6, 'HOUR'))
(
   PARTITION SOBH_MIN_PART VALUES LESS THAN
       (TO_DATE('01-07-2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
);

Partition by date range. Use NUMTOYMINTERVAL to partition by month. In this case, a new partition is created with 1 month interval.


CREATE TABLE SALES_ORDER_BY_MONTH
(
  EXTRACT_TM   DATE              NOT NULL,
  CUSTOMER_ID  VARCHAR2(20)      NOT NULL,
  SO_ID        VARCHR2(20)       NOT NULL,
  ORDER_DT     DATE,
  PRC_AMT      NUMBER,
  TOT_QTY      NUMBER(5),
  UPDATE_DT    DATE
)
PARTITION BY RANGE (extract_tm)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION SOBH_MIN_PART VALUES LESS THAN
        (TO_DATE('01-07-2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
);

Partition by number range. Specify the interval number and minimum partition.


CREATE TABLE TEST_RANGE_NUM_PARTS
(
   REC_ID              NUMBER (10),
   COL1                NUMBER,
   COL2                NUMBER,
   UPDT_TS             DATE DEFAULT SYSDATE
)
TABLESPACE USERS
PARTITION BY RANGE
   (REC_ID)
   INTERVAL ( 25000 )
   ( PARTITION TRNP_MIN_PART
       VALUES LESS THAN (25000)
       LOGGING
       TABLESPACE USERS
   )
MONITORING;