成人无码视频,亚洲精品久久久久av无码,午夜精品久久久久久毛片,亚洲 中文字幕 日韩 无码

資訊專欄INFORMATION COLUMN

alter table move 和alter table shrink兩種重組表方法的對(duì)比

IT那活兒 / 3530人閱讀
alter table move 和alter table shrink兩種重組表方法的對(duì)比
點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多精彩內(nèi)容?。?/span>

01


move&shrink--空間釋放


shrink移動(dòng)高水位線的同時(shí)，釋放申請(qǐng)的空間；而move不會(huì)。
1. 新建兩種表test3、test4，并向表中插入數(shù)據(jù)； 
2. 查詢兩張表當(dāng)前占用的數(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進(jìn)行move操作，test4進(jìn)行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. 對(duì)比move和shrink的結(jié)果；
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
通過(guò)對(duì)比結(jié)果，兩張的初始數(shù)據(jù)量相同，刪除的數(shù)據(jù)量相同，剩余數(shù)據(jù)量也相同。但在分別執(zhí)行move和shrink后，test3中的BLOCKS 數(shù)量明顯多于test4，而test4中EMPTY_BLOCKS多于test3.說(shuō)明當(dāng)前test4的空間釋放更完全。

02


move&shrink--索引


move后表中的索引需要重建；shrink自動(dòng)維護(hù)索引。
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的部分?jǐn)?shù)據(jù)后進(jìn)行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. 對(duì)比move前后rowid，此時(shí)rowid已經(jīng)發(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)，當(dāng)前已不可用，需要重建；
SQL> select index_name,status from user_indexes where index_name=T5;
INDEX_NAME STATUS
------------------------------ --------
T5 UNUSABLE
5. 重建test5的索引，索引恢復(fù)可用；
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的部分?jǐn)?shù)據(jù)后進(jìn)行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
通過(guò)對(duì)比實(shí)驗(yàn)結(jié)果，shrink后表的rowid不變且當(dāng)前索引依舊可用；但move后rowid產(chǎn)生了變化且索引不可用，需要重建恢復(fù)。

03


move&shrink--表空間不足


在表空間不充足時(shí)，move操作無(wú)法進(jìn)行（需要跟原表相同的空間大?。?。
1. 分別創(chuàng)建兩個(gè)大小相等的表空間；
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. 在兩個(gè)表空間分別創(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í)驗(yàn)數(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. 確認(rèn)表1的大小，刪除部分?jǐn)?shù)據(jù)后進(jìn)行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. 返回結(jié)果報(bào)錯(cuò)，沒(méi)有充足的空間；
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. 確認(rèn)表2的大小，刪除部分?jǐn)?shù)據(jù)后進(jìn)行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.
通過(guò)結(jié)果對(duì)比得，當(dāng)所在的表空間剩余空間不足時(shí)，move是無(wú)法進(jìn)行的，而shrink可以成功完成。但是可以將表move到其他空間充足的表空間進(jìn)行重組，刪除原來(lái)的表再move會(huì)開(kāi)始所在的表空間；而shrink無(wú)法實(shí)現(xiàn)，只能在當(dāng)前所在的位置進(jìn)行操作。

本 文 原 創(chuàng) 來(lái) 源：IT那活兒微信公眾號(hào)（上海新炬王翦團(tuán)隊(duì)）


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

轉(zhuǎn)載請(qǐng)注明本文地址：http://m.hztianpu.com/yun/129697.html

相關(guān)文章

  • 大廠面試預(yù)備篇——《兩萬(wàn)字MySql基礎(chǔ)總結(jié)》??建議收藏

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

    dreamtecher 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<