Avoiding row migration

When we update a row and it does not fit entirely within the original database block due to the corresponding growth in size, we have a row migration. In the original place (where the row was stored) we have placed a pointer to the new location of the row.

How to do it...

In this recipe, we will see how to detect row migration issues, and how to avoid migrating rows in our tables. Follow these steps:

1. Connect to HR schema:

   CONNECT hr@TESTDB/hr
   

2. Create the table BIG_ROWS:

   CREATE TABLE HR.BIG_ROWS (
     id number NOT NULL,
     field1 char(2000) DEFAULT 'A' NOT NULL,
     field2 char(2000),
     field3 char(2000),
     field4 char(1000),
     constraint PK_BIG_ROWS primary key (ID))
   TABLESPACE EXAMPLE PCTFREE 10;
   

3. Populate the table:

   INSERT INTO HR.BIG_ROWS (id)
   select rownum from all_objects where rownum < 101;
   

4. Analyze the table to refresh the statistics:

   ANALYZE TABLE HR.BIG_ROWS COMPUTE STATISTICS;
   

5. Verify if there are migrated rows:

   SELECT CHAIN_CNT FROM ALL_TABLES
   WHERE OWNER = ‹HR...