Oracle: MOVE vs SHRINK Commands

by JP Vijaykumar


The move command compacts the rows within Oracle blocks, resolves row chaining, and resets a table’s High Water Mark


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).
































































01 – 03 Created table temp_jp and inserted 2500 rows into the table
04 Displayed the rows distribution in the table’s Oracle blocks.

Strangely, the number of rows inserted per Oracle block is not uniform.
05 Indexed my table temp_jp.
06 Verified the disk space usage for the table and index.

By default, Oracle allocates one extent for a table and two extents for an index.
07 Added a third column to temp_jp table to simulate row chaining.
08 Temp_jp table is analyzed.
09 Select the number of rows and number of chained rows from temp_jp table.

Almost all of the rows in the table temp_jp are chained.
10 Verified the disk space usage for the table and index after simulating row chaining.

The disk space usage for the table temp_jp increased by 10 fold.
11 Deleted all rows from the table, leaving one row per Oracle block within the table.
12 Displayed the spread of rows from temp_jp table after deleting records.

The number of rows present per Oracle block are uniform, since we deleted all of the rows from table, leaving one row per Oracle block within the table.
13 Temp_jp table is analyzed.
14 Select number of rows and chained rows from temp_jp table.
15 Verify the status of the index on temp_jp table. It’s VALID.
16 16-A

Performed move operation on temp_jp table.

16-BA

16-BB Performed shrink operation on temp_jp table.
17 17-A

The status of the index is unusable after temp_jp table’s move operation.

17-B The status of the index is valid after temp_jp table’s shrink operation.
18 Displayed the disk space usage for the temp_jp table and it’s index.

18-A

After the move operation, one extent was allocated to the table and two for the index.

18-B

After the shrink operation, the table and index are allocated one extent each of 8 Oracle blocks. The initial two extents for the index had come down to one.
19 Displayed the spread of rows within Oracle blocks of temp_jp table.

19-A

All five rows in temp_jp table are compacted into one Oracle block.

19-B

All five rows in temp_jp table are spread into three Oracle blocks.
20 20-AA

Table analyze operation failed, with the error ORA-01502

20-AB

Rebuilt the index on temp_jp table to validate it.

After the index rebuild operation, one Oracle extent was allocated to the index.

20-AC

After index rebuild the table is analyzed.

20-B

Table is analyzed.
21 Select the number of rows and row chaining of temp_jp table.

21-A

After move operation on temp_jp table, row chaining was resolved. 0 rows chained.

21-B

After shrink operation on temp_jp table, row chaining was not resolved. 2 rows chained.
22 Verify the status of the index on temp_jp table is valid.

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles