MITe命令壓縮Oracle塊中的記錄
move和shrink命令都會重置表的高水平線
注意
下面的步驟簡要描述了使用move和shrink命令對TEMP_JP表進行重組時對該表的各種操作
創建表temp_jp並插入
顯示在表的Oracle塊中的記錄分配
奇怪的是
索引表temp_jp
檢驗表和索引的磁盤空間利用情況
默認情況下
對temp_jp表添加一個第三列來模擬行鏈接
分析Temp_jp表
從temp_jp表選擇行數和鏈接行數
temp_jp表中幾乎所有的記錄都是鏈接的
在模擬行鏈接之後檢查表和索引的磁盤空間利用情況
表temp_jp的磁盤空間使用增加了
刪除這個表的所有記錄
在刪除記錄之後
記錄的數目顯示了每個Oracle塊是一致的
分析Temp_jp表
從temp_jp表選擇行數和鏈接行數
檢查temp_jp表的索引狀態
對表執行move操作
在對表執行了move操作之後
顯示temp_jp表和它的索引的磁盤空間使用情況
在進行move操作之後
在進行shrink操作之後
展開顯示temp_jp表Oracle塊中的記錄
在temp_jp表中的所有
在temp_jp表中的所有
表分析操作失敗
在表上重新建立索引來使其生效
在索引重建操作之後
在索引重建之後
分析這個表
選擇temp_jp表的行數和行鏈接數
在對temp_jp表進行move操作之後
在對temp_jp表進行shrink操作之後
檢驗temp_jp表的索引狀態是有效的
測試A
move操作怎樣影響一個表的Oracle塊中行鏈接和數據的分配
drop table temp_jp;
create table temp_jp(col
declare
begin
for i in
insert into temp_jp values(i
end loop;
commit;
end;
/
select dbms_rowid
dbms_rowid
count(*)
from temp_jp
group by dbms_rowid
dbms_rowid
order by dbms_rowid
dbms_rowid
DBMS_ROWID
create index temp_jp_idx on temp_jp(col
select segment_name
from user_segments where segment_name like
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
TEMP_JP TABLE
TEMP_JP_IDX INDEX
alter table temp_jp add(col
WITH MOVE COMMAND AND HOW THE DATA IS SPREAD BEFORE AND AFTER THE MOVE COMMAND IN EACH BLOCK OF THE TABLE
analyze table temp_jp compute statistics;
select table_name
TABLE_NAME NUM_ROWS CHAIN_CNT
TEMP_JP
select segment_name
from user_segments where segment_name like
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
TEMP_JP TABLE
TEMP_JP_IDX INDEX
declare
begin
for c
max(rowid) max_rowid
from temp_jp group by DBMS_ROWID
for c
from temp_jp
where DBMS_ROWID
if ((c
delete from temp_jp where rowid = c
end if;
end loop;
end loop;
commit;
end;
/
select dbms_rowid
dbms_rowid
count(*)
from temp_jp
group by dbms_rowid
dbms_rowid
order by dbms_rowid
dbms_rowid
DBMS_ROWID
analyze table temp_jp compute statistics;
select table_name
TABLE_NAME NUM_ROWS CHAIN_CNT
TEMP_JP
select index_name
INDEX_NAME STATUS
TEMP_JP_IDX VALID
alter table temp_jp move tablespace users;
select index_name
INDEX_NAME STATUS
TEMP_JP_IDX UNUSABLE
select segment_name
from user_segments where segment_name like
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
TEMP_JP TABLE
TEMP_JP_IDX INDEX
select dbms_rowid
dbms_rowid
count(*)
from temp_jp
group by dbms_rowid
dbms_rowid
order by dbms_rowid
dbms_rowid
DBMS_ROWID
analyze table temp_jp compute statistics;
analyze table temp_jp compute statistics
*
ERROR at line
ORA
alter index TEMP_JP_IDX rebuild online;
analyze table temp_jp compute statistics;
select table_name
TABLE_NAME NUM_ROWS CHAIN_CNT
TEMP_JP
select index_name
INDEX_NAME STATUS
TEMP_JP_IDX VALID
測試B
shrink操作怎樣影響一個表的Oracle塊中行鏈接和數據分配
drop table temp_jp;
create table temp_jp(col
declare
begin
for i in
insert into temp_jp values(i
end loop;
commit;
end;
/
select dbms_rowid
dbms_rowid
count(*)
from temp_jp
group by dbms_rowid
dbms_rowid
order by dbms_rowid
dbms_rowid
DBMS_ROWID
create index temp_jp_idx on temp_jp(col
select segment_name
from user_segments where segment_name like
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
TEMP_JP TABLE
TEMP_JP_IDX INDEX
alter table temp_jp add(col
COMMAND AND HOW THE DATA IS SPREAD BEFORE AND AFTER THE MOVE COMMAND IN EACH
BLOCK OF THE TABLE
analyze table temp_jp compute statistics;
select table_name
TABLE_NAME NUM_ROWS CHAIN_CNT
TEMP_JP
select segment_name
from user_segments where segment_name like
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
TEMP_JP TABLE
TEMP_JP_IDX INDEX
declare
begin
for c
max(rowid) max_rowid
from temp_jp group by DBMS_ROWID
for c
from temp_jp
where DBMS_ROWID
if ((c
delete from temp_jp where rowid = c
end if;
end loop;
end loop;
commit;
end;
/
select dbms_rowid
dbms_rowid
count(*)
from temp_jp
group by dbms_rowid
dbms_rowid
order by dbms_rowid
dbms_rowid
DBMS_ROWID
analyze table temp_jp compute statistics;
select table_name
TABLE_NAME NUM_ROWS CHAIN_CNT
TEMP_JP
select index_name
INDEX_NAME STATUS
TEMP_JP_IDX VALID
alter table temp_jp enable row movement;
alter table temp_jp shrink space cascade;
select index_name
INDEX_NAME STATUS
TEMP_JP_IDX VALID
select segment_name
from user_segments where segment_name like
SEGMENT_NAME SEGMENT_TYPE MG BLOCKS EXTENTS
TEMP_JP TABLE
TEMP_JP_IDX INDEX
select dbms_rowid
dbms_rowid
count(*)
from temp_jp
group by dbms_rowid
dbms_rowid
order by dbms_rowid
dbms_rowid
DBMS_ROWID
analyze table temp_jp compute statistics;
select table_name
TABLE_NAME NUM_ROWS CHAIN_CNT
TEMP_JP
select index_name
INDEX_NAME STATUS
TEMP_JP_IDX VALID
在對temp_jp表進行move操作之後
shrink不能完全解決表中行鏈接問題
在上面的比較之後
From:http://tw.wingwit.com/Article/program/Oracle/201311/17804.html