Wednesday, February 22, 2017

How to create a range partitioned table



Using interval partitioning:

A simple example of an Interval Range Partitioned table:
CREATE TABLE DOCUMENTS
(
  DOCNAME          VARCHAR2(100 BYTE),
  TRACKING#        VARCHAR2(32 BYTE),
  ESTABLISHED_DT   DATE
)
PARTITION BY RANGE (ESTABLISHED_DT)
INTERVAL
(
   NUMTOYMINTERVAL(1,'MONTH')
)
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('2014-03','YYYY-MM') )
)
TABLESPACE USERS;

If you use INTERVAL partitioning, you are not allowed to specify an overflow partition.

Without internval partitioning:

Before Oracle 11g, this was the only way to create a RANGE partitioned table:
You had to manually define your partitions, and remember to manually add new ones if you expected new partition key values to be inserted.

Here I am using a column of type NUMBER to implement range partitioning.
I am defining an overflow partition to catch values outside the range of expected values:

CREATE TABLE mytable
(
  ID                      NUMBER(18)           NOT NULL,
  f_name                  NUMBER(18)           NOT NULL,
  l_name                  NUMBER(18)           NOT NULL,
  created_year            NUMBER(4)            NOT NULL
   ....
)
PARTITION BY RANGE (created_year)
(
PARTITION P_2003 values less than (2004) TABLESPACE 2003_DATA,
PARTITION P_2004 values less than (2005) TABLESPACE 2004_DATA,
PARTITION P_2005 values less than (2006) TABLESPACE 2005_DATA,
PARTITION P_DEFAULT values less than (MAXVALUE)
);

No comments:

Post a Comment