oracle的分区表

1. 范围分区
create table dept
(deptno number(12),
deptname varchar2(30))
partition by range(deptno)
(
partition d1 values less than (10) tablespace dept1,
partition d2 values less than (20) tablespace dept2,
partition d3 values less than (maxvalue) tablespace dept3
)

2. 多列范围分区
create table cust_sales
(acc_no number(5),
deptname char(30),
item_id number(9),
sale_day integer not null,
sale_mth integer not null,
sale_yr  integer not null
)
partition by range(sale_yr,sale_mth,sale_day)
(
partition cust_sales_q1 values less than (2011,04,01) tablespace users,
partition cust_sales_q2 values less than (2011,07,01) tablespace users2,
partition cust_sales_q3 values less than (2011,10,01) tablespace users,
partition cust_sales_q4 values less than (2012,01,01) tablespace users2,
partition cust_sales_qx values less than (maxvalue,maxvalue,maxvalue) tablespace users2
);

3. 散列分区
create table cust_sales_hash
(
acct_no number(5),
cust_name char(30),
sale_day integer not null,
sale_mth integer not null,
sale_yr ?integer not null
)
partition by hash(acct_no)
partitions 4
store in (users1,users2,users3,users4);

4. 组合分区
create table orders
(
ordid number,
acct_no number(5),
cust_name char(30),
orderdate date,
productid number
)
partition by range(orderdate)
    subpartition by hash(productid) subpartitions 8
(
partition q1 values less than (to_date('01-APR-2011','dd-mon-yyyy'))  ,
partition q2 values less than (to_date('01-JUL-2011','dd-mon-yyyy'))  ,
partition q3 values less than (to_date('01-OCT-2011','dd-mon-yyyy'))  ,
partition q4 values less than (maxvalue)  
);

5. 列表分区
create table dept_part
(
dept_no number,
dname   varchar2(14),
loc     varchar2(13)
)
partition by list(dname)
(
partition d1_east values  ('BOSTION','NEW YORK')  ,
partition d2_east values  ('SAN FRANCISCO','log angeles')  ,
partition d3_east values  ('ATLANTA','DALLAS') ,
partition d4_east values  ('CHICAGO','detroit')  
);