Oracle分区表

作用:将在张大表的数据分布到多个表分区段,不同分区彼此独立,从而提高了表的可用性和性能

种类:范围分区,散列分区(使用HASH算法,最常使用),列表分区,范围/散列组合分区,范围/列表组合分区

范围分区表

创建范围分区表

 
 
create table t(v number,b number)  
partition by range(v) (  
partition p1 values less than ('11') tablespace test1,  
partition p2 values less than ('21') tablespace test2);  

增加与删除分区

#增加分区

 
 
alter table t add partition p3 values less than ('31') tablespace test3;  
alter table t drop partition p3  

一个时间分区的例子

 
 
alter session set nls_data_lanage=AMERICAN;  
alter session set nls_data_format='DD-MON-YYYY' 
create table t(v_date date,b number)  
partition by range(v_date)(  
partition p1 values less than ('01-APR-2009') tablespace test1,  
partition p2 values less than ('01-JUN-2009') tablespace test2);  

2.散列分区表(最常用)

创建

 
 
create table t1(  
v number,b number)  
partition by hash(v)  
(partition p1 tablespace test1,  
partition p2 tablespace test2);  

增加分区

 
 
alter table t add partition p3 tablespace test3;  

删除分区

 
 
alter table t drop coalesce partition;  

3.列表分区

建列表分区

 
 
create table t(  
v varchar2(10),  
b number  
)partition by list(v)  
(partition p1 values('a','b') tablespace test1,  
partition p2 values('c','d') tablespace test2);  

#插入数据

 
 
SQL> insert into t values('a',10);  
SQL> insert into t values('d',20); 

#注意,插入数据时第一个字段只能为a,b,c,d

 
 
SQL> insert into t values('f',30);  
ERROR at line 1:  
ORA-14400: inserted partition key does not map to any partition  

#查询

 
 
select * from t;  
select * from t partition(p1);  
select * from t partition(p2);  
select * from t where v=XXX  

增加分区

 
 
alter table t add partition p3 values('31','32') tablespace test3;  

删除分区

 
 
alter table t drop partition p3  

4.范围/散列组合分区

建立散列组合分区

 
 
create table t(  
v number,b number)  
partition by range(v)  
subpartition by hash(b) subpartitions 2  
store in (test1,test2)(  
partition p1 values less than ('11'),  
partition p2 values less than ('21'));  

查询

 
 
select * from t;  
select * from t partition(p1);  
select * from t where ....  

增加主分区和子分区

 
 
alter table t add partition p3 values less than ('31') tablespace test3;  
alter table t modify partition p3 add subpartition;  

删除分区

 
 
alter table t coalesce partition;  
alter table t modify partition p1 coalesce subpartition;  

5.范围/列表组合分区

创建

 
 
create table t(  
v number,b number)  
partition by range(v)  
subpartition by list(b)  
(partition p1 values less than ('11') tablespace test1(  
subpartition p1_1 values('1','3'),  
subpartition p1_2 values('5','6')  
),  
partition p2 values less than ('21') tablespace test2(  
subpartition p2_1 values('13','14'),  
subpartition p2_2 values('15','16')  
));  

查询

 
 
select * from t  
select * from t partition(p1)  
select * from t subpartition(p1_1)  
select * from t where .....  
select segment_name,partition_name,tablespace_name  
from user_segments where segment_name='T';  

增加分区和子分区

 
 
alter table t add partition p3 values less than ('31') tablespace test3(  
subpartition p3_1 values('25','26'),  
subpartition p3_2 values('22','23'));  
alter table t modify partition r3  
add subpartition r3_3 tablespace test3 values('28','29');  

删除分区

 
 
alter table t modify partition p1 coalesce subpartition;  

其它设置

 
 
交换分区数据  
alter table t exchange partition p1 with table tt;  
载断分区  
alter table t truncate partition p1;  
修改分区名  
alter table t rename partition p2_1 to p2;  
合并分区  
alter table t merge partitions p1,p2 into partition p01  
重组分区  
alter table t move partition p1 tablespace test04  
为列表分区和子分区加值  
alter table t modify partition p1 add values('111');  
alter table t modify subpartition p3_1 add values('111');  
从列表分区和子分区中删除值  
alter table t modify partition p1 drop values('111')  
alter table t modify subpartition p3_1 drop values('111')  

分区表常用的数据字典

 
 
分区表信息:  dba_part_tables  
显示分区:    dba_tab_partitions  
显示子分区:  dba_tab_subpartitions  
显示分区列:  dba_part_key_columns  
显示子分区列:dba_subpart_dey_columns  
显示分区索引:dba_part_indexes  
显示索引分区:dba_ind_partitions