CREATE TABLE sales ( invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL ) STORAGE (INITIAL 100K NEXT 50K) LOGGING PARTITION BY RANGE ( sale_year, sale_month, sale_day) ( PARTITION sales_q1 VALUES LESS THAN ( 1999, 04, 01 ) TABLESPACE BASEDATA STORAGE (INITIAL 20K NEXT 10K), PARTITION sales_q2 VALUES LESS THAN ( 1999, 07, 01 ) TABLESPACE BASEDATA, PARTITION sales_q3 VALUES LESS THAN ( 1999, 10, 01 ) TABLESPACE BASEDATA, PARTITION sales_q4 VALUES LESS THAN ( 2000, 01, 01 ) TABLESPACE BASEDATA) ENABLE ROW MOVEMENT;CREATE INDEX month_ix ON sales(sale_month) GLOBAL PARTITION BY RANGE(sale_month) (PARTITION pm1_ix VALUES LESS THAN (2), PARTITION pm2_ix VALUES LESS THAN (3), PARTITION pm3_ix VALUES LESS THAN (4), PARTITION pm4_ix VALUES LESS THAN (5), PARTITION pm5_ix VALUES LESS THAN (6), PARTITION pm6_ix VALUES LESS THAN (7), PARTITION pm7_ix VALUES LESS THAN (8), PARTITION pm8_ix VALUES LESS THAN (9), PARTITION pm9_ix VALUES LESS THAN (10), PARTITION pm10_ix VALUES LESS THAN (11), PARTITION pm11_ix VALUES LESS THAN (12), PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));CREATE INDEX local_year_ix ON sales(sale_year) LOCAL;
樓上的建表方法很好,但是分區時是按月份分區的,不符合樓主的要求: 每个月的1号放到一个分区表中。2号放到另一个分区表中。 ...... 30号放到另一个分区表中因此不應該使用范圍分區,而應該使用列表分區的方式 CREATE TABLE sales ( invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL ) STORAGE (INITIAL 100K NEXT 50K) LOGGING PARTITION BY LIST (sale_day) ( PARTITION sales_q1 VALUES(1) TABLESPACE BASEDATA STORAGE (INITIAL 20K NEXT 10K), PARTITION sales_q2 VALUES(2) TABLESPACE BASEDATA, PARTITION sales_q3 VALUES(3) TABLESPACE BASEDATA, PARTITION sales_q4 VALUES(4) TABLESPACE BASEDATA), ...... PARTITION sales_q4 VALUES(31) TABLESPACE BASEDATA )ENABLE ROW MOVEMENT;
( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
STORAGE (INITIAL 100K NEXT 50K) LOGGING
PARTITION BY RANGE ( sale_year, sale_month, sale_day)
( PARTITION sales_q1 VALUES LESS THAN ( 1999, 04, 01 )
TABLESPACE BASEDATA STORAGE (INITIAL 20K NEXT 10K),
PARTITION sales_q2 VALUES LESS THAN ( 1999, 07, 01 )
TABLESPACE BASEDATA,
PARTITION sales_q3 VALUES LESS THAN ( 1999, 10, 01 )
TABLESPACE BASEDATA,
PARTITION sales_q4 VALUES LESS THAN ( 2000, 01, 01 )
TABLESPACE BASEDATA)
ENABLE ROW MOVEMENT;CREATE INDEX month_ix ON sales(sale_month)
GLOBAL PARTITION BY RANGE(sale_month)
(PARTITION pm1_ix VALUES LESS THAN (2),
PARTITION pm2_ix VALUES LESS THAN (3),
PARTITION pm3_ix VALUES LESS THAN (4),
PARTITION pm4_ix VALUES LESS THAN (5),
PARTITION pm5_ix VALUES LESS THAN (6),
PARTITION pm6_ix VALUES LESS THAN (7),
PARTITION pm7_ix VALUES LESS THAN (8),
PARTITION pm8_ix VALUES LESS THAN (9),
PARTITION pm9_ix VALUES LESS THAN (10),
PARTITION pm10_ix VALUES LESS THAN (11),
PARTITION pm11_ix VALUES LESS THAN (12),
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));CREATE INDEX local_year_ix ON sales(sale_year) LOCAL;
每个月的1号放到一个分区表中。2号放到另一个分区表中。 ...... 30号放到另一个分区表中因此不應該使用范圍分區,而應該使用列表分區的方式
CREATE TABLE sales
( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
STORAGE (INITIAL 100K NEXT 50K) LOGGING
PARTITION BY LIST (sale_day)
(
PARTITION sales_q1 VALUES(1)
TABLESPACE BASEDATA STORAGE (INITIAL 20K NEXT 10K),
PARTITION sales_q2 VALUES(2)
TABLESPACE BASEDATA,
PARTITION sales_q3 VALUES(3)
TABLESPACE BASEDATA,
PARTITION sales_q4 VALUES(4)
TABLESPACE BASEDATA),
......
PARTITION sales_q4 VALUES(31)
TABLESPACE BASEDATA
)ENABLE ROW MOVEMENT;
最近非常忙,没有准时回来收贴。sorry!