熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle:MOVE與SHRINK命令相比較

2013-11-13 16:06:03  來源: Oracle 

  MITe命令壓縮Oracle塊中的記錄解決行鏈接問題並重置表的高水平線

  move和shrink命令都會重置表的高水平線那麼哪個命令更有效呢?這篇文章討論使用move和shrink命令重新組織一個表然後比較Oracle塊的記錄被壓縮得怎麼樣以及行鏈接解決得怎麼樣

  注意關於表高水平線和重置表高水平線的不同方法的詳細討論不在這篇文章的討論范圍內

  下面的步驟簡要描述了使用move和shrink命令對TEMP_JP表進行重組時對該表的各種操作在這一系列步驟中出現的一些異常被清晰地標注出來(Move相關的命令是以藍色表示Shrink相關的命令是以綠色表示)

  

   – 

  創建表temp_jp並插入條記錄到這個表中

  

  顯示在表的Oracle塊中的記錄分配

  奇怪的是插入到每個Oracle塊中的記錄數不是相同的

  

  索引表temp_jp

  

  檢驗表和索引的磁盤空間利用情況

默認情況下Oracle為一個表分配一個區間為一個索引分配兩個區間

  

  對temp_jp表添加一個第三列來模擬行鏈接

  

  分析Temp_jp表

  

  從temp_jp表選擇行數和鏈接行數

temp_jp表中幾乎所有的記錄都是鏈接的

  

  在模擬行鏈接之後檢查表和索引的磁盤空間利用情況

表temp_jp的磁盤空間使用增加了

  

  刪除這個表的所有記錄使這個表中每個Oracle塊只留一條記錄

  

  在刪除記錄之後展開顯示temp_jp表的記錄

記錄的數目顯示了每個Oracle塊是一致的因為我們刪除了這個表的所有記錄這個表中每個Oracle塊只留一條記錄

  

  分析Temp_jp表

  

  從temp_jp表選擇行數和鏈接行數

  

  檢查temp_jp表的索引狀態它是VALID

  

  A

對表執行move操作

BA

BB對temp_jp表執行shrink操作

  

  A

在對表執行了move操作之後索引的狀態是不可用的

B在對temp_jp表執行了操作之後索引的狀態是無效的

  

  顯示temp_jp表和它的索引的磁盤空間使用情況

A

在進行move操作之後對表分配了一個區間對索引分配了兩個區間

B

在進行shrink操作之後表和索引被分配了每個具有個Oracle塊的區間最初分配給索引的兩個區間下降為一個

  

  展開顯示temp_jp表Oracle塊中的記錄

A

在temp_jp表中的所有條記錄被壓縮到一個Oracle塊中

B

在temp_jp表中的所有條記錄被擴展到個Oracle塊中

  

  AA

表分析操作失敗錯誤為ORA

AB

在表上重新建立索引來使其生效

在索引重建操作之後一個Oracle區間被分配給了這個索引

AC

在索引重建之後分析這個表

B

分析這個表

  

  選擇temp_jp表的行數和行鏈接數

A

在對temp_jp表進行move操作之後行鏈接被解決了鏈接行數為

B

在對temp_jp表進行shrink操作之後行鏈接沒有解決鏈接行數為

  

  檢驗temp_jp表的索引狀態是有效的

  測試A

  move操作怎樣影響一個表的Oracle塊中行鏈接和數據的分配

  A

  drop table temp_jp;

  A

  create table temp_jp(col number()col varchar()) tablespace users;

  A

   declare
  begin
  for i in loop
  insert into temp_jp values(iRAMA);
  end loop;
  commit;
  end;
  /

  A

   select dbms_rowidrowid_relative_fno(rowid)
  dbms_rowidrowid_block_number(rowid) 
  count(*)
  from temp_jp
  group by dbms_rowidrowid_relative_fno(rowid)
  dbms_rowidrowid_block_number(rowid)
  order by dbms_rowidrowid_relative_fno(rowid) 
  dbms_rowidrowid_block_number(rowid);
DBMS_ROWIDROWID_RELATIVE_FNO(ROWID) DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
rows selected

  A

  create index temp_jp_idx on temp_jp(col) tablespace users;

  A

   select segment_namesegment_typebytes// mgblocksextents
  from user_segments where segment_name like TEMP%;
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
                                   
TEMP_JP           TABLE                                       
TEMP_JP_IDX      INDEX                                        

  A

   alter table temp_jp add(col varchar() default THIS IS TO TEST THE ROW CHAINING ISSUE
WITH  MOVE COMMAND AND HOW THE DATA IS SPREAD BEFORE AND AFTER THE MOVE COMMAND IN EACH BLOCK OF THE TABLE);

  A

  analyze table temp_jp compute statistics;

  A

   select table_namenum_rowschain_cnt from user_tables where table_name=TEMP_JP;
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
            
TEMP_JP                                          

  A

   select segment_namesegment_typebytes// mgblocksextents
  from user_segments where segment_name like TEMP%;
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
          
TEMP_JP           TABLE                                     
TEMP_JP_IDX      INDEX                                      

  A

   declare
   begin
   for c in (select DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID) block
    max(rowid) max_rowid
  from temp_jp group by DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)) loop
  for c in (select rowidDBMS_ROWIDROWID_BLOCK_NUMBER(ROWID) block
    from temp_jp
  where DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)=cblock) loop
  if ((cblock = cblock)  and (crowid <> cmax_rowid)) then
 delete from temp_jp where rowid = crowid;
 end if;
 end loop;
 end loop;
commit;
 end;
  /

  A

   select dbms_rowidrowid_relative_fno(rowid)
      dbms_rowidrowid_block_number(rowid) 
      count(*)
      from temp_jp
      group by dbms_rowidrowid_relative_fno(rowid)
      dbms_rowidrowid_block_number(rowid)
      order by dbms_rowidrowid_relative_fno(rowid) 
      dbms_rowidrowid_block_number(rowid);
DBMS_ROWIDROWID_RELATIVE_FNO(ROWID) DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

                                                                             
                                                                             
                                                                             
                                                                             
                                                                             

  A

  analyze table temp_jp compute statistics;

  B

   select table_namenum_rowschain_cnt from user_tables where table_name=TEMP_JP;
TABLE_NAME               NUM_ROWS  CHAIN_CNT

TEMP_JP                                          

  A

   select index_namestatus from user_indexes where table_name=TEMP_JP;
INDEX_NAME                     STATUS

TEMP_JP_IDX                    VALID

  A

  alter table temp_jp move tablespace users;

  A

   select index_namestatus from user_indexes where table_name=TEMP_JP;
INDEX_NAME                     STATUS

TEMP_JP_IDX                    UNUSABLE

  A

   select segment_namesegment_typebytes// mgblocksextents
     from user_segments where segment_name like TEMP%;
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS

TEMP_JP              TABLE                                  
TEMP_JP_IDX     INDEX                                 

  A

   select dbms_rowidrowid_relative_fno(rowid)
         dbms_rowidrowid_block_number(rowid) 
          count(*)
          from temp_jp
          group by dbms_rowidrowid_relative_fno(rowid)
          dbms_rowidrowid_block_number(rowid)
          order by dbms_rowidrowid_relative_fno(rowid) 
          dbms_rowidrowid_block_number(rowid);
DBMS_ROWIDROWID_RELATIVE_FNO(ROWID) DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

                                                                             

  AA

   analyze table temp_jp compute statistics;
analyze table temp_jp compute statistics
*
ERROR at line :
ORA: index GZBGQTTEMP_JP_IDX or partition of such index is in unusable state

  AB

  alter index TEMP_JP_IDX rebuild online;

  AC

  analyze table temp_jp compute statistics;

  A

   select table_namenum_rowschain_cnt from user_tables where table_name=TEMP_JP;
TABLE_NAME                       NUM_ROWS  CHAIN_CNT

TEMP_JP                                          

  A

   select index_namestatus from user_indexes where table_name=TEMP_JP;
INDEX_NAME                     STATUS

TEMP_JP_IDX                    VALID

  測試B

  shrink操作怎樣影響一個表的Oracle塊中行鏈接和數據分配

  B

  drop table temp_jp;

  B

  create table temp_jp(col number()col varchar()) tablespace users;

  B

  
  declare
  begin
  for i in  loop
  insert into temp_jp values(iRAMA);
  end loop;
  commit;
  end;
  /

  B

    select dbms_rowidrowid_relative_fno(rowid)
   dbms_rowidrowid_block_number(rowid) 
   count(*)
   from temp_jp
   group by dbms_rowidrowid_relative_fno(rowid)
 dbms_rowidrowid_block_number(rowid)
   order by dbms_rowidrowid_relative_fno(rowid) 
 dbms_rowidrowid_block_number(rowid);
DBMS_ROWIDROWID_RELATIVE_FNO(ROWID) DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
   
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
rows selected

  B

  create index temp_jp_idx on temp_jp(col) tablespace users;

  B

   select segment_namesegment_typebytes// mgblocksextents
  from user_segments where segment_name like TEMP%;
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
                         
TEMP_JP                    TABLE                                                     
TEMP_JP_IDX           INDEX                                                     

  B

   alter table temp_jp add(col varchar() default THIS IS TO TEST THE ROW CHAINING ISSUE WITH  MOVE
COMMAND AND HOW THE DATA IS SPREAD BEFORE AND AFTER THE MOVE COMMAND IN EACH
BLOCK OF THE TABLE);

  B

  analyze table temp_jp compute statistics;

  B

   select table_namenum_rowschain_cnt from user_tables where table_name=TEMP_JP;
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
           
TEMP_JP                                                

  B

   select segment_namesegment_typebytes// mgblocksextents
  from user_segments where segment_name like TEMP%;
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
                          
TEMP_JP         TABLE                                              
TEMP_JP_IDX     INDEX                                                              

  B

   declare
begin
for c in (select DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID) block
    max(rowid) max_rowid
           from temp_jp group by DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)) loop
for c in (select rowidDBMS_ROWIDROWID_BLOCK_NUMBER(ROWID) block
           from temp_jp
           where DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)=cblock) loop
if ((cblock = cblock) and (crowid <> cmax_rowid)) then
delete from temp_jp where rowid = crowid;
end if;
end loop;
end loop;
commit;
end;
/

  B

   select dbms_rowidrowid_relative_fno(rowid)
  dbms_rowidrowid_block_number(rowid) 
  count(*)
  from temp_jp
  group by dbms_rowidrowid_relative_fno(rowid)
 dbms_rowidrowid_block_number(rowid)
  order by dbms_rowidrowid_relative_fno(rowid) 
 dbms_rowidrowid_block_number(rowid);
DBMS_ROWIDROWID_RELATIVE_FNO(ROWID) DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
rows selected

  B

  analyze table temp_jp compute statistics;

  A

   select table_namenum_rowschain_cnt from user_tables where table_name=TEMP_JP;
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
                     
TEMP_JP                                                        

  B

   select index_namestatus from user_indexes where table_name=TEMP_JP;
INDEX_NAME                     STATUS
      
TEMP_JP_IDX                    VALID

  BA

  alter table temp_jp enable row movement;

  BB

  alter table temp_jp shrink space cascade;

  B

   select index_namestatus from user_indexes where table_name=TEMP_JP;
INDEX_NAME                     STATUS
      
TEMP_JP_IDX                    VALID

  B

   select segment_namesegment_typebytes// mgblocksextents
  from user_segments where segment_name like TEMP%;
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
                                          
TEMP_JP                   TABLE                                                     
TEMP_JP_IDX          INDEX                                                     

  B

   select dbms_rowidrowid_relative_fno(rowid)
  dbms_rowidrowid_block_number(rowid) 
  count(*)
  from temp_jp
  group by dbms_rowidrowid_relative_fno(rowid)
  dbms_rowidrowid_block_number(rowid)
  order by dbms_rowidrowid_relative_fno(rowid) 
  dbms_rowidrowid_block_number(rowid);
DBMS_ROWIDROWID_RELATIVE_FNO(ROWID) DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
 
                                                                             
                                                                             
                                                                             

  B

  analyze table temp_jp compute statistics;

  B

   select table_namenum_rowschain_cnt from user_tables where table_name=TEMP_JP;
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
                   
TEMP_JP                                              

  B

   select index_namestatus from user_indexes where table_name=TEMP_JP;
INDEX_NAME                     STATUS
     
TEMP_JP_IDX                    VALID

  在對temp_jp表進行move操作之後所有的記錄被壓縮進一個oracle塊中在temp_jp表中的行鏈接問題被完全解決了

  shrink不能完全解決表中行鏈接問題表中留下的條記錄被擴展到這個表的個oracle塊中

  在上面的比較之後對於一個讀取要求較高執行以毫秒來計的應用程序我推薦使用move命令


From:http://tw.wingwit.com/Article/program/Oracle/201311/17804.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.