Avoiding row chaining

We encounter row chaining when the size of the row data is larger than the size of the database block used to store it. In this situation, the row is split across more than one database block, so, to read it we need to access more than one database block, resulting in greater I/O.

Getting ready

Before we can start, we have to alter an initialization parameter of the test database (assuming the default block size is 8KB in the test database):

ALTER SYSTEM SET db_16k_cache_size = 16m scope=both;

We need to set this parameter to allocate a memory buffer dedicated to storing database blocks of a different size; in this recipe, we will create a tablespace using a 16KB block size, so we need the corresponding buffer allocated to use it.

How to do it...

In this recipe, we will examine how to detect row chaining issues, and how to avoid chaining in our tables. Follow these steps:

1. Connect to the HR schema:

   CONNECT hr@TESTDB/hr
   

2. Create the table BIG_ROWS:

   CREATE TABLE HR.BIG_ROWS ...