A Fact About Row Migration
All of you know about row chaining and row migration in Oracle RDBMS.
You will see plenty of examples and explanation for row migration and row chaining.
The question I have is:
When will oracle put back already migrated row into its original block?
Let me explain a bit more about row migration.
Assume a row is stored in block number 10. A transaction updates the row, the row gets expanded, and
oracle sees no free space to accommodate the updated rows, so it puts the row in another block (say block
11) and adds a pointer to its original block 10. Technically, the row now sits in block number 11.
Later some time, another transaction updates the row (that has been migrated), and the row expands further
and assume that there is no free space in block number 11 to accommodate the updated row. At this time,
oracle assigns a block from the freelist to store this updated row. If this block happens to be the original block
because of some deletion happened in the past, then Oracle will use it provided the transaction freelist gets
the block from the master freelist. Then you will see decreased value in CHAIN_CNT column in the
USER_TABLES view.
Let me demonstrate this:
SQL> create table t1
(id int, a char(1900), b char(1900),
c char(1900), d char(1900))
pctfree 0 ;
Table created.
SQL> insert into t1 (id,a,b) values (1,'x','x');
1 row created.
SQL> insert into t1 (id,a,b) values (2,'x','x');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select table_name, chain_cnt, blocks, empty_blocks
from user_tables
where table_name = 'T1' ;
TABLE_NAME CHAIN_CNT BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1 0 1 3
SQL> insert into t1 (id,a) values (3,'x');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table t1 compute statistics
/
Table analyzed.
SQL> select table_name, chain_cnt, blocks, empty_blocks
from user_tables
where table_name = 'T1'
/
TABLE_NAME CHAIN_CNT BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1 0 2 2
Now 2 blocks are used to store 3 rows.
SQL> select dbms_rowid.rowid_block_number(rowid) from t1 ;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
4328
4328
4329
The newly assigned block 4329 has used space of around 1902 bytes.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select table_name, chain_cnt, blocks, empty_blocks
from user_tables
where table_name = 'T1'
/
TABLE_NAME CHAIN_CNT BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1 0 2 2
Now I increase the row length (for id=1) by another 1900 byets.
SQL> update t1 set c='x' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select table_name, chain_cnt, blocks, empty_blocks
from user_tables
where table_name = 'T1'
/
TABLE_NAME CHAIN_CNT BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1 1 2 2
Since the block 4329 has enough space, the row (for id =1) is
stored in 4329 block.
SQL> select dbms_rowid.rowid_block_number(rowid), id from t1 ;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID
------------------------------------ ----------
4328 2
4329 3
4328 1
SQL> delete t1 where id =2;
1 row deleted.
SQL> commit;
Commit complete.
After the row (for id =2) is deleted, the block 4328 has around 8000 bytes free space, but still the block 4328
has its original rowid for the id=1.
SQL> select dbms_rowid.rowid_block_number(rowid), id from t1 ;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID
------------------------------------ ----------
4329 3
4328 1
SQL> analyze table t1 compute statistics ;
Table analyzed.
SQL> select table_name, chain_cnt, blocks, empty_blocks
from user_tables where table_name = 'T1' ;
TABLE_NAME CHAIN_CNT BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1 1 2 2
And oracle also reported that there is one migrated row which is this case for row (id=1).
Now increase the row length by another 1900 bytes for the row(id=1).
and let us see what happens to the original block 4328
SQL> update t1 set d='x' where id=1 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select table_name, chain_cnt, blocks, empty_blocks
from user_tables where table_name = 'T1' ;
TABLE_NAME CHAIN_CNT BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1 0 2 2
Note Chain_cnt has column zero value.
The above tiny example demonstrated clearly one point:
After a row is migrated to another block, and oracle finds no free space in the migrated block to accommodate
further increase in the same row, and at the same time the original block has enough space, then oracle may
use the original block for the updated row. Please underline the word “may use”. The reason is: in the above
example, I used one freelist and one freelist group for the table. So the freed block 4328 went back to master
free list after a row was deleted. And subsequently it was reused because all types of freelists are
implemented as a single linked list using Last-In-First-Out(LIFO) mechanism. If there are multiple freelists or
more than one free list group, then oracle may/may not use the original block for the updated row because of
the hash function used to arrive process freelist.
The vice versa is NOT true. That is, if a migrated row’s size is decreased again by update, and at the same
time its original block has free space to accommodate the updated row, even then oracle will NOT put back
the row into its original block.
Cheers, Tamil
No comments:
Post a Comment