Row Chaining, Migrating, PCTFREE, PCTUSE

In this post I like to discuss several aspects of oracle data block. We all know that data block is the smallest unit of storage space managed by oracle database. Oracle requests data in multiples of Oracle data blocks.

Row Chaining
---------------------------
There may be the scenario that a row to be inserted is too large that can not fit into a single data block. Suppose standard data block is 8k and row size is larger that it. In this case, Oracle stores the data for the row in a chain of data blocks -one or more data blocks that are reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases can't be avoided.

Row Migrating
-----------------------------
The second case in which rows can not fit into a single case is the row is updated, the overall free space of the data block is fully filled up. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

Whenever row chaining or row migrating happens the I/O performance associated with the row is decreased as to retrieve the information of one row multiple data blocks need to be scanned.

PCTFREE parameter
-------------------------------------
The PCTFREE parameter specifies the minimum percentage of data block to be reserved as free space. If you used PCTFREE 20 then at least 20% size of data block will be reserved as free space. For example if you use data block size 8K (DB_BLOCK_SIZE=8192) then PCTFREE 20 will reserved 1638 bytes as free space in a data block.

This parameter is used to update to the existing rows already within each block.

You can specify this parameter which issuing CREATE TABLE statement.
PCTUSED Parameter
----------------------------------
This parameter specifies the minimum percentage of a block that can be used for row data plus overhead(data block header, table directory, and row directory) before new rows are added to the block.

After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls below the parameter PCTUSED.

If we set PCTUSED 40 in the CREATE TABLE statement then data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (Here the block's used space has previously reached PCTFREE).

Thus with the help of PCTFREE and PCTUSED oracle optimize the use of space in the data blocks of the extents within a data segment. For example, whether oracle will try to use the data block or not in order to insert a new row.