几个常用的oracle table partition用法:
1. table partitioning type:
a) range partition
b) hash partition
c) list partition
d) composite partition 包括了range-hash partition, list-hash partition
2. table partitioning syntax: 语法
a) range partition:
create table sales_range
( salesman_id number(5),
salesman_name varchar2(30),
sales_mount number(10),
sales_date date)
compress
partition by range ( sales_date)
(
partition sales_Jan_2000 values less than ( to_date('02/01/2000', 'MM/DD/YYYY') ),
partition sales_Feb_2000 values less than ( to_date('03/01/2000','MM/DD/YYYY')),
partition sales_others values less than ( MAXVALUE)
);
注意: 1) 这里的MAXVALUE是大于或者等于to_date('03/01/2000','MM/DD/YYYY')的;
2) 可以通过user_tab_partitions或者dba_tab_partitions 来查看相关table partition的情况,但是有些数据需要调用dbms_stats.gather_schema_stats来更新,比如num_rows
b) hash partition
create table sales_hash
( salesman_id number(5),
salesman_name varchar2(30),
sales_mount number(10),
sales_date date)
partition by hash (sales_date)partitions 4 ;
注意:1)这里的partitions 数值必须是2的n次幂
2)在user_tab_partitions或者dba_tab_partition里面,high_value是显示为null的。和range partition的显示值不一样
c) list partition
create table sales_list( salesman_id number(5),
salesman_name varchar2(30),
sales_mount number(10),
sales_state varchar2(20),
sales_date date)
partition by list (sales_state)
( partition sales_west values( 'Califonie','Hawaii'),
partition sales_east values('NewYork','Florida')
) ;