Databases Reference
In-Depth Information
How it works...
We have created the table
BIG_ROWS
in which row length is smaller than 8 KB, the DB block
size value for the tablespace
example
of
TESTDB
, but whose length can grow from 2 to 7 KB
depending on whether
FIELD2
,
FIELD3
, and
FIELD4
are null or not.
The size in the previous example is calculated for single-byte
charactersets; if we use multi-byte charactersets the space
needed by a single character is more than 1 byte.
We have populated this table with 100 rows keeping the latter fields null. After analyzing the
table, we know that there aren't any chained/migrated rows.
A migrated row occurs when a row is updated but does not fit into the original DB block, due
to its new size. Thus, the database engine stores a pointer in the original position of the row,
which points to the new position of the row (hence "migrated").
Using the pointer allows us to avoid invalidating the indexes, because the original
ROWID
is kept. The database stores the row header in the original place, pointing to the new block
where the row is entirely stored.
To prevent row migration, we have to set a higher value for the table
PCTFREE
storage
parameters, to allow more space in the block, which is free for subsequent updates of
the rows.
In the example, the table has a value of
PCTFREE
set to
10
, meaning that the database
will keep 10 percent of the DB block free to allow row growth due to subsequent updates.
To solve the migrated row issue once it's in place, we have to list the migrated rows in a
(temporary) table, delete them from the original place, disable foreign key constraints and
triggers if they exist, to avoid violating them, and insert them back from the temporary table
to the original table. This is what we have done in the last part of the example, verifying that
after such a move row migration has disappeared.
There's more...
Row migration, similar to row chaining, causes poor performance because the database
engine must read more than one block in order to access to the row data.
The biggest difference between row chaining and row migration is that row chaining highlights
a bad row design or a small DB block size, while row migration indicates that a very small
PCTFREE
parameter has been used for a table.
