oracle 分区操作（Oracle分区操作）

oracle 分区操作（Oracle分区操作） oracle 分区操作(Oracle分区操作) Some notes of ORACLE partition table management, 2010-04-09 21:10 partition table management note (limited to the ordinary table, that is, heap table partition management, IOT and CLUSTER TABLE no longer discuss the scope) 1. adding partitions (add partition) The syntax is: alter table XXX add partition... It should be noted that if there is a maxvalue or default partition in the partition, add partition will be wrong, and split should be used Such as: Alter table t_range add partition P5 values less than (50) [tablespace users]; --50 is greater than all values of the previous partition Alter table t_list add partition P5 values (7,8,9) [tablespace users]; --7,8,9 cannot appear in the previous partition Alter table t_hash add partition [p5] [tablespace users]; Add subarea: Alter table XXX modify partition P1 add subpartition... For example, increase RANGE-HASH subarea ALTER TABLE diving MODIFY PARTITION locations_us ADD SUBPARTITION us_locs5 TABLESPACE us1; Range, list increased the partition will not affect the index (including global and local), HASH will let add partition data redistribution, IO, if it will lead to a unusable index data movement without specifying the update indexes option, need to be recompiled. Of course, what we say affects the index in the form of data in the table, no data of course can not affect the index. 2. merging partitions (coalesce partition) Alter table XXX coalesce partion [update indexes]; Alter table XXX modify partition P1 coalesce subpartition; Only for HASH partitions or sub partitions, merging one will reduce one partition (at least to 1), redistribute data, generate IO, and have data moving index failures (if not specified update indexes) 3. delete partitions (drop partition) Alter table XXX drop partition ppp; Delete subarea: Alter table XXX drop subpartition ppp; This function is not supported by hash. Also notice that deleting partitions will delete data in the partition at the same time. Similarly, if the update indexes is not specified, the operation will cause the GLOBAL index to fail, and LOCAL will not, because the corresponding LOCAL index partition is deleted too, and the LOCAL of other partitions will not be affected. 4. exchange partition (exchange partition) Alter table tb1 exchange partition/subpartition P1 with table tb2; It's said that the data dictionary is changed, so it's faster. You can exchange partitions with non partitioned tables, sub partitions are exchanged with non partitioned tables, and composite partitions are exchanged with partitioned tables. For example: Composite partition with partitioned table exchange: CREATE TABLE T1 (I NUMBER, J NUMBER) PARTITION BY HASH (I) (PARTITION P1, PARTITION P2); CREATE TABLE T2 (I NUMBER, J NUMBER) PARTITION BY RANGE (J) SUBPARTITION BY HASH (I) (PARTITION P1 VALUES LESS THAN (10)) SUBPARTITION t2_pls1 SUBPARTITION t2_pls2, PARTITION P2 VALUES LESS THAN (20) SUBPARTITION t2_p2s1 SUBPARTITION t2_p2s2); ALTER TABLE T2 EXCHANGE PARTITION P1 WITH TABLE T1 WITH VALIDATION; If you specify the WITH VALIDATION (default), you will check the exchanged data to see if it fits the rules of the partition, 没有验证会忽略合法检查(比如ID = 12的记录此时可以交换到ID 值小于(10)的分区里)，但如果上有主键或独特的约束的话，指 定没有验证会被忽略。 同样，如果不指定更新指标，全球索引会失效，需要重新编译。 5。合并分区(合并分区) ALTER TABLE XXX合并分区和子分区P1，P2为分区/子tablespace_name P3 [表]; 哈希不适用，因为它有凝聚了嘛。 表分区必须是相邻的。 跟凝聚一样，会产生IO，IO数据量大的话，也是相当大的。 同样可以用更新指标避免索引失效 6。修改列表分区加值 ALTER TABLE XXX修改分区/子P1加值(7,9); 要注意的是，增加的值不能在其他分区列的值值中存在，也不能在默认分区(如果有的话)中有相应值。 不会影响索引 7。修改列表分区下降值 ALTER TABLE XXX修改分区/子P1下降值(8,9); 同样，删除的值不能存在记录。 不会影响索引 8。拆分分区(分割分区) 功能与合并分区相反。通常我们会用来拆分次/默认分区。 范围分区: ALTER TABLE XXX分割分区/子P1在(15)到(分/子p1_new1，分区/子p1_new2); 列表分区: ALTER TABLE XXX分割分区/子P1值(15,16)为(分/子p1_new1，分区/子p1_new2); 原分区中符合新值定义的记录会存入第一个分区，其他存入第二个分区，当然，在新分区后面可以指定属性，比如表空间。 哈希分区不适用。 会产生IO 同样，可用更新指标来避免索引失效 9。截断分区(删除分区) 跟TRUNCATETABLE一样，截断该分区内的数据。 ALTER TABLE XXX删除分区/子P1; 同样，可用更新指标来避免索引失效 10。移动分区(移动分区) ALTER TABLE XXX移动分区/子P1…; 有些功能比如改变分区表空间，修改分区就做不到，此时就可以用移 动分区来做。 使用表语句的移动分区子句: ,重新聚类数据并减少碎片 ,将分区移动到另一个表空间 ,修改创建时间属性 ,使用表格压缩将数据存储在压缩格式中 如: ALTER TABLE部分移动分区depot2 表空间ts094 nologging压缩; (如果指定压缩，只影响未来的存储，而不是现有的数据。) 同样，可用更新指标来避免索引失效 11。重命名分区(重命名分区) ALTER TABLE XXX重命名分区/子P1 p1_new; 跟重命名表差不多。 12。修改分区默认属性(修改默认属性) 修改表属性:ALTER TABLE XXX修改默认属性… 修改分区属性(适用于组合分区):修改表XXX修改默认属性分区P1… 只对以后添加的分区产生影响，适用于所有分区，其中哈希分区只能 修改表空间属性。 如: 修改默认属性表空间用户; 13。修改子分区属性(设置的子模板) ALTER TABLE XXX集的子模板(…); 仅影响以后的子分区，当前的子分区属性不会改变 如: ALTER TABLE XXX集的子模板 (分区表空间tbs_1 P1， 分区表空间tbs_2)P2; 如果要取消掉子分区模板: ALTER TABLE XXX集的子模板();