淘先锋技术网

首页 1 2 3 4 5 6 7

创建分区表

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;