Partitioned Tables
--
Range Partitioning Example
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_jan2006 VALUES LESS THAN(TO_DATE( ' 02/01/2006 ' , ' MM/DD/YYYY ' )),
PARTITION sales_feb2006 VALUES LESS THAN(TO_DATE( ' 03/01/2006 ' , ' MM/DD/YYYY ' )),
PARTITION sales_mar2006 VALUES LESS THAN(TO_DATE( ' 04/01/2006 ' , ' MM/DD/YYYY ' )),
PARTITION sales_apr2006 VALUES LESS THAN(TO_DATE( ' 05/01/2006 ' , ' MM/DD/YYYY ' )),
PARTITION sales_may2006 VALUES LESS THAN(TO_DATE( ' 06/01/2006 ' , ' MM/DD/YYYY ' ))
);
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-01-15 ' , ' yyyy-mm-dd ' ));
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-02-15 ' , ' yyyy-mm-dd ' ));
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-03-15 ' , ' yyyy-mm-dd ' ));
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-04-15 ' , ' yyyy-mm-dd ' ));
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-05-15 ' , ' yyyy-mm-dd ' ));
create index idx_sales_range_sales_date on sales_range (sales_date);
ALTER TABLE sales_range DROP PARTITION sales_jan2006 UPDATE GLOBAL INDEXES;
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_jan2006 VALUES LESS THAN(TO_DATE( ' 02/01/2006 ' , ' MM/DD/YYYY ' )),
PARTITION sales_feb2006 VALUES LESS THAN(TO_DATE( ' 03/01/2006 ' , ' MM/DD/YYYY ' )),
PARTITION sales_mar2006 VALUES LESS THAN(TO_DATE( ' 04/01/2006 ' , ' MM/DD/YYYY ' )),
PARTITION sales_apr2006 VALUES LESS THAN(TO_DATE( ' 05/01/2006 ' , ' MM/DD/YYYY ' )),
PARTITION sales_may2006 VALUES LESS THAN(TO_DATE( ' 06/01/2006 ' , ' MM/DD/YYYY ' ))
);
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-01-15 ' , ' yyyy-mm-dd ' ));
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-02-15 ' , ' yyyy-mm-dd ' ));
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-03-15 ' , ' yyyy-mm-dd ' ));
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-04-15 ' , ' yyyy-mm-dd ' ));
insert into sales_range values ( 12345 , ' test ' , 1000 , to_date( ' 2006-05-15 ' , ' yyyy-mm-dd ' ));
create index idx_sales_range_sales_date on sales_range (sales_date);
ALTER TABLE sales_range DROP PARTITION sales_jan2006 UPDATE GLOBAL INDEXES;
When to Partition a Table
Here are some suggestions for when to partition a table:
- Tables greater than 2GB should always be considered for partitioning.
- Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read-only.
参考: ora92_doc/server.920/a96524/c12parti.htm
详细请参考oracle文档
