在线重定义表是一个非常好用的功能, 也是一个比较老的话题了.
在Oracle中可以使用DBMS_REDEFINITION包对表进行重定义, 例如把单表重定义成分区表.
但是在线重定义需要耗费多少空间, 又是否需要move rows呢?
我个人认为是需要移动行的, 因为每个分区对应一个segemnt, segment是由extents组成, 每个extents又是由连续的block组成.
因此单表在拆分成分区后, segment势必要变成多个, 而在原始表中一个block里面可能包含了属于多个分区的记录, 那么势必会需要涉及move rows.
以上是我的个人分析, 那实际上是怎么样的呢?
先来看一个例子 :
这个例子错开插入不同大小的ID记录, 确保分区SEGMENT不能连续的拆分BLOCK。
oracle@db1-> sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 13 18:57:51 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options// 连接到test库
SQL> conn test/test// 创建测试表
SQL> create table test.tbl_redefine_test(id int primary key,info varchar2(64) default 'Hello, I_am_digoal.zhou.', crt_time date default sysdate) tablespace kefu;
Table created.查看索引, 将索引改成nologging的, 提高插入的性能
SQL> select index_name from user_indexes where table_name='TBL_REDEFINE_TEST';
INDEX_NAME
------------------------------
SYS_C0028986
SQL> alter index SYS_C0028986 nologging;
Index altered.// 错开ID的范围插入将近20W条记录.
insert into tbl_redefine_test nologging (id) select rownum from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+200000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+500000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+400000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+100000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+900000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+700000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+300000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1000000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+600000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+800000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1200000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1500000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1400000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1100000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1900000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1700000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1300000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+2000000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1600000 from dual connect by level < 100000;
insert into tbl_redefine_test nologging (id) select rownum+1800000 from dual connect by level < 100000;
commit;// 登陆到超级用户准备在线重定义表, 拆成分区表
SQL> conn / as sysdba
Connected.
SQL> set timing on// 首先查看原始表的起始BLOCK ID以及占用的空间大小53531 , 109051904
SQL> set pagesize 50000 linesize 190
SQL> column owner format a16
SQL> column SEGMENT_NAME format a32
SQL> select owner,segment_name,partition_name,header_block,bytes,extents,relative_fno from dba_segments where segment_name='TBL_REDEFINE_TEST';
OWNER SEGMENT_NAME PARTITION_NAME HEADER_BLOCK BYTES EXTENTS RELATIVE_FNO
---------------- -------------------------------- ------------------------------ ------------ ---------- ---------- ------------
TEST TBL_REDEFINE_TEST 53531 109051904 84 5// 使用DBMS_REDEFINITION.CAN_REDEF_TABLE检查原始表是否满足在线重定义条件
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TBL_REDEFINE_TEST',
3 DBMS_REDEFINITION.CONS_USE_PK);
4 END;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11// 创建中间表(分区表)
SQL> CREATE TABLE test.part_test
(id int primary key,info varchar2(64) default 'Hello, I_am_digoal.zhou.', crt_time date default sysdate)
3 PARTITION BY RANGE(id)
4 (PARTITION p1 VALUES LESS THAN (300000) TABLESPACE kefu,
5 PARTITION p2 VALUES LESS THAN (600000) TABLESPACE kefu,
6 PARTITION p3 VALUES LESS THAN (900000) TABLESPACE kefu,
7 PARTITION p4 VALUES LESS THAN (1200000) TABLESPACE kefu,
8 PARTITION p5 VALUES LESS THAN (1500000) TABLESPACE kefu,
9 PARTITION p6 VALUES LESS THAN (1800000) TABLESPACE kefu,
10 PARTITION p7 VALUES LESS THAN (2100000) TABLESPACE kefu
11 );
Table created.
Elapsed: 00:00:00.06// 在线重定义, 从这次操作的时间上可以看出, 这里应该发生了大量数据的拷贝.
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('TEST', 'TBL_REDEFINE_TEST','PART_TEST',
3 'id id, info info, crt_time crt_time',
4 dbms_redefinition.cons_use_pk);
5 END;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.20//克隆对象 权限, 约束, 触发器等.
//This procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. This procedure does not clone the already registered dependent objects.
This subprogram is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table (which represents the post-redefinition table).SQL> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TEST', 'TBL_REDEFINE_TEST','PART_TEST',
5 DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
6 END;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.49// 几个报错, 需要忽略, 因为克隆过程会用到重名的约束. 并通过下面的SQL查询是否有其他错误.
// Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint, and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.
SQL> select object_name, base_table_name, ddl_txt from
2 DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
SYS_C0028986 TBL_REDEFINE_TEST
CREATE UNIQUE INDEX "TEST"."TMP$$_SYS_C00289860" ON "TEST"."PART_TEST" ("ID")
SYS_C0028986 TBL_REDEFINE_TEST
ALTER TABLE "TEST"."PART_TEST" ADD CONSTRAINT "TMP$$_SYS_C00289860" PRIMARY KEY
Elapsed: 00:00:00.02// 同步中间表数据和原始表的数据
SQL> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'TBL_REDEFINE_TEST', 'PART_TEST');
3 END;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02// 这一步前, 可以对中间表进行添加索引等操作, 在完成这个操作后, 原始表将被重定义成中间表的形态, 因此原始表在此步骤会短暂的被锁. This procedure completes the redefinition process. Before this step, you can create new indexes, triggers, grants, and constraints on the interim table. The referential constraints involving the interim table must be disabled. After completing this step, the original table is redefined with the attributes and data of the interim table. The original table is locked briefly during this procedure
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST', 'TBL_REDEFINE_TEST', 'PART_TEST');
3 END;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46// 完成后查看, 无重定义错误
SQL> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
no rows selected
Elapsed: 00:00:00.00// 结束后查看原始表的起始BLOCK ID以及占用的空间大小. (显然已经是7个分区了)
// 显然已经变成分区表了, 并且他们的BLOCKID都发生了变化, 已经不是原来的53531. 而是57755开始的BLOCKID, 跳过了4224个block.
SQL> set pagesize 50000 linesize 190
SQL> column owner format a16
SQL> column SEGMENT_NAME format a32
SQL> select owner,segment_name,partition_name,header_block,bytes,extents,relative_fno from dba_segments where segment_name='TBL_REDEFINE_TEST';
OWNER SEGMENT_NAME PARTITION_NAME HEADER_BLOCK BYTES EXTENTS RELATIVE_FNO
---------------- -------------------------------- ------------------------------ ------------ ---------- ---------- ------------
TEST TBL_REDEFINE_TEST P1 57755 15728640 30 5
TEST TBL_REDEFINE_TEST P2 57763 15728640 30 5
TEST TBL_REDEFINE_TEST P3 57771 15728640 30 5
TEST TBL_REDEFINE_TEST P4 57779 15728640 30 5
TEST TBL_REDEFINE_TEST P5 57787 15728640 30 5
TEST TBL_REDEFINE_TEST P6 57795 15728640 30 5
TEST TBL_REDEFINE_TEST P7 57803 15728640 30 5
7 rows selected.
Elapsed: 00:00:00.01再看另一个例子, ID是连续插入的, 如果ORACLE足够智能, 应该可以讲segment里面包含的blocks简单的SPLIT开来. 只需要move少量的rows.
但是结果还是同上一个例子一样, move了所有的行.
SQL> create table test.tbl_redefine_test(id int primary key,info varchar2(64) default 'Hello, I_am_digoal.zhou.', crt_time date default sysdate) tablespace kefu;
Table created.
Elapsed: 00:00:00.02
SQL> select index_name from user_indexes where table_name='TBL_REDEFINE_TEST';
INDEX_NAME
------------------------------
SYS_C0028988
Elapsed: 00:00:00.00
SQL> alter index SYS_C0028988 nologging;
Index altered.
Elapsed: 00:00:00.01
SQL> insert into tbl_redefine_test nologging (id) select rownum from dual connect by level < 2000000;
1999999 rows created.
Elapsed: 00:00:08.27
SQL> commit;
Commit complete.
Elapsed: 00:00:00.03
SQL> conn /as sysdba
Connected.
SQL> set timing on
SQL> set pagesize 50000 linesize 190
SQL> column owner format a16
SQL> column SEGMENT_NAME format a32
SQL> select owner,segment_name,partition_name,header_block,bytes,extents,relative_fno from dba_segments where segment_name='TBL_REDEFINE_TEST';
OWNER SEGMENT_NAME PARTITION_NAME HEADER_BLOCK BYTES EXTENTS RELATIVE_FNO
---------------- -------------------------------- ------------------------------ ------------ ---------- ---------- ------------
TEST TBL_REDEFINE_TEST 86811 100663296 83 5
Elapsed: 00:00:00.01
SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TBL_REDEFINE_TEST',
3 DBMS_REDEFINITION.CONS_USE_PK);
4 END;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> CREATE TABLE test.part_test
2 (id int primary key,info varchar2(64) default 'Hello, I_am_digoal.zhou.', crt_time date default sysdate)
3 PARTITION BY RANGE(id)
4 (PARTITION p1 VALUES LESS THAN (300000) TABLESPACE kefu,
5 PARTITION p2 VALUES LESS THAN (600000) TABLESPACE kefu,
6 PARTITION p3 VALUES LESS THAN (900000) TABLESPACE kefu,
7 PARTITION p4 VALUES LESS THAN (1200000) TABLESPACE kefu,
8 PARTITION p5 VALUES LESS THAN (1500000) TABLESPACE kefu,
9 PARTITION p6 VALUES LESS THAN (1800000) TABLESPACE kefu,
10 PARTITION p7 VALUES LESS THAN (2100000) TABLESPACE kefu
11 );
Table created.
Elapsed: 00:00:00.03
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('TEST', 'TBL_REDEFINE_TEST','PART_TEST',
3 'id id, info info, crt_time crt_time',
4 dbms_redefinition.cons_use_pk);
5 END;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.08
SQL> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TEST', 'TBL_REDEFINE_TEST','PART_TEST',
5 DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
6 END;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
SQL> select object_name, base_table_name, ddl_txt from
2 DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_C0028988 TBL_REDEFINE_TEST CREATE UNIQUE INDEX "TEST"."TMP$$_SYS_C00289880" ON "TEST"."PART_TEST" ("ID")
SYS_C0028988 TBL_REDEFINE_TEST ALTER TABLE "TEST"."PART_TEST" ADD CONSTRAINT "TMP$$_SYS_C00289880" PRIMARY KE
Y
Elapsed: 00:00:00.00
SQL> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'TBL_REDEFINE_TEST', 'PART_TEST');
3 END;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST', 'TBL_REDEFINE_TEST', 'PART_TEST');
3 END;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
SQL> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
no rows selected
Elapsed: 00:00:00.00
SQL> select owner,segment_name,partition_name,header_block,bytes,extents,relative_fno from dba_segments where segment_name='TBL_REDEFINE_TEST';
OWNER SEGMENT_NAME PARTITION_NAME HEADER_BLOCK BYTES EXTENTS RELATIVE_FNO
---------------- -------------------------------- ------------------------------ ------------ ---------- ---------- ------------
TEST TBL_REDEFINE_TEST P1 91035 15728640 30 5
TEST TBL_REDEFINE_TEST P2 91043 15728640 30 5
TEST TBL_REDEFINE_TEST P3 91051 15728640 30 5
TEST TBL_REDEFINE_TEST P4 91059 15728640 30 5
TEST TBL_REDEFINE_TEST P5 91067 15728640 30 5
TEST TBL_REDEFINE_TEST P6 91075 15728640 30 5
TEST TBL_REDEFINE_TEST P7 91083 10485760 25 5
7 rows selected.
Elapsed: 00:00:00.01
SQL> select owner,segment_name,partition_name,header_block,bytes,extents,relative_fno from dba_segments where segment_name='PART_TEST';
OWNER SEGMENT_NAME PARTITION_NAME HEADER_BLOCK BYTES EXTENTS RELATIVE_FNO
---------------- -------------------------------- ------------------------------ ------------ ---------- ---------- ------------
TEST PART_TEST 86811 100663296 83 5
Elapsed: 00:00:00.08时间： 2024-09-16 17:25:18