Partitioned Tables

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;

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文档

water ye 2006-05-12 16:37 发表评论