---- Simple Range Partition On ARCHIVE_DATA table: ----
CREATE TABLE ARCHIVE_DATA
(
id NUMBER (10) NOT NULL
, form_type VARCHAR2 (16) NOT NULL
, class_name VARCHAR2 (64) NOT NULL
, view_technology VARCHAR2 (30) DEFAULT 'freemarker'
, archive_time TIMESTAMP(6)
, changed_by_login_id NUMBER (10)
, individual_id NUMBER (10)
, form_id VARCHAR2 (30)
, prov_id NUMBER (10)
, clob_data CLOB
, form_name VARCHAR2 (150)
)
PARTITION BY RANGE (archive_time)
(
PARTITION OD_TILL_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00'),
PARTITION OD_200701_200712 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00'),
PARTITION OD_200801_200812 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00'),
PARTITION OD_200901_200912 VALUES LESS THAN (TIMESTAMP'2010-01-01 00:00:00') ,
PARTITION OD_201001_201003 VALUES LESS THAN (TIMESTAMP'2010-04-01 00:00:00') ,
PARTITION OD_201004_201006 VALUES LESS THAN (TIMESTAMP'2010-07-01 00:00:00') ,
PARTITION OD_201007_201009 VALUES LESS THAN (TIMESTAMP'2010-10-01 00:00:00') ,
PARTITION OD_201010_201012 VALUES LESS THAN (TIMESTAMP'2011-01-01 00:00:00') ,
PARTITION OD_AFTER_2010 VALUES LESS THAN (MAXVALUE)
);
---- Simple Range Partition On SALES_RANGE table: ----
CREATE TABLE sales_range
(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE
)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);
---- Simple LIST Partition On SALES_LIST table: ----
CREATE TABLE sales_list
(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE
)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);
---- Composite Partitioning Range-List Example: ----
CREATE TABLE bimonthly_regional_sales
(
deptno NUMBER,
item_no VARCHAR2(20),
txn_date DATE,
txn_amount NUMBER,
state VARCHAR2(2)
)
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE
(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3
)
(
PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')),
PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),
PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')
)
);
