Oracle：MOVE与SHRINK命令相比较

MITe命令压缩Oracle块中的记录、解决行链接问题，并重置表的高水平线。

　　move和shrink命令都会重置表的高水平线，那么哪个命令更有效呢?这篇文章讨论使用move和shrink命令重新组织一个表，然后比较Oracle块的记录被压缩得怎么样以及行链接解决得怎么样。

　　注意：关于表高水平线和重置表高水平线的不同方法的详细讨论不在这篇文章的讨论范围内。

　　下面的步骤简要描述了使用move和shrink命令对TEMP_JP表进行重组时对该表的各种操作。在这一系列步骤中出现的一些异常被清晰地标注出来(Move相关的命令是以蓝色表示，Shrink相关的命令是以绿色表示)。

 

01 – 03	创建表temp_jp并插入2500条记录到这个表中。
04	显示在表的Oracle块中的记录分配。 奇怪的是，插入到每个Oracle块中的记录数不是相同的。
05	索引表temp_jp。
06	检验表和索引的磁盘空间利用情况。 默认情况下，Oracle为一个表分配一个区间，为一个索引分配两个区间。
07	对temp_jp表添加一个第三列来模拟行链接。
08	分析Temp_jp表。
09	从temp_jp表选择行数和链接行数。 temp_jp表中几乎所有的记录都是链接的。
10	在模拟行链接之后检查表和索引的磁盘空间利用情况。 表temp_jp的磁盘空间使用增加了10番。
11	删除这个表的所有记录，使这个表中每个Oracle块只留一条记录。
12	在删除记录之后，展开显示temp_jp表的记录。 记录的数目显示了每个Oracle块是一致的，因为我们删除了这个表的所有记录，这个表中每个Oracle块只留一条记录。
13	分析Temp_jp表。
14	从temp_jp表选择行数和链接行数。
15	检查temp_jp表的索引状态。它是VALID。
16	16-A 对表执行move操作。 16-BA 16-BB对temp_jp表执行shrink操作。
17	17-A 在对表执行了move操作之后，索引的状态是不可用的。 17-B在对temp_jp表执行了操作之后，索引的状态是无效的。
18	显示temp_jp表和它的索引的磁盘空间使用情况。 18-A 在进行move操作之后，对表分配了一个区间，对索引分配了两个区间。 18-B 在进行shrink操作之后，表和索引被分配了每个具有8个Oracle块的区间。最初分配给索引的两个区间下降为一个。
19	展开显示temp_jp表Oracle块中的记录。 19-A 在temp_jp表中的所有5条记录被压缩到一个Oracle块中。 19-B 在temp_jp表中的所有5条记录被扩展到3个Oracle块中。
20	20-AA 表分析操作失败，错误为ORA-01502。 20-AB 在表上重新建立索引来使其生效。 在索引重建操作之后，一个Oracle区间被分配给了这个索引。 20-AC 在索引重建之后，分析这个表。 20-B 分析这个表。
21	选择temp_jp表的行数和行链接数。 21-A 在对temp_jp表进行move操作之后，行链接被解决了，链接行数为0。 21-B 在对temp_jp表进行shrink操作之后，行链接没有解决。链接行数为2.
22	检验temp_jp表的索引状态是有效的。

测试A

　　move操作怎样影响一个表的Oracle块中行链接和数据的分配：

　　01-A

　　drop table temp_jp;

　　02-A

　　create table temp_jp(col1 number(10),col2 varchar2(20)) tablespace users;

　　03-A

declare
  begin
  for i in 1..2500 loop
  insert into temp_jp values(i,'RAMA');
  end loop;
  commit;
  end;
  /

　　04-A

select dbms_rowid.rowid_relative_fno(rowid) ,
  dbms_rowid.rowid_block_number(rowid)  ,
  count(*)
  from temp_jp
  group by dbms_rowid.rowid_relative_fno(rowid),
  dbms_rowid.rowid_block_number(rowid)
  order by dbms_rowid.rowid_relative_fno(rowid), 
  dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ -------------------------- ----------
                                   4                                 2764        526
                                   4                                 2765        519
                                   4                                 2766        417
                                   4                                 2767        519
                                   4                                 2768        519
5 rows selected.

　　05-A

　　create index temp_jp_idx on temp_jp(col1) tablespace users;

06-A

select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
  from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
---------------         ------------------                 ----------  ----------        ----------
TEMP_JP           TABLE                    .0625          8          1
TEMP_JP_IDX      INDEX                     .125          16          2

　　07-A

alter table temp_jp add(col3 varchar2(256) 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');

　　08-A

　　analyze table temp_jp compute statistics;

　　09-A

select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------        ----------     ----------
TEMP_JP                                2500           2426

　　10-A

select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
  from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
---------------         ------------------  ---------- ---------- ----------
TEMP_JP           TABLE                    .625         80         10
TEMP_JP_IDX      INDEX                    .125         16          2

　　11-A

declare
   begin
   for c1 in (select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block,
    max(rowid) max_rowid
  from temp_jp group by DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) loop
  for c2 in (select rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block
    from temp_jp
  where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=c1.block) loop
  if ((c2.block = c1.block)  and (c2.rowid <> c1.max_rowid)) then
 delete from temp_jp where rowid = c2.rowid;
 end if;
 end loop;
 end loop;
commit;
 end;
  /

12-A

select dbms_rowid.rowid_relative_fno(rowid) ,
      dbms_rowid.rowid_block_number(rowid)  ,
      count(*)
      from temp_jp
      group by dbms_rowid.rowid_relative_fno(rowid),
      dbms_rowid.rowid_block_number(rowid)
      order by dbms_rowid.rowid_relative_fno(rowid), 
      dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ------------------------------------ ----------
                                   4                                 2764          1
                                   4                                 2765          1
                                   4                                 2766          1
                                   4                                 2767          1
                                   4                                 2768          1

　　13-A

　　analyze table temp_jp compute statistics;

　　14-B

select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME               NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
TEMP_JP                                 5          5

　　15-A

select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME                     STATUS
------------------------------ --------
TEMP_JP_IDX                    VALID

　　16-A

　　alter table temp_jp move tablespace users;

　　17-A

select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME                     STATUS
------------------------------ --------
TEMP_JP_IDX                    UNUSABLE

    18-A

select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
     from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
--------------- ------------------ ---------- ---------- ----------
TEMP_JP              TABLE               .0625          8          1
TEMP_JP_IDX     INDEX               .125         16          2

　　19-A

select dbms_rowid.rowid_relative_fno(rowid) ,
         dbms_rowid.rowid_block_number(rowid)  ,
          count(*)
          from temp_jp
          group by dbms_rowid.rowid_relative_fno(rowid),
          dbms_rowid.rowid_block_number(rowid)
          order by dbms_rowid.rowid_relative_fno(rowid), 
          dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ------------------------------------ ----------
                                   4                                 2908          5

　　20-AA

analyze table temp_jp compute statistics;
analyze table temp_jp compute statistics
*
ERROR at line 1:
ORA-01502: index 'GZBGQT.TEMP_JP_IDX' or partition of such index is in unusable state

　　20-AB

　　alter index TEMP_JP_IDX rebuild online;

　　20-AC

　　analyze table temp_jp compute statistics;

　　21-A

select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
TEMP_JP                                 5          0

22-A

select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME                     STATUS
------------------------------ --------
TEMP_JP_IDX                    VALID

　　测试B

　　shrink操作怎样影响一个表的Oracle块中行链接和数据分配：

　　01-B

　　drop table temp_jp;

　　02-B

　　create table temp_jp(col1 number(10),col2 varchar2(20)) tablespace users;

　　03-B

　　declare
　　begin
　　for i in 1..2500 loop
　　insert into temp_jp values(i,'RAMA');
　　end loop;
　　commit;
　　end;
　　/

　　04-B

 select dbms_rowid.rowid_relative_fno(rowid) ,
   dbms_rowid.rowid_block_number(rowid)  ,
   count(*)
   from temp_jp
   group by dbms_rowid.rowid_relative_fno(rowid),
 dbms_rowid.rowid_block_number(rowid)
   order by dbms_rowid.rowid_relative_fno(rowid), 
 dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ------------------------    ----------
                                   4                                 1908        526
                                   4                                 1909        519
                                   4                                 1910        417
                                   4                                 1911        519
                                   4                                 1912        519
5 rows selected.

05-B

　　create index temp_jp_idx on temp_jp(col1) tablespace users;

　　06-B

select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
  from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
---------------          ------------------        ----------  ----------      ----------
TEMP_JP                    TABLE                             .0625          8               1
TEMP_JP_IDX           INDEX                              .125          16              2

　　07-B

alter table temp_jp add(col3 varchar2(256) 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');

　　08-B

　　analyze table temp_jp compute statistics;

　　09-B

select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------        ----------    ----------
TEMP_JP                                2500                 2426

　　10-B

select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
  from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
---------------          ------------------         ----------  ----------      ----------
TEMP_JP         TABLE                            .625         80          10
TEMP_JP_IDX     INDEX                                    .125         16                  2

　　11-B

declare
begin
for c1 in (select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block,
    max(rowid) max_rowid
           from temp_jp group by DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) loop
for c2 in (select rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block
           from temp_jp
           where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=c1.block) loop
if ((c2.block = c1.block) and (c2.rowid <> c1.max_rowid)) then
delete from temp_jp where rowid = c2.rowid;
end if;
end loop;
end loop;
commit;
end;
/

12-B

select dbms_rowid.rowid_relative_fno(rowid) ,
  dbms_rowid.rowid_block_number(rowid)  ,
  count(*)
  from temp_jp
  group by dbms_rowid.rowid_relative_fno(rowid),
 dbms_rowid.rowid_block_number(rowid)
  order by dbms_rowid.rowid_relative_fno(rowid), 
 dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ --------------------------   ----------
                                   4                                 1908          1
                                   4                                 1909          1
                                   4                                 1910          1
                                   4                                 1911          1
                                   4                                 1912          1
5 rows selected.

　　13-B

　　analyze table temp_jp compute statistics;

　　14-A

select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------         ----------             ----------
TEMP_JP                                 5                        5

　　15-B

select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME                     STATUS
------------------------------       --------
TEMP_JP_IDX                    VALID

　　16-BA

　　alter table temp_jp enable row movement;

　　16-BB

　　alter table temp_jp shrink space cascade;

　　17-B

select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME                     STATUS
------------------------------       --------
TEMP_JP_IDX                    VALID

18-B

select segment_name,segment_type,bytes/1024/1024 mg,blocks,extents
  from user_segments where segment_name like 'TEMP%';
SEGMENT_NAME    SEGMENT_TYPE               MG     BLOCKS    EXTENTS
---------------                ------------------                  ----------  ----------       ----------
TEMP_JP                   TABLE                              .0625          8              1
TEMP_JP_IDX          INDEX                               .0625          8             1

　　19-B

select dbms_rowid.rowid_relative_fno(rowid) ,
  dbms_rowid.rowid_block_number(rowid)  ,
  count(*)
  from temp_jp
  group by dbms_rowid.rowid_relative_fno(rowid),
  dbms_rowid.rowid_block_number(rowid)
  order by dbms_rowid.rowid_relative_fno(rowid), 
  dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ --------------------------  ----------
                                   4                                 1908          3
                                   4                                 1909          1
                                   4                                 1910          1

　　20-B

　　analyze table temp_jp compute statistics;

　　21-B

select table_name,num_rows,chain_cnt from user_tables where table_name='TEMP_JP';
TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------       ----------             ----------
TEMP_JP                                 5              2

　　22-B

select index_name,status from user_indexes where table_name='TEMP_JP';
INDEX_NAME                     STATUS
------------------------------      --------
TEMP_JP_IDX                    VALID

　　在对temp_jp表进行move操作之后，所有的记录被压缩进一个oracle块中。在temp_jp表中的行链接问题被完全解决了。

　　shrink不能完全解决表中行链接问题。表中留下的5条记录被扩展到这个表的3个oracle块中。

　　在上面的比较之后，对于一个读取要求较高、执行以毫秒来计的应用程序，我推荐使用move命令。