按范围分区例子:CREATE TABLE sales (invoice_no NUMBER, ... sale_date DATE NOT NULL ) PARTITION BY RANGE (sale_date) (PARTITION p1 VALUES LESS THAN (TO_DATE('2009-01-01','YYYY-MM-DD') TABLESPACE tbs1, PARTITION p2 VALUES LESS THAN (TO_DATE('2009-02-01','YYYY-MM-DD') TABLESPACE tbs2, PARTITION p3 VALUES LESS THAN (TO_DATE('2009-03-01','YYYY-MM-DD') TABLESPACE tbs3, PARTITION p4 VALUES LESS THAN (TO_DATE('2009-04-01','YYYY-MM-DD') TABLESPACE tbs4 ); 以后再增加分区: ALTER TABLE sales ADD PARTITION p5 VALUES LESS THAN (TO_DATE('2009-05-01','YYYY-MM-DD') TABLESPACE tbs5;
如果数据无限增长,并且增长量比较大的话,还是按月+天分区可能要好一点吧! CREATE TABLE table_name (invoice_no NUMBER, ... date DATE NOT NULL, partid varchar(4)) PARTITION BY RANGE (partid) (PARTITION p1_1 VALUES LESS THAN '0112' TABLESPACE tbs1, PARTITION p1_2 VALUES LESS THAN '0122' TABLESPACE tbs2, PARTITION p1_3 VALUES LESS THAN '0132' ........
(invoice_no NUMBER,
...
sale_date DATE NOT NULL )
PARTITION BY RANGE (sale_date)
(PARTITION p1
VALUES LESS THAN (TO_DATE('2009-01-01','YYYY-MM-DD')
TABLESPACE tbs1,
PARTITION p2
VALUES LESS THAN (TO_DATE('2009-02-01','YYYY-MM-DD')
TABLESPACE tbs2,
PARTITION p3
VALUES LESS THAN (TO_DATE('2009-03-01','YYYY-MM-DD')
TABLESPACE tbs3,
PARTITION p4
VALUES LESS THAN (TO_DATE('2009-04-01','YYYY-MM-DD')
TABLESPACE tbs4 ); 以后再增加分区:
ALTER TABLE sales
ADD PARTITION p5
VALUES LESS THAN (TO_DATE('2009-05-01','YYYY-MM-DD')
TABLESPACE tbs5;
CREATE TABLE table_name
(invoice_no NUMBER,
...
date DATE NOT NULL,
partid varchar(4))
PARTITION BY RANGE (partid)
(PARTITION p1_1
VALUES LESS THAN '0112'
TABLESPACE tbs1,
PARTITION p1_2
VALUES LESS THAN '0122'
TABLESPACE tbs2,
PARTITION p1_3
VALUES LESS THAN '0132'
........