Both the move and shrink commands reset the high water mark of a table, but which command is more efficient? This article discusses re-organizing a table using the move and shrink commands, then compares how the rows are compacted within Oracle blocks and how row chaining is resolved.

Note: It is beyond the scope of this document to present a detailed discussion about the High Water Mark of a table and all of the different methodologies for resetting the High Water Mark of a table.

The following steps briefly describe various operations performed on the table, TEMP_JP, during the re-org of the table using the move and shrink commands. A few exceptions in the series of steps are marked distinctly(Move related commands in BLUE, Shrink related commands in GREEN).

TEST A

How move operation affects row chaining and distribution of data within Oracle blocks in a table:

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

TEST B

How the shrink operation affects row chaining and distribution of data within Oracle blocks in a table:

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

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

All of the rows are compacted into one oracle block, after the move operation on temp_jp table. Row chaining is completely resolved in temp_jp table.

The shrink operation could not completely resolve row chaining in the table. The remaining 5 rows in the table are spread across three oracle blocks in the table.

After all the foregoing, in a read intensive application, where milliseconds in performance count, I would vote for the move command. I am prepared to go the extra mile, rebuilding the unusable indexes and provisioning extra disk space for the objects’ move operation while resetting high water mark.

REFERENCES:

http://www.dbasupport.com/oracle/ora10g/unusable_indexes.shtml

Note:242090.1 10g NEW FEATURE on SEGMENT SHRINK

http://sysdba.wordpress.com/2006/04/28/how-to-adjust-the-high-watermark-in-oracle-10g-alter-table-shrink/

http://forums.oracle.com/forums/thread.jspa?messageID=2414152

Back to DBAsupport.com