alter table move 和alter table shrink两种重组表方法的对比


01

move&shrink–空间释放

shrink移动高水位线的同时,释放申请的空间;而move不会。1. 新建两种表test3、test4,并向表中插入数据; 2. 查询两张表当前占用的数据块数量;

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST3','TEST4');
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST3 11 88  .0625
TEST4 11 88  .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST3','TEST4');
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80   8
TEST4 80   8

3. 两张分别删除相同的数据量;

4. test3进行move操作,test4进行shrink操作;

SQL> select count(*) from test3;
COUNT(*)
----------
50000
SQL> delete from test3 where rownum<=20000;
20000 rows deleted.
SQL> analyze table test3 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST3','TEST4');
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST3 11 88 .0625
TEST4 11 88 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST3','TEST4');
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80 8
TEST4 80 8
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test3;
USED_BLOCKS
-----------
46
SQL> alter table test3 move;
Table altered.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST3','TEST4');
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST3','TEST4');
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80 8
TEST4 80 8
SQL> analyze table test3 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST3','TEST4');
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST3','TEST4');
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52 4
TEST4 80 8
SQL> delete from test4 where rownum<=20000;
20000 rows deleted.
SQL> analyze table test4 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST3','TEST4');
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST3','TEST4');
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52 4
TEST4 80 8
SQL> alter table test4 enable row movement;
Table altered.
SQL> alter table test4 shrink space;
Table altered.
SQL> analyze table test4 compute statistics;
Table analyzed.

5. 对比move和shrink的结果;

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST3','TEST4');
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 7 56  .0625
TEST3 7 56  .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST3','TEST4');
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52   4
TEST4 46  10

通过对比结果,两张的初始数据量相同,删除的数据量相同,剩余数据量也相同。但在分别执行move和shrink后,test3中的BLOCKS 数量明显多于test4,而test4中EMPTY_BLOCKS多于test3.说明当前test4的空间释放更完全。

02

move&shrink–索引

move后表中的索引需要重建;shrink自动维护索引。1. 新建两种表分别插入相同的数据(两种表都建有索引);

SQL> select rowid,id from test5;
ROWID ID
------------------ ----------
AAAVytAAEAAAC5jAAA 1
AAAVytAAEAAAC5jAAB 2
AAAVytAAEAAAC5jAAC 3
AAAVytAAEAAAC5jAAD 4
AAAVytAAEAAAC5lAAA 1
AAAVytAAEAAAC5lAAB 2
AAAVytAAEAAAC5lAAC 3
AAAVytAAEAAAC5lAAD 4
AAAVytAAEAAAC5lAAE 5
AAAVytAAEAAAC5lAAF 6
AAAVytAAEAAAC5lAAG 7
AAAVytAAEAAAC5lAAH 8
AAAVytAAEAAAC5lAAI 9
AAAVytAAEAAAC5lAAJ 10
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVytAAEAAAC5jAAA 11875
AAAVytAAEAAAC5jAAB 11875
AAAVytAAEAAAC5jAAC 11875
AAAVytAAEAAAC5jAAD 11875
AAAVytAAEAAAC5lAAA 11877
AAAVytAAEAAAC5lAAB 11877
AAAVytAAEAAAC5lAAC 11877
AAAVytAAEAAAC5lAAD 11877
AAAVytAAEAAAC5lAAE 11877
AAAVytAAEAAAC5lAAF 11877
AAAVytAAEAAAC5lAAG 11877
AAAVytAAEAAAC5lAAH 11877
AAAVytAAEAAAC5lAAI 11877
AAAVytAAEAAAC5lAAJ 11877

2. 删除test5的部分数据后进行move操作;

SQL> delete from test5 where id >2;
10 rows deleted.
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVytAAEAAAC5jAAA 11875
AAAVytAAEAAAC5jAAB 11875
AAAVytAAEAAAC5lAAA 11877
AAAVytAAEAAAC5lAAB 11877
SQL> alter table test5 move;
Table altered.

3. 对比move前后rowid,此时rowid已经发生了改变;

SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVywAAEAAAC5zAAA 11891
AAAVywAAEAAAC5zAAB 11891
AAAVywAAEAAAC5zAAC 11891
AAAVywAAEAAAC5zAAD 11891

4. 查看test5的索引状态,当前已不可用,需要重建;

SQL> select index_name,status from user_indexes where index_name='T5';
INDEX_NAME STATUS
------------------------------ --------
T5 UNUSABLE

5. 重建test5的索引,索引恢复可用;

SQL> alter index t5 rebuild;
Index altered.
SQL> select index_name,status from user_indexes where index_name='T5';
INDEX_NAME STATUS
------------------------------ --------
T5 VALID

6. 删除test6的部分数据后进行shrink操作;

SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAC 11867
AAAVysAAEAAAC5bAAD 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867
AAAVysAAEAAAC5bAAG 11867
AAAVysAAEAAAC5bAAH 11867
AAAVysAAEAAAC5bAAI 11867
AAAVysAAEAAAC5bAAJ 11867
AAAVysAAEAAAC5bAAK 11867
AAAVysAAEAAAC5bAAL 11867
AAAVysAAEAAAC5bAAM 11867
AAAVysAAEAAAC5bAAN 11867
SQL> delete from test6 where id >2;
10 rows deleted.
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867

7. shrink后查看索引状态和rowid的变化。

SQL> alter table test6 shrink space;
Table altered.
SQL> select index_name,status from user_indexes where index_name='T6';
INDEX_NAME STATUS
------------------------------ --------
T6 VALID
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867

通过对比实验结果,shrink后表的rowid不变且当前索引依旧可用;但move后rowid产生了变化且索引不可用,需要重建恢复。

03

move&shrink–表空间不足

在表空间不充足时,move操作无法进行(需要跟原表相同的空间大小)。1. 分别创建两个大小相等的表空间;

SQL> create tablespace move datafile '/oracle/files/move.dbf' size 50M autoextend off;
Tablespace created.
SQL> create table test_move (id number) tablespace move;
Table created.
SQL> create tablespace shrink datafile '/oracle/files/shrink.dbf' size 50M autoextend off;
Tablespace created.

2. 在两个表空间分别创建一张表;

SQL> create table test_move (id number) tablespace move;
Table created
SQL> create table test_shrink (id number) tablespace shrink;
Table created.

3. 向表中插入实验数据;

SQL> declare
i number:=1;
begin
for i in 1..500000 loop
insert into scott.test_move (id) values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.

4. 确认表1的大小,删除部分数据后进行move操作;

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TEST_MOVE';
SUM(BYTES)/1024/1024
--------------------
31
SQL> delete from test_move where id < 2000;
9995 rows deleted.

5. 返回结果报错,没有充足的空间;

SQL> alter table test_move move;
alter table test_move move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace MOVE

6. 确认表2的大小,删除部分数据后进行shrink操作;

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TEST_SHRINK';
SUM(BYTES)/1024/1024
--------------------
31
SQL> delete from test_shrink where id < 2000;
9995 rows deleted.
SQL> alter table test_shrink enable row movement;
Table altered.
SQL> alter table test_shrink shrink space;
Table altered.

7. 创建新的表空间,将表1move到新的表空间。

SQL> create tablespace move1 datafile '/oracle/files/move1.dbf' size 60M autoextend off;
Tablespace created.
SQL> alter table test_move move tablespace move1;
Table altered.

通过结果对比得,当所在的表空间剩余空间不足时,move是无法进行的,而shrink可以成功完成。但是可以将表move到其他空间充足的表空间进行重组,删除原来的表再move会开始所在的表空间;而shrink无法实现,只能在当前所在的位置进行操作。

本 文 原 创 来 源:IT那活儿微信公众号(上海新炬王翦团队)

发表评论

登录后才能评论
网站客服
网站客服
申请收录 侵权处理
分享本页
返回顶部