alter table move 和alter table shrink兩種重組表方法的對比

IT那活兒發(fā)布于2023-01-11 13:20 / 3456人閱讀

點擊上方“IT那活兒”，關注后了解更多精彩內容?。?/span>

01

move&shrink--空間釋放

shrink移動高水位線的同時，釋放申請的空間；而move不會。

1. 新建兩種表test3、test4，并向表中插入數(shù)據(jù)；

2. 查詢兩張表當前占用的數(shù)據(jù)塊數(shù)量；

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. 兩張分別刪除相同的數(shù)據(jù)量；

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

通過對比結果，兩張的初始數(shù)據(jù)量相同，刪除的數(shù)據(jù)量相同，剩余數(shù)據(jù)量也相同。但在分別執(zhí)行move和shrink后，test3中的BLOCKS 數(shù)量明顯多于test4，而test4中EMPTY_BLOCKS多于test3.說明當前test4的空間釋放更完全。

02

move&shrink--索引

move后表中的索引需要重建；shrink自動維護索引。

1. 新建兩種表分別插入相同的數(shù)據(jù)（兩種表都建有索引）；

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的部分數(shù)據(jù)后進行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已經發(fā)生了改變；

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的索引狀態(tài)，當前已不可用，需要重建；

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的部分數(shù)據(jù)后進行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后查看索引狀態(tài)和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. 分別創(chuàng)建兩個大小相等的表空間；

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. 在兩個表空間分別創(chuàng)建一張表；

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

3. 向表中插入實驗數(shù)據(jù)；

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的大小，刪除部分數(shù)據(jù)后進行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的大小，刪除部分數(shù)據(jù)后進行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. 創(chuàng)建新的表空間，將表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無法實現(xiàn)，只能在當前所在的位置進行操作。

本文原創(chuàng) 來源：IT那活兒微信公眾號（上海新炬王翦團隊）

云服務器 GPU云服務器 altered alter Table使用方法 js重制table數(shù)據(jù)方法

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉載請注明本文地址：http://systransis.cn/yun/129697.html

大廠面試預備篇——《兩萬字MySql基礎總結》??建議收藏

?? 一條獨家專欄 ?? 搞技術，進大廠，聊人生 ?《大廠面試突擊》——面試10多家中大廠的萬字總結 ?《技術專家修煉》——高薪必備，企業(yè)真實場景 ?《leetcode 300題》——每天一道算法題，進大廠必備 ?《糊涂算法》——數(shù)據(jù)結構+算法全面講解 ?《從實戰(zhàn)學python》——python的各種應用 ?《程序人生》——聽一條聊職場，聊人生 ?更多資料點這里天下難事，必作于易；天下大事，...

dreamtecher 2021-09-07 09:59 評論0 收藏0

發(fā)表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

我要關注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1356·2023-01-11 13:20
RAC雙節(jié)點crash回復一例

閱讀 1707·2023-01-11 13:20
ORA-600處理一例

閱讀 1215·2023-01-11 13:20
雙節(jié)點RAC實例2 HANG 故障分析一例

閱讀 1906·2023-01-11 13:20
RAC集群節(jié)點1重啟分析一例

閱讀 4165·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2757·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1402·2023-01-11 13:20
分布式緩存組件故障分析及監(jiān)控優(yōu)化

閱讀 3671·2023-01-11 13:20

成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

alter table move 和alter table shrink兩種重組表方法的對比

01

02

03

本文原創(chuàng) 來源：IT那活兒微信公眾號（上海新炬王翦團隊）

相關文章

大廠面試預備篇——《兩萬字MySql基礎總結》??建議收藏

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

RAC雙節(jié)點crash回復一例

ORA-600處理一例

雙節(jié)點RAC實例2 HANG 故障分析一例

RAC集群節(jié)點1重啟分析一例

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補丁報錯處理一例

分布式緩存組件故障分析及監(jiān)控優(yōu)化

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

alter table move 和alter table shrink兩種重組表方法的對比

01

02

03

本 文 原 創(chuàng) 來 源：IT那活兒微信公眾號（上海新炬王翦團隊）

相關文章

發(fā)表評論

0條評論

男|高級講師

TA的文章

最新活動

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

本文原創(chuàng) 來源：IT那活兒微信公眾號（上海新炬王翦團隊）