分区表相关
作用:将在张大表的数据分布到多个表分区段,不同分区彼此独立,从而提高了表的可用性和性能
种类:范围分区,散列分区(使用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,否则会报错
#查询
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);
增加主分区和子分区
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 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
种类:范围分区,散列分区(使用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,否则会报错
#查询
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);
增加主分区和子分区
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 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
