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