Oracle普通heap表在线重定义分区表

最新推荐文章于 2024-01-25 08:15:56 发布

挠背小能手

最新推荐文章于 2024-01-25 08:15:56 发布

阅读量227

点赞数

分类专栏： Oracle日常运维文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/m0_37625564/article/details/128803852

版权

Oracle日常运维专栏收录该内容

129 篇文章 16 订阅

订阅专栏

在日常业务运行中，可能业务初期为做好完善的规划，未将部分数据量较大的表设计成分区表，后期数据量越来越大的时候，即使走索引而非全表也会导致查询性能极低，本文介绍如何将普通heap表在线重定义成分区表。

创建测试的heap表

SQL> create table ray(id int,name varchar2(10),constraint pk_ray_id primary key(id));

Table created.

插入数据

insert into ray values(0,'a');
insert into ray values(1,'a');
insert into ray values(10,'a');
insert into ray values(11,'a');
insert into ray values(12,'a');
insert into ray values(13,'a');
insert into ray values(20,'a');
insert into ray values(21,'a');
insert into ray values(30,'a');
insert into ray values(31,'a');
insert into ray values(40,'a');
insert into ray values(41,'a');
insert into ray values(55,'a');
insert into ray values(56,'a');
insert into ray values(57,'a');
insert into ray values(58,'a');
insert into ray values(66,'a');
insert into ray values(67,'a');
insert into ray values(72,'a');
insert into ray values(73,'a');
insert into ray values(74,'a');
insert into ray values(83,'a');
insert into ray values(84,'a');
insert into ray values(85,'a');
insert into ray values(92,'a');
insert into ray values(93,'a');
insert into ray values(94,'a');
insert into ray values(95,'a');
insert into ray values(100,'a');
insert into ray values(101,'a');
insert into ray values(222,'a');
insert into ray values(333,'a');
insert into ray values(444,'a');
insert into ray values(555,'a');
insert into ray values(10000,'a');
commit;

检查表信息

SQL> set linesize 100
SQL> col index_name for a25
SQL> select index_name,index_type,tablespace_name from user_indexes where table_name='RAY';


INDEX_NAME                INDEX_TYPE                  TABLESPACE_NAME
------------------------- --------------------------- ------------------------------
PK_RAY_ID                 NORMAL                      RAY

SQL> SQL> desc ray;
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER(38)
NAME                                                           VARCHAR2(10)
 
SQL> select count(*) from ray;

  COUNT(*)
----------
        35

创建中转表

create table ray_p(id int,name varchar2(10),constraint pk_ray_p_id primary key(id))
partition by range(id)
(
partition ray_p1 values less than (10) tablespace ray,
partition ray_p2 values less than (20) tablespace ray,
partition ray_p3 values less than (30) tablespace ray,
partition ray_p4 values less than (40) tablespace ray,
partition ray_p5 values less than (50) tablespace ray,
partition ray_p6 values less than (60) tablespace ray,
partition ray_p7 values less than (70) tablespace ray,
partition ray_p8 values less than (80) tablespace ray,
partition ray_p9 values less than (90) tablespace ray,
partition ray_p10 values less than (maxvalue) tablespace ray
);

检查中转表信息

SQL> col table_name for a6
SQL> col partition_name for a10
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='RAY_P';

TABLE_ PARTITION_ TABLESPACE_NAME
------ ---------- ------------------------------
RAY_P  RAY_P1     RAY
RAY_P  RAY_P10    RAY
RAY_P  RAY_P2     RAY
RAY_P  RAY_P3     RAY
RAY_P  RAY_P4     RAY
RAY_P  RAY_P5     RAY
RAY_P  RAY_P6     RAY
RAY_P  RAY_P7     RAY
RAY_P  RAY_P8     RAY
RAY_P  RAY_P9     RAY

10 rows selected.

验证heap表是否支持在线重定义

SQL> begin
  2    dbms_redefinition.can_redef_table('RAY','RAY');
  3  end;
/ 
  4  
PL/SQL procedure successfully completed.

在线重定义heap表

SQL> begin
  2    dbms_redefinition.start_redef_table('RAY', 'RAY', 'RAY_P');
  3  end;
/
  4  
PL/SQL procedure successfully completed.

手工注册创建的主键索引

SQL> begin
  2    DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT
  3    ('RAY',
   'RAY',
  4    5     'RAY_P',
   DBMS_REDEFINITION.CONS_INDEX,
   'RAY',
  6    7    8     'PK_RAY_ID',
  9     'PK_RAY_P_ID');
 10  end;
 11  /

PL/SQL procedure successfully completed.

同步数据

SQL> begin
  2    dbms_redefinition.sync_interim_table('RAY','RAY','RAY_P');
  3  end;
/ 
  4  
PL/SQL procedure successfully completed.

完成重定义

SQL> begin
  2     dbms_redefinition.finish_redef_table('RAY','RAY','RAY_P');
  3  end;
/ 
  4  
PL/SQL procedure successfully completed.

验证结果

SQL> col table_name for a6
SQL> col partition_name for a10
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='RAY';

TABLE_ PARTITION_ TABLESPACE_NAME
------ ---------- ------------------------------
RAY    RAY_P1     RAY
RAY    RAY_P10    RAY
RAY    RAY_P2     RAY
RAY    RAY_P3     RAY
RAY    RAY_P4     RAY
RAY    RAY_P5     RAY
RAY    RAY_P6     RAY
RAY    RAY_P7     RAY
RAY    RAY_P8     RAY
RAY    RAY_P9     RAY

10 rows selected.

SQL> select count(*) from ray partition(ray_p1);

  COUNT(*)
----------
         2

SQL> select * from ray partition(ray_p1);

        ID NAME
---------- ----------
         0 a
         1 a

SQL> select * from ray partition(ray_p3);

        ID NAME
---------- ----------
        20 a
        21 a

SQL> select * from ray partition(ray_p8);

        ID NAME
---------- ----------
        72 a
        73 a
        74 a

SQL> select * from ray partition(ray_p10);

        ID NAME
---------- ----------
        92 a
        93 a
        94 a
        95 a
       100 a
       101 a
       222 a
       333 a
       444 a
       555 a
     10000 a

11 rows selected.

删除中转表

SQL> drop table ray_p purge;

Table dropped.

SQL> select * from ray_p;
select * from ray_p
              *
ERROR at line 1:
ORA-00942: table or view does not exist

检查索引约束

SQL> set linesize 100
SQL> col index_name for a25
SQL> select index_name,index_type,tablespace_name from user_indexes where table_name='RAY';

INDEX_NAME                INDEX_TYPE                  TABLESPACE_NAME
------------------------- --------------------------- ------------------------------
PK_RAY_ID                 NORMAL                      RAY

SQL> col table_name for a15
SQL> col owner for a6
SQL> col constraint_name for a20
SQL> col table_name for a15
SQL> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='RAY'; 

OWNER  CONSTRAINT_NAME      C TABLE_NAME
------ -------------------- - ---------------
RAY    PK_RAY_P_ID          P RAY

修改约束名（constraint_name是不会随在线重定义转换的）

SQL> alter table ray rename constraint pk_ray_p_id to pk_ray_id;

Table altered.

SQL> col table_name for a15
SQL> col owner for a6
SQL> col constraint_name for a20
col table_name for a15
select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='RAY'; 
SQL> SQL> 
OWNER  CONSTRAINT_NAME      C TABLE_NAME
------ -------------------- - ---------------
RAY    PK_RAY_ID            P RAY

至此，普通heap表在线重定义成分区表完成