文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

如何理解ADD和DROP分区

2023-06-06 02:32

关注

如何理解ADD和DROP分区,针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

add和drop分区
语法:
ALTER TABLE t_pe_r
      ADD PARTITION p4 VALUES LESS THAN (30 );
ALTER TABLE t_pe_l
      ADD PARTITION p4 VALUES  (30 );
ALTER TABLE t_pe_h
      ADD PARTITION p3;

alter table t_pe_r drop partition p3;

限制:如果范围分区使用maxvalue选项则报错ORA-14074: 分区界限必须调整为高于最后一个分区界限
      如果LIST分区使用了default选项则报错ORA-14323: 在 DEFAULT 分区已存在时无法添加分区
      drop 分区不能使用在HASH分区表中,报错ORA-14255: 未按范围, 组合范围或列表方法对表进行分区

我们这里讨论HASH、list、range 3方式下add partition和drop partition关于local索引,global索引和普通索引的状态。
使用脚本
drop table t_pe_r ;
drop table t_pe_l;
drop table t_pe_h;
CREATE TABLE t_pe_r (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY RANGE(j)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20));
create index t_pe_r_n on t_pe_r(i);
create index t_pe_r_l on t_pe_r(j) local;
create index t_pe_r_g on t_pe_r(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

CREATE TABLE t_pe_l (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY list (j)
        (PARTITION p1 VALUES (10),
         PARTITION p2 VALUES (20));
create index t_pe_l_n on t_pe_l(i);
create index t_pe_l_l on t_pe_l(j) local;
create index t_pe_l_g on t_pe_l(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

CREATE TABLE t_pe_h (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY hash(j)
        (PARTITION p1 ,
         PARTITION p2 );
create index t_pe_h_n on t_pe_h(i);
create index t_pe_h_l on t_pe_h(j) local;
create index t_pe_h_g on t_pe_h(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);
 
insert into t_pe_r
values(2,5,'a','A');
insert into t_pe_r
values(1,15,'b','B');
insert into t_pe_l
values(2,10,'a','A');
insert into t_pe_l
values(1,20,'b','B');
insert into t_pe_h
values(2,5,'a','A');
insert into t_pe_h
values(1,15,'b','B');
insert into t_pe_h
values(3,25,'c','C');

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_h_n';
下面进行添加
ALTER TABLE t_pe_r
      ADD PARTITION p4 VALUES LESS THAN (30 );
ALTER TABLE t_pe_l
      ADD PARTITION p4 VALUES  (30 );
ALTER TABLE t_pe_h
      ADD PARTITION p3;

然后查看索引状态
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_L                       USABLE   P2
T_PE_R_L                       USABLE   P1
T_PE_R_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_G                       USABLE   PG2
T_PE_R_G                       USABLE   PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_R_N                       VALID
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_L                       USABLE   P2
T_PE_L_L                       USABLE   P1
T_PE_L_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_G                       USABLE   PG2
T_PE_L_G                       USABLE   PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_L_N                       VALID
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_H_L                       USABLE   P2
T_PE_H_L                       UNUSABLE P1
T_PE_H_L                       UNUSABLE P3
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_H_G                       UNUSABLE PG2
T_PE_H_G                       UNUSABLE PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_h_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_H_N                       UNUSABLE
可以看到实际上LIST和HASH的所有索引都没有受到影响,而HASH分区则不同,所有的索引均失效,添加分区后通过HASH算法重新分布了行,那么应该ROWID也受到了影响,可以DUMP出来看看。
索引进行rebuild
alter index T_PE_H_L rebuild  partition p1;
在进行HASH分区的加入分区时候最好如下:
ALTER TABLE t_pe_h
      ADD PARTITION p3 update indexes; 加上UPDATE INDEXES,同时实际上HASH的分区个数应该是2的N次方,不然会分布不均匀。
然后我们测试下DROP partition,drop partition只能用于RANGE 和LIST分区方式,HASH分区不能使用:
You can drop partitions from range, list, or composite range-list partitioned tables. For hash-partitioned tables, or hash subpartitions of range-hash partitioned

tables, you must perform. a coalesce operation instead.
使用脚本:
alter table t_pe_r drop partition p1;
alter table t_pe_l drop partition p1;
然后观察:
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_L                       USABLE   P2
T_PE_R_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_G                       UNUSABLE PG2
T_PE_R_G                       UNUSABLE PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_R_N                       UNUSABLE
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_L                       USABLE   P2
T_PE_L_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_G                       UNUSABLE PG2
T_PE_L_G                       UNUSABLE PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_L_N                       UNUSABLE
可以看到普通索引,全局索引均已经失效,但是本地索引却不受影响。
如果我们带上UPDATE INDEXES会怎么样?
SQL> alter table t_pe_r drop partition p1 update indexes;
 
Table altered
SQL> alter table t_pe_l drop partition p1 update indexes;
 
Table altered
 
SQL>
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_L                       USABLE   P2
T_PE_R_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_G                       USABLE   PG2
T_PE_R_G                       USABLE   PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_R_N                       VALID
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_L                       USABLE   P2
T_PE_L_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_G                       USABLE   PG2
T_PE_L_G                       USABLE   PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_L_N                       VALID
可以看到加上UPDATE INDEXES 就会自动重建失效的索引。
结论:
1、如果范围分区使用maxvalue选项则报错ORA-14074: 分区界限必须调整为高于最后一个分区界限
2、如果LIST分区使用了default选项则报错ORA-14323: 在 DEFAULT 分区已存在时无法添加分区
3、drop 分区不能使用在HASH分区表中,报错ORA-14255: 未按范围, 组合范围或列表方法对表进行分区,如果要减少一个HASH分区表中的分区需要用ALTER TABLE ... COALESCE PARTITION
4、HASH分区进行ADD PARTITION操作,普通索引,本地索引,全局索引都会失效,除非使用UPDATE INDEXES,但是LIST、RANGE分区不受影响
5、LIST,RANGE分区进行DROP PARTITION操作全局索引及普通索引会失效,但是LOCAL索引不受影响。除非使用UPDATE INDEXES.
6、如果想要为全局索引增加分区,那这个操作只能对HASH分区的全局有效,ORA-14640: 添加/合并索引分区操作只对散列分区的全局索引有效,但是DROP全局索引的分区对HASH\LIST\RANGE均有效。

关于如何理解ADD和DROP分区问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注编程网行业资讯频道了解更多相关知识。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯