创建分区表

1.间隔分区

 partition by range(logtime)  ----以某一列分区

 interval (numtodsinterval(1,'day'))  ---间隔

 store in (TS_BASE_PARTITION_DATA) ---存储表空间

 (

 partition P20130101 values less than (to_date('20130101','yyyymmdd') )

 )

2.引用分区

create table orders (

order# number primary key,

order_date date,

data varchar2(30)

)

enable row movement

partition by range(order_date)

(

          partition P20130801 values less than(to_date('20130801','yyyymmdd') ),

          partition P20130802 values less than(to_date('20130802','yyyymmdd') ),

          partition P20130803 values less than(to_date('20130803','yyyymmdd') ),

          partition P20130804 values less than(to_date('20130804','yyyymmdd') ),

          partition P20130805 values less than(to_date('20130805','yyyymmdd') )

);

create table order_line_item

(

order# number,

line# number,

order_date date,

data varchar2(30),

constraint c1_pk primary key (order#,line#),---主键连接

constraint c1_fk_p foreign key(order#) references orders ---外键引用

)

enable row movement

partition by range(order_date)

(

          partition P20130801 values less than(to_date('20130801','yyyymmdd') ),

          partition P20130802 values less than(to_date('20130802','yyyymmdd') ),

          partition P20130803 values less than(to_date('20130803','yyyymmdd') ),

          partition P20130804 values less than(to_date('20130804','yyyymmdd') ),

          partition P20130805 values less than(to_date('20130805','yyyymmdd') )

)

3.列表分区

partition  by list(column_name)

(

partition partition_name values('ME','NH'),

partition partition_name values('CT','MN')

)

4.散列分区

partition by hash(column_name)

(

partition p1 tablespace p1,

partition p2 tablespace p2

)

oracle 建议N是2的一个幂（2,4,6,8）从而得到最佳的总体分布。

5.区间分区

 

partition by range(logtime)  ----以某一列分区

(

 partition P20130101 values less than (to_date('20130101','yyyymmdd') )

 )

区间分区与分隔区间的却别就是分区不会自增长 分隔区间可以自增区间。

6.组合分区

partition by range(column_name)

subpartition by list(column_name) ----添加子分区标致

(

partition p1 values less than (to_date(logtime,'yyyymmdd'))

(subpartition p1_sub_p1 values(1,2,3,4),

          subpartition p1_sub_p2 values(5,6,7,8)), ---子分区包含在分区内

partition p2 values less than (to_date(logtime,'yyyymmdd'))

(subpartition p2_sub_p1 values(1,2,3,4),

          subpartition p2_sub_p2 values(5,6,7,8))---子分区包含在分区内

)

7.行移动

alter table table_name enable row movement;