分区表 分区索引 增加 收缩 删除 表分区
来源：互联网 发布：java软件开发工作描述 编辑：程序博客网 时间：2024/05/22 11:49
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 alteredSQL> 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 40SQL> 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 createdSQL> 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_SPACEIDX_RANGE_ID R_P2 20 USABLE KARL_SPACEIDX_RANGE_ID R_P3 30 USABLE KARL_SPACEIDX_RANGE_ID R_P4 40 USABLE KARL_SPACEIDX_RANGE_ID R_P5 MAXVALUE USABLE KARL_SPACE SQL> alter table t_partition_range drop partition R_P5 ; Table alteredSQL> 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_SPACEIDX_RANGE_ID R_P2 20 USABLE KARL_SPACEIDX_RANGE_ID R_P3 30 USABLE KARL_SPACEIDX_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 PART01T_PART_HASH T_HASH_P2 PART02T_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 PART01T_PART_HASH T_HASH_P2 PART02T_PART_HASH T_HASH_P3 PART03T_PART_HASH T_HASH_P4 KARL_SPACE 注意，收缩的只是分区，并不会影响到数据，但是视被收缩分区中数据的多少，收缩表分区也会涉及到IO操作。另外如果你在执行该语句时没有指定update indexes子句，收缩过程中有数据改动的分区其local索引和glocal索引都会失效，需要重新编译。//检验一下INDEX失效的情况SQL> drop index idx_part_hash_id ; Index droppedSQL> create index idx_part_hash_id on t_part_hash (id ) local; Index createdSQL> 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 PART01IDX_PART_HASH_ID T_HASH_P2 USABLE PART02IDX_PART_HASH_ID T_HASH_P3 USABLE PART03IDX_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 alteredSQL> 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 PART01T_PART_HASH T_HASH_P2 PART02T_PART_HASH T_HASH_P3 PART03SQL> 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 PART01IDX_PART_HASH_ID T_HASH_P2 UNUSABLE PART02 //发现UNUSABLEIDX_PART_HASH_ID T_HASH_P3 USABLE PART03SQL> alter index IDX_PART_HASH_ID rebuild partition t_hash_p2; Index alteredSQL> 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 PART01IDX_PART_HASH_ID T_HASH_P2 USABLE PART02 //现在状态OKIDX_PART_HASH_ID T_HASH_P3 USABLE PART033、删除表分区(drop partition)删除表分区包含两种操作，分别是：删除分区：alter table [tbname] drop partition [ptname];删除子分区：alter table [tbname] drop subpartition [ptname];除hash分区和hash子分区外，其它的分区格式都可以支持这项操作。例如，删除分区：SQL> select partition_name, table_name ,subpartition_count 2 from user_tab_partitions 3 where table_name ='T_PART_LIST'; PARTITION_NAME TABLE_NAME SUBPARTITION_COUNT------------------------------ ------------------------------ ------------------T_LIST_P1 T_PART_LIST 0T_LIST_P2 T_PART_LIST 0T_LIST_P3 T_PART_LIST 0SQL> select * from t_part_list; ID NAME----------- ---------- 1 asdfad 2 asfda 3 3333 3 afasdf SQL> select * from t_part_list partition(t_list_p3); ID NAME----------- ---------- 3 3333 3 afasdf SQL> alter table t_part_list drop partition t_list_p3; Table altered SQL> select * from t_part_list; ID NAME----------- ---------- 1 asdfad 2 asfda select * from t_part_list partition(t_list_p3) //肯定不在！在就有鬼了！ ORA-02149: 指定的分区不存在注意：1.由于是ddl操作，这种删除也会是非常迅速的，因此如果你确认某个分区的数据都要被删除，使用drop partition会比delete更加高效。如果你的本意是希望删除掉指定的分区但保留数据，你应该使用merge partition，后面也会讲到。2.同样，如果你在执行该语句时没有指定update indexes子句，也会导致glocal索引的失效，至于local索引嘛，删除分区时对应的索引分区会被同时删除，但其它分区的local索引不会受到影响。
