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

IT那活儿发布于2023-01-11 13:20 / 3361人阅读

点击上方“IT那活儿”，关注后了解更多精彩内容！！

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那活儿微信公众号（上海新炬王翦团队）

GPU云服务器云服务器 altered alter Table使用方法 js重制table数据方法

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129697.html

大厂面试预备篇——《两万字MySql基础总结》❤️建议收藏

❤️ 一条独家专栏 ⭐️ 搞技术，进大厂，聊人生 ?《大厂面试突击》——面试10多家中大厂的万字总结 ?《技术专家修炼》——高薪必备，企业真实场景 ?《leetcode 300题》——每天一道算法题，进大厂必备 ?《糊涂算法》——数据结构+算法全面讲解 ?《从实战学python》——python的各种应用 ?《程序人生》——听一条聊职场，聊人生 ?更多资料点这里天下难事，必作于易；天下大事，...

dreamtecher 2021-09-07 09:59 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师

我要关注我要私信

TA的文章

消息中间件故障分析一例

阅读 1249·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1557·2023-01-11 13:20
ORA-600处理一例

阅读 1011·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 1680·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 3971·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2519·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1310·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3486·2023-01-11 13:20

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

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

01

02

03

本文原创来源：IT那活儿微信公众号（上海新炬王翦团队）

相关文章

大厂面试预备篇——《两万字MySql基础总结》❤️建议收藏

发表评论

0条评论

IT那活儿

男|高级讲师

TA的文章

消息中间件故障分析一例

RAC双节点crash回复一例

ORA-600处理一例

双节点RAC实例2 HANG 故障分析一例

RAC集群节点1重启分析一例

CRS启动报错CRS-1656处理分享

oracle 12CR2打补丁报错处理一例

分布式缓存组件故障分析及监控优化

最新活动

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

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

01

02

03

本 文 原 创 来 源：IT那活儿微信公众号（上海新炬王翦团队）

相关文章

发表评论

0条评论

男|高级讲师

TA的文章

最新活动

本文原创来源：IT那活儿微信公众号（上海新炬王翦团队）