Databases Reference
In-Depth Information
How it works...
We have created the table
BIG_ROWS
in which row length is greater than 8 Kbytes, the DB
block size value for the tablespace
EXAMPLE
of
TESTDB
.
We have populated this table with 100 rows, and after analyzing the table we know that
there are 100 chained rows. A chained row is a row which cannot fit into one DB block,
due to its size (when inserted or updated). So the database engine stores the initial part
of the row in a DB block, which is chained to another DB block where the remaining part
of the row content is stored.
To avoid chained rows, we can move the table to a tablespace with a greater DB block size;
we have created the tablespace
TS_16K
with a block size of 16K, greater than the average
row length of the
BIG_ROWS
table.
We have moved the
BIG_ROWS
table to the newly created tablespace and rebuilt the
primary key index—which is marked unusable after the move. We then analyzed the
table again to refresh the statistics.
After the move, the chained rows have disappeared from the
BIG_ROWS
table.
There's more...
We can use different block sizes in the Oracle database, but every tablespace can have only
one block size. Before adding a tablespace with a different DB block size, we have to make
room in the database buffer cache to store DB blocks of every size, as we have done in the
Getting
ready
section of this recipe.
As stated earlier, row chaining occurs when the database block size isn't big enough to store
a row entirely. In such cases, the only solution to avoid row chaining is to move the table to a
tablespace with a bigger DB block size, as we have done.
After moving the table, we had to rebuild the index. Why?
The answer is simple. An index contains the ROWIDs of the table
rows, and the ROWIDs identify the position of the row, madeup
by the object, the datafile, the block number, and the slot (row)
number. When we move a table, the datafile and the block number
change, so the indexes have to be rebuilt.
