Oracle 表分区

表分区

 

1. 范围分区

create table student (

id integer not null,

name varchar2(20),

score integer

)

partition by range(score) (

partition p1 values less than(60),

partition p2 values less than(75),

partition p3 values less than(85),

partition p4 values less than(maxvalue)

);

 

--------------------------------------------------------------------------------------------------

 

2.散列分区

 

create table dept (

deptNo int,

deptName varchar2(14)

)

partition by hash(deptNo) (

partition p1,

partition p2

);

 

--------------------------------------------------------------------------------------------------

 

3. 复合分区

 

create table sal_grade (

grade number,

losal number,

hisal number

);

 

Subpartition by hash (losal, hisal ) (

partition p1 values less than(10) (

subpartition sp1,

subpartition sp2

),

partition p2 values less than (20) (

subpartition sp3,

subpartition sp4

)

)

 

--------------------------------------------------------------------------------------------------

 

4.列表分区

 

create table customer (

id int,

name varchar(20),

state varchar(20)

)

partition by list(state) (

partition asia values ('中国','韩国','新加坡'),

partition europe values ('英国,'法国','德国'),

partition ameria values ('美国','加拿大','墨西哥')

)

 

--------------------------------------------------------------------------------------------------

向表中插入数据时sql 没什么变化

 

查询数据时，可单独查询某个分区中的数据，如：

select * from student partition(p1)  查询分区p1里的数据

 

--------------------------------------------------------------------------------------------------

 

表分区维护

 

添加分区：

alter table student add partition p5 values less than(120)

 

删除分区:

alter table student drop partition p4;

 

截短分区：

alter table student truncate partitioin p5;

 

合并分区：

alter table student merge partitions p3,p4 into partition p6; 

 

--------------------------------------------------------------------------------------------------

涉及表分区的数据字典

user_tab_partitions 和user_ind_partitions  ( ind ->index )