共計(jì) 7836 個(gè)字符，預(yù)計(jì)需要花費(fèi) 20 分鐘才能閱讀完成。
這篇文章將為大家詳細(xì)講解有關(guān) alter table move 與 shrink space 的區(qū)別是什么，文章內(nèi)容質(zhì)量較高，因此丸趣 TV 小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。
一、move
案例：
同事將一關(guān)鍵表中刪了多余的 300w 條數(shù)據(jù)后，程序就變的異常緩慢。分析得出，應(yīng)該是表空間碎片過多，舊的索引效率過低。
執(zhí)行下面兩句話：
alter table ycsbt_qyygxx_jb move;
alter index R_SBXX_YCSBD_FK rebuild online;
效果非常明顯。
deltete 不會(huì)釋放表空間，但是可以重用，也就是插入可以填補(bǔ)空洞，當(dāng)然現(xiàn)實(shí)應(yīng)用中確實(shí)是存在經(jīng)常刪除很少插入的情況，這樣就存在了釋放表空間優(yōu)化數(shù)據(jù)庫(kù)的可行性了，truncate 有不能帶條件的缺陷，自然就想到用 alter table move 重移表空間的方法。這里要注意三個(gè)要素
1、 alter table move 省略了 tablespace XXX, 表示用戶移到自己默認(rèn)的表空間，因此當(dāng)前表空間至少要是該表兩倍大，這很好理解，由于易錯(cuò)所以提出，就不再細(xì)說了。
2、 alter table move 過程中會(huì)導(dǎo)致索引失效，必須要考慮重新索引
3、 alter table move 過程中會(huì)產(chǎn)生鎖，應(yīng)該避免在業(yè)務(wù)高峰期操作！
就第二點(diǎn)和第三點(diǎn)做實(shí)驗(yàn)說明如下
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
– 先獲取該 SESSION 的 SID, 方便實(shí)驗(yàn)觀察
SQL select sid from v$mystat where rownum=1;
SID
——————–
160
SQL create table ljb_test as select * from dba_objects;
Table created
SQL select count(*) from ljb_test;
COUNT(*)
——————-
62659
SQL create index idx_test on ljb_test(object_id);
Index created
– 查詢當(dāng)前該 SESSION 并無鎖
SQL select * from v$lock where sid=160;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- —- ———- ———- ———-
– 查看索引狀態(tài)也正常！
SQL select index_name,table_name,status from user_indexes where table_name= LJB_TEST
INDEX_NAME TABLE_NAME STATUS
—————————— —————————— ———————————————–
IDX_TEST LJB_TEST VALID
– 執(zhí)行命令 alter table ljb_test move;
— 查詢：
select * from v$lock where sid=160;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– —— —- ——- ———- —— ——–
—— —————-
2043451C 20434530 160 CF 0 0 4 0 0 0
1FA072BC 1FA073D8 160 TX 917534 592 6 0 1 0
204344C0 204344D4 160 HW 76 323783147 6 0 0 0
1F9C4224 1F9C423C 160 TM 84825 0 6 0 0 0
204342F4 20434308 160 TT 76 16 4 0 0 0
1F9C377C 1F9C37C4 160 TS 76 323783147 6 0 0 0
– 不過由于 alter table move 命令未結(jié)束，索引仍然有效！
SQL select index_name,table_name,status from user_indexes where table_name= LJB_TEST
INDEX_NAME TABLE_NAME STATUS
—————————— —————————— —————————————————-
IDX_TEST LJB_TEST VALID
– 等 alter table ljb_test move; 命令結(jié)束后，再查看發(fā)現(xiàn)鎖消失了
SQL select * from v$lock where sid=160;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- —- ———- ———- ———-
———- ———-
– 但是索引卻失效了！
SQL select index_name,table_name,status from user_indexes where table_name= LJB_TEST
INDEX_NAME TABLE_NAME STATUS
———————————————————————————-
IDX_TEST LJB_TEST UNUSABLE
總結(jié)：這個(gè)實(shí)驗(yàn)說明：除了知道 alter
table
move 命令可以釋放空間（當(dāng)然這語(yǔ)句最根本的作用還是移動(dòng)表到不同的表空間去，這里只是借用它可以釋放空間的一個(gè)特性），還要了解該動(dòng)作會(huì)鎖表直到命令結(jié)束，而且會(huì)導(dǎo)致索引失效，屬于危險(xiǎn)命令，建議千萬不要在業(yè)務(wù)高峰期操作。
二、shrink
都知道 alter table move 或 shrink space 可以收縮段，用來消除部分行遷移，消除空間碎片，使數(shù)據(jù)更緊密，但 move 跟 shrink space 還是有區(qū)別的。
Move 會(huì)移動(dòng)高水位，但不會(huì)釋放申請(qǐng)的空間，是在高水位以下 (below HWM) 的操作。
而 shrink space 同樣會(huì)移動(dòng)高水位，但也會(huì)釋放申請(qǐng)的空間，是在高水位上下 (below and above HWM) 都有的操作。
使用 Shrink 有兩個(gè)前提條件：
表必須啟用 row movement
表段所在表空間的段空間管理 (segment space management) 必須為 auto
在線段收縮的語(yǔ)法如下：
alter table/index/materialized view object_name shrink space [cascade] [compact];
cascade：是指壓縮所有依賴的對(duì)象，比如壓縮表語(yǔ)句加上 cascade，表上所有的索引都會(huì)被壓縮
compact：把壓縮過程分為兩個(gè)階段：第一個(gè)階段的語(yǔ)句帶 compact，壓縮段空間，在這個(gè)過程中需要在表上加 RX 鎖，即只在需要移動(dòng)的行上加鎖。由于涉及到 rowid 的改變，需要 enable row movement. 同時(shí)要 disable 基于 rowid 的 trigger. 這一過程對(duì)業(yè)務(wù)影響比較小。；第二個(gè)階段語(yǔ)句不帶 compact，調(diào)整高水位并釋放收回的空間。此過程需要在表上加 X 鎖，會(huì)造成表上的所有 DML 語(yǔ)句阻塞。在業(yè)務(wù)特別繁忙的系統(tǒng)上可能造成比較大的影響。對(duì)于大表，建議采用 compact 選項(xiàng)。
也許很難理解吧，看測(cè)試就知道了。
SQL select * from v$version;
BANNER
—————————————————————-
oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL analyze table test compute statistics;
Table analyzed.
SQL col SEGMENT_NAME for a10
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS INIT
———- ———- ———- ———-
TEST 10 1280 10
SQL col TABLE_NAME for a10
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 0 1280
–TEST 表初始分配了 10M 的空間，可以看到有 10 個(gè) EXTENTS，1280 個(gè) BLOCKS。USER_TABLES 視圖顯示有 0 個(gè)使用的 BLOCKS，1280 個(gè)空閑 BLOCKS，即該 10M 空間內(nèi)的 BLOCK 都還沒被 ORACLE”格式化”。
SQL begin
for i in 1..100000 loop
insert into test values(i);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS
———- ———- ———-
TEST 10 1280
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 186 1094
– 插入 10W 條數(shù)據(jù)后，分配的空間仍不變，因?yàn)?10 個(gè) EXTENTS 還沒使用完。顯示使用了 186 個(gè) BLOCKS，空閑 1094 個(gè) BLOCKS。這時(shí)候的 186BLOCKS 即是高水位線
SQL delete from test where rownum =50000;
50000 rows deleted.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS
———- ———- ———-
TEST 10 1280
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 186 1094
SQL select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
———–
77
– 這邊可以看到，刪掉一半數(shù)據(jù)后，仍然顯示使用了 186 個(gè) BLOCKS，高水位沒變。但查詢真正使用的 BLOCK 數(shù)只有 77 個(gè)。所以 DELETE 操作是不會(huì)改變 HWM 的
SQL alter table test move;
Table altered.
SQL analyze table test compute statistics;
Table analyzed.
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 81 1199
–MOVE 之后,HWM 降低了，空閑塊也上去了
SQL select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS
———- ———- ———-
TEST 10 1280
– 但是分配的空間并沒有改變，仍然是 1280 個(gè) BLOCKS。下面看用 SHRINK SPACE 的方式
SQL alter table test enable row movement;
Table altered.
SQL alter table test shrink space;
Table altered.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS
———- ———- ———-
TEST 1 88
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 81 7
– 分配的空間已經(jīng)降到最小，1 個(gè) EXTENTS，88 個(gè) BLOCKS
所以 MOVE 并不算真正意義上的壓縮空間，只會(huì)壓縮 HWM 以下的空間，消除碎片。我們一般建表時(shí)沒有指定 initial 參數(shù)(默認(rèn)是 8 個(gè) BLOCK)，也就感覺不到這個(gè)差異。而 SHRINK
SPACE 真正做到了對(duì)段的壓縮，包括初始分配的也壓了，所以它是 blow and above HWM 操作。
至于需要哪種方法，得看你的需求來了，需要分析表的增長(zhǎng)情況，要是以后還會(huì)達(dá)到以前的 HWM 高度，那顯然 MOVE 是更合適的，因?yàn)?SHRINK SPACE 還需要重新申請(qǐng)之前放掉的空間，無疑增加了操作。
注意：
1. 不過用 MOVE 的方式也可以做到真正的壓縮分配空間，只要指定 STORAGE 參數(shù)即可。
SQL drop table test;
Table dropped.
SQL create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS INIT
———- ———- ———- ———-
TEST 10 1280 10
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 0 1280
SQL alter table test move storage (initial 1m);
Table altered.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS INIT
———- ———- ———- ———-
TEST 16 128 1
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 0 128
2. 使用 move 時(shí)，會(huì)改變一些記錄的 ROWID，所以 MOVE 之后索引會(huì)變?yōu)闊o效，需要 REBUILD。同時(shí) move 表需要 2 倍的表空間，而 shrink 不需要
3. 使用 shrink space 時(shí)，索引會(huì)自動(dòng)維護(hù)。如果在業(yè)務(wù)繁忙時(shí)做壓縮，可以先 shrink space compact，來壓縮數(shù)據(jù)而不移動(dòng) HWM，等到不繁忙的時(shí)候再 shrink space 來移動(dòng) HWM。
4. 索引也是可以壓縮的，壓縮表時(shí)指定 Shrink space cascade 會(huì)同時(shí)壓縮索引，也可以 alter index xxx shrink space 來壓縮索引。
5.shrink space 需要在表空間是自動(dòng)段空間管理的，所以 system 表空間上的表無法 shrink space。
6.shrink 的限制
關(guān)于 alter table move 與 shrink space 的區(qū)別是什么就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
