分区表及分区索引(8)--增加和收缩表分区
来源:互联网 发布:男生办公鼠标推荐 知乎 编辑:程序博客网 时间:2024/05/17 04:16
1、增加表分区(add partition)
增加表分区适应于所有的分区形式,其语法是alter table tbname add partition .....
但是,需要注意对于像list,range这种存在范围值的分区,所要增加的分区值必须要大于当前分区中的最大值
(如果当前存在maxvalue或default的分区,add partition会报错,这种情况只能使用split,后面会讲到),hash分区则无此限制。
例如:
SQL> create table t_partition_range (id number ,name varchar2(10))
2 partition by range(id )(
3 partition r_p1 values less than (10),
4 partition r_p2 values less than (20),
5 partition r_p3 values less than (30))
6 ;
Table created
SQL> alter table t_partition_range add partition r_p4 values less than (40);
Table altered
SQL> select * from user_tab_partitions
2 where table_name ='T_PARTITION_RANGE';
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
------------------------------ --------- ------------------------------ ------------------ --------------------
T_PARTITION_RANGE NO R_P1 0 10
T_PARTITION_RANGE NO R_P2 0 20
T_PARTITION_RANGE NO R_P3 0 30
T_PARTITION_RANGE NO R_P4 0 40
SQL> alter table t_partition_range add partition r_p5 values less than (maxvalue);
Table altered
SQL> select * from user_tab_partitions
2 where table_name = 'T_PARTITION_RANGE';
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
------------------------------ --------- ------------------------------ ------------------ -------------
T_PARTITION_RANGE NO R_P1 0 10
T_PARTITION_RANGE NO R_P2 0 20
T_PARTITION_RANGE NO R_P3 0 30
T_PARTITION_RANGE NO R_P4 0 40
T_PARTITION_RANGE NO R_P5 0 MAXVALUE
//测试删除分区,LOCAL索引会不会失效。
Hash和list的语法与上类似,这里不再举例。
注意:
1、对于hash分区,当你执行add partition操作的时候,oracle会自动选择一个分区,
并重新分配部分记录到新建的分区,这也意味着有可能带来一些IO操作。
2、执行alter table时未指定update indexes子句:
如果是range/list分区,其local索引和global索引不会受影响;
如果是hash分区,新加分区及有数据移动的分区的local索引和global索引会被置为unuseable,需要重新编译。
3、复合分区完全适用上述所述规则。
SQL> create index idx_range_id on t_partition_range (id) local;
Index created
SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_RANGE_ID';
SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_RANGE_ID';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
------------------------------ ------------------------------ --------------------- -------- ------------------------------
IDX_RANGE_ID R_P1 10 USABLE KARL_SPACE
IDX_RANGE_ID R_P2 20 USABLE KARL_SPACE
IDX_RANGE_ID R_P3 30 USABLE KARL_SPACE
IDX_RANGE_ID R_P4 40 USABLE KARL_SPACE
IDX_RANGE_ID R_P5 MAXVALUE USABLE KARL_SPACE
SQL> alter table t_partition_range drop partition R_P5 ;
Table altered
SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_RANGE_ID';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
------------------------------ ------------------------------ ----------------------- -------- ------------------------------
IDX_RANGE_ID R_P1 10 USABLE KARL_SPACE
IDX_RANGE_ID R_P2 20 USABLE KARL_SPACE
IDX_RANGE_ID R_P3 30 USABLE KARL_SPACE
IDX_RANGE_ID R_P4 40 USABLE KARL_SPACE
//STATUS 全部为 USABLE,果然是有效的。 继续..
2、收缩表分区(coalesce partitions)
Coalesce partition是个很有意思的分区功能,仅能被应用于hash分区或复合分区的hash子分区,执行之后,
会自动收缩当前的表分区,比如某表当前有5个hash分区,执行alter table tbname coalesce partitions后就变成4个,
再执行一次就变成3个,再执行一次就变2个,再执行一次就...........就报错了:)
,对于已分区的表至少要有一个分区存在的嘛!
例如:
SQL> select table_name,partition_name,tablespace_name
2 from user_tab_partitions
3 where table_name = 'T_PART_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_PART_HASH T_HASH_P1 PART01
T_PART_HASH T_HASH_P2 PART02
T_PART_HASH T_HASH_P3 PART03
SQL> alter table T_PART_HASH add partition t_hash_p4 ;
Table altered
SQL> select table_name,partition_name,tablespace_name
2 from user_tab_partitions
3 where table_name = 'T_PART_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_PART_HASH T_HASH_P1 PART01
T_PART_HASH T_HASH_P2 PART02
T_PART_HASH T_HASH_P3 PART03
T_PART_HASH T_HASH_P4 KARL_SPACE
注意,收缩的只是分区,并不会影响到数据,但是视被收缩分区中数据的多少,收缩表分区也会涉及到IO操作。
另外如果你在执行该语句时没有指定update indexes子句,收缩过程中有数据改动的分区其local索引和glocal索引都会失效,需要重新编译。
//检验一下INDEX失效的情况
SQL> drop index idx_part_hash_id ;
Index dropped
SQL> create index idx_part_hash_id on t_part_hash (id ) local;
Index created
SQL> select index_name ,partition_name ,status ,tablespace_name from
2 user_ind_partitions
3 where index_name = 'IDX_PART_HASH_ID';
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
IDX_PART_HASH_ID T_HASH_P1 USABLE PART01
IDX_PART_HASH_ID T_HASH_P2 USABLE PART02
IDX_PART_HASH_ID T_HASH_P3 USABLE PART03
IDX_PART_HASH_ID T_HASH_P4 USABLE KARL_SPACE
//因为刚才添加的分区 t_hash_p4 没有指定TABLEspace_name,SO..t_hash_p4被默认添加到用户的表空间里。
//而此时建的是local index 。则一样在karl_space表空间里。
//并注意此时INDEX 的status 是 USABLE
//下面执行收缩表分区操作。
SQL> alter table t_part_hash coalesce partition;
Table altered
SQL> select table_name,partition_name,tablespace_name
2 from user_tab_partitions
3 where table_name = 'T_PART_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_PART_HASH T_HASH_P1 PART01
T_PART_HASH T_HASH_P2 PART02
T_PART_HASH T_HASH_P3 PART03
SQL> select index_name ,partition_name ,status ,tablespace_name from
2 user_ind_partitions
3 where index_name = 'IDX_PART_HASH_ID';
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
IDX_PART_HASH_ID T_HASH_P1 USABLE PART01
IDX_PART_HASH_ID T_HASH_P2 UNUSABLE PART02 //发现UNUSABLE
IDX_PART_HASH_ID T_HASH_P3 USABLE PART03
SQL> alter index IDX_PART_HASH_ID rebuild partition t_hash_p2;
Index altered
SQL> select index_name ,partition_name ,status ,tablespace_name from
2 user_ind_partitions
3 where index_name = 'IDX_PART_HASH_ID';
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
IDX_PART_HASH_ID T_HASH_P1 USABLE PART01
IDX_PART_HASH_ID T_HASH_P2 USABLE PART02 //现在状态OK
IDX_PART_HASH_ID T_HASH_P3 USABLE PART03
增加表分区适应于所有的分区形式,其语法是alter table tbname add partition .....
但是,需要注意对于像list,range这种存在范围值的分区,所要增加的分区值必须要大于当前分区中的最大值
(如果当前存在maxvalue或default的分区,add partition会报错,这种情况只能使用split,后面会讲到),hash分区则无此限制。
例如:
SQL> create table t_partition_range (id number ,name varchar2(10))
2 partition by range(id )(
3 partition r_p1 values less than (10),
4 partition r_p2 values less than (20),
5 partition r_p3 values less than (30))
6 ;
Table created
SQL> alter table t_partition_range add partition r_p4 values less than (40);
Table altered
SQL> select * from user_tab_partitions
2 where table_name ='T_PARTITION_RANGE';
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
------------------------------ --------- ------------------------------ ------------------ --------------------
T_PARTITION_RANGE NO R_P1 0 10
T_PARTITION_RANGE NO R_P2 0 20
T_PARTITION_RANGE NO R_P3 0 30
T_PARTITION_RANGE NO R_P4 0 40
SQL> alter table t_partition_range add partition r_p5 values less than (maxvalue);
Table altered
SQL> select * from user_tab_partitions
2 where table_name = 'T_PARTITION_RANGE';
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
------------------------------ --------- ------------------------------ ------------------ -------------
T_PARTITION_RANGE NO R_P1 0 10
T_PARTITION_RANGE NO R_P2 0 20
T_PARTITION_RANGE NO R_P3 0 30
T_PARTITION_RANGE NO R_P4 0 40
T_PARTITION_RANGE NO R_P5 0 MAXVALUE
//测试删除分区,LOCAL索引会不会失效。
Hash和list的语法与上类似,这里不再举例。
注意:
1、对于hash分区,当你执行add partition操作的时候,oracle会自动选择一个分区,
并重新分配部分记录到新建的分区,这也意味着有可能带来一些IO操作。
2、执行alter table时未指定update indexes子句:
如果是range/list分区,其local索引和global索引不会受影响;
如果是hash分区,新加分区及有数据移动的分区的local索引和global索引会被置为unuseable,需要重新编译。
3、复合分区完全适用上述所述规则。
SQL> create index idx_range_id on t_partition_range (id) local;
Index created
SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_RANGE_ID';
SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_RANGE_ID';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
------------------------------ ------------------------------ --------------------- -------- ------------------------------
IDX_RANGE_ID R_P1 10 USABLE KARL_SPACE
IDX_RANGE_ID R_P2 20 USABLE KARL_SPACE
IDX_RANGE_ID R_P3 30 USABLE KARL_SPACE
IDX_RANGE_ID R_P4 40 USABLE KARL_SPACE
IDX_RANGE_ID R_P5 MAXVALUE USABLE KARL_SPACE
SQL> alter table t_partition_range drop partition R_P5 ;
Table altered
SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_RANGE_ID';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
------------------------------ ------------------------------ ----------------------- -------- ------------------------------
IDX_RANGE_ID R_P1 10 USABLE KARL_SPACE
IDX_RANGE_ID R_P2 20 USABLE KARL_SPACE
IDX_RANGE_ID R_P3 30 USABLE KARL_SPACE
IDX_RANGE_ID R_P4 40 USABLE KARL_SPACE
//STATUS 全部为 USABLE,果然是有效的。 继续..
2、收缩表分区(coalesce partitions)
Coalesce partition是个很有意思的分区功能,仅能被应用于hash分区或复合分区的hash子分区,执行之后,
会自动收缩当前的表分区,比如某表当前有5个hash分区,执行alter table tbname coalesce partitions后就变成4个,
再执行一次就变成3个,再执行一次就变2个,再执行一次就...........就报错了:)
,对于已分区的表至少要有一个分区存在的嘛!
例如:
SQL> select table_name,partition_name,tablespace_name
2 from user_tab_partitions
3 where table_name = 'T_PART_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_PART_HASH T_HASH_P1 PART01
T_PART_HASH T_HASH_P2 PART02
T_PART_HASH T_HASH_P3 PART03
SQL> alter table T_PART_HASH add partition t_hash_p4 ;
Table altered
SQL> select table_name,partition_name,tablespace_name
2 from user_tab_partitions
3 where table_name = 'T_PART_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_PART_HASH T_HASH_P1 PART01
T_PART_HASH T_HASH_P2 PART02
T_PART_HASH T_HASH_P3 PART03
T_PART_HASH T_HASH_P4 KARL_SPACE
注意,收缩的只是分区,并不会影响到数据,但是视被收缩分区中数据的多少,收缩表分区也会涉及到IO操作。
另外如果你在执行该语句时没有指定update indexes子句,收缩过程中有数据改动的分区其local索引和glocal索引都会失效,需要重新编译。
//检验一下INDEX失效的情况
SQL> drop index idx_part_hash_id ;
Index dropped
SQL> create index idx_part_hash_id on t_part_hash (id ) local;
Index created
SQL> select index_name ,partition_name ,status ,tablespace_name from
2 user_ind_partitions
3 where index_name = 'IDX_PART_HASH_ID';
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
IDX_PART_HASH_ID T_HASH_P1 USABLE PART01
IDX_PART_HASH_ID T_HASH_P2 USABLE PART02
IDX_PART_HASH_ID T_HASH_P3 USABLE PART03
IDX_PART_HASH_ID T_HASH_P4 USABLE KARL_SPACE
//因为刚才添加的分区 t_hash_p4 没有指定TABLEspace_name,SO..t_hash_p4被默认添加到用户的表空间里。
//而此时建的是local index 。则一样在karl_space表空间里。
//并注意此时INDEX 的status 是 USABLE
//下面执行收缩表分区操作。
SQL> alter table t_part_hash coalesce partition;
Table altered
SQL> select table_name,partition_name,tablespace_name
2 from user_tab_partitions
3 where table_name = 'T_PART_HASH';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_PART_HASH T_HASH_P1 PART01
T_PART_HASH T_HASH_P2 PART02
T_PART_HASH T_HASH_P3 PART03
SQL> select index_name ,partition_name ,status ,tablespace_name from
2 user_ind_partitions
3 where index_name = 'IDX_PART_HASH_ID';
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
IDX_PART_HASH_ID T_HASH_P1 USABLE PART01
IDX_PART_HASH_ID T_HASH_P2 UNUSABLE PART02 //发现UNUSABLE
IDX_PART_HASH_ID T_HASH_P3 USABLE PART03
SQL> alter index IDX_PART_HASH_ID rebuild partition t_hash_p2;
Index altered
SQL> select index_name ,partition_name ,status ,tablespace_name from
2 user_ind_partitions
3 where index_name = 'IDX_PART_HASH_ID';
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
IDX_PART_HASH_ID T_HASH_P1 USABLE PART01
IDX_PART_HASH_ID T_HASH_P2 USABLE PART02 //现在状态OK
IDX_PART_HASH_ID T_HASH_P3 USABLE PART03
- 分区表及分区索引(8)--增加和收缩表分区
- 深入学习分区表及分区索引(8)--增加和收缩表分区
- 分区表 分区索引 增加 收缩 删除 表分区
- 分区表及分区索引(16)--增加和删除索引分区
- 分区表和分区索引
- 分区表和分区索引
- 分区表和分区索引
- 增加、删除和收缩表分区!
- 深入学习分区表及分区索引(9)--删除表分区
- 分区表及分区索引(9)--删除表分区
- 分区表及分区索引(11)--合并表分区
- 分区表及分区索引(12)--修改list表分区
- 分区表及分区索引(13)--分隔表分区
- 分区表及分区索引(10)--交换分区
- 分区表及分区索引(1)
- Oracle分区表及分区索引
- Oracle分区表及分区索引
- 分区表及分区索引(15)--修改表分区属性和模板
- 自增、自减运算
- WCF编程]WCF使用Net.tcp绑定时候出现错误:元数据包含无法解析的引用
- 生活就是一本书2011.10
- Android SO Inject
- android 图标设计
- 分区表及分区索引(8)--增加和收缩表分区
- web工程 编译 @Override 错误
- 将datatable写入XML文件
- netstat命令的一些说明
- 分区表及分区索引(9)--删除表分区
- linux 下遍历文件夹下的所有文件
- 引用 linux ioctl函数
- MYSQL、SQL Server、Oracle数据库排序空值null问题及其解决办法
- 分区表及分区索引(10)--交换分区