How to 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.

1.  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 USERS PCTFREE 10;

2.  Populate the table:

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

3.  Analyze the table to refresh the statistics:

ANALYZE TABLE HR.BIG_ROWS COMPUTE STATISTICS;

4.  Verify if there are migrated rows:

SELECT CHAIN_CNT FROM ALL_TABLES 

WHERE OWNER = 'HR' AND TABLE_NAME = 'BIG_ROWS';

5.  In the next screenshot, we can see the results of these operations:

6.  Update some data in the table:

UPDATE HR.BIG_ROWS SET field2 = 'B', field3 = 'C', field4 = 'D' WHERE MOD(id, 2) = 1;

7.  Analyze the table to refresh the statistics:

ANALYZE TABLE HR.BIG_ROWS COMPUTE STATISTICS;

8.  Verify if there are migrated rows:

SELECT CHAIN_CNT FROM ALL_TABLES 

WHERE OWNER = 'HR' AND TABLE_NAME = 'BIG_ROWS';

9. Create table CHAINED_ROWS:

create table HR.CHAINED_ROWS (

owner_name varchar2(30),

table_name varchar2(30),

cluster_name varchar2(30),

partition_name varchar2(30),

subpartition_name varchar2(30),

head_rowid rowid, 

analyze_timestamp date);

10. Analyze the table to list the migrated rows:

ANALYZE TABLE HR.BIG_ROWS LIST CHAINED ROWS INTO HR.CHAINED_ROWS;

11. Count (or list) the migrated rows:

SELECT COUNT(*) FROM HR.CHAINED_ROWS;

12. In the next screenshot, we can see the results of these operations:

13. Create a temporary table, which is an empty copy of BIG_ROWS:

CREATE GLOBAL TEMPORARY TABLE HR.TEMP_BIG_ROWS as select * FROM HR.BIG_ROWS WHERE 1=0;

14. Copy the migrated rows to the temporary table:

INSERT INTO TEMP_BIG_ROWS SELECT B.* FROM HR.BIG_ROWS B, HR.CHAINED_ROWS T WHERE T.OWNER_NAME = 'HR' AND T.TABLE_NAME = 'BIG_ROWS' AND T.HEAD_ROWID = B.ROWID;

15. Delete the migrated rows from the BIG_ROWS table:

DELETE FROM HR.BIG_ROWS B WHERE EXISTS (SELECT T.ROWID FROM HR.CHAINED_ROWS T WHERE T.OWNER_NAME = 'HR' AND T.TABLE_NAME = 'BIG_ROWS' AND T.HEAD_ROWID = B.ROWID);

17.  Copy the migrated rows from the temporary table back to the BIG_ROWS table:

INSERT INTO HR.BIG_ROWS SELECT * FROM HR.TEMP_BIG_ROWS;

17. Analyze the table to refresh the statistics:

ANALYZE TABLE HR.BIG_ROWS COMPUTE STATISTICS;

18. Verify if there are migrated rows:

SELECT CHAIN_CNT FROM ALL_TABLES 

WHERE OWNER = 'HR' AND TABLE_NAME = 'BIG_ROWS';

19. In the next screenshot, we can see the results of these operations:

20. Drop the tables used for testing:

DROP TABLE HR.TEMP_BIG_ROWS;

DROP TABLE HR.CHAINED_ROWS;

DROP TABLE HR.BIG_ROWS;