Oracle row Migration & Row link detection and elimination

What is a row migration row link? # The following description is from Oracle11gR2Document1. The row link Therowistoolargetofitintoonedatablockwhenitisfirstinserted. Inrowchaining, OracleDatabasestor

What is a row migration row link? # The following description is from Oracle11gR2Document1. The row link Therowistoolargetofitintoonedatablockwhenitisfirstinserted. Inrowchaining, OracleDatabasestor

What is row Migration & Row link?

# The following description is from Oracle 11gR2 Document

1. Line Link

The row is too large to fit into one data block when it is first inserted.

In row chaining, Oracle Database stores the data for the row in a chain of one or more data blocks reserved for the segment. row chaining most often occurs with large rows. examples include rows that contain a column of data type LONG or long raw, a VARCHAR2 (4000) column in a 2 KB block, or a row with a huge number of columns. row chaining in these cases is unavoidable.


2. Row migration

A row that originally fit into one data block is updated so that the overall row length increases, but insufficient free space exists to hold the updated row.

In row migration, Oracle Database moves the entire row to a new data block, assuming the row can fit in a new block. the original row piece of a migrated row contains a pointer or "forwarding address" to the new block containing the migrated row. the rowid of a migrated row does not change.

The topic of this article is to eliminate row migration. In this case, we must first simulate the travel migration:

-- Create a chained_rows table SQL> @ $ ORACLE_HOME/rdbms/admin/utlchain. sqlTable created. SQL> -- CREATE test TABLE EMPLOYEESSQL> CREATE TABLE EMPLOYEES TABLESPACE TEST16K PCTFREE 0 AS SELECT * FROM HR. EMPLOYEES; Table created. SQL> COMMIT; Commit complete. SQL> -- analysis test table EMPLOYEESSQL> analyze Table employees list chained rows into chained_rows; table analyzed. SQL> -- Query: the current test table EMPLOYEES does not have row migration SQL> select count (*) from chained_rows where table_name = 'ployees'; COUNT (*) ---------- 0SQL> -- Update the test table EMPLOYEES structure SQL> alter Table employees modify FIRST_NAME varchar2 (2000); table altered. SQL & gt; alter table employees modify LAST_NAME varchar2 (2000); Table altered. SQL> alter table employees modify EMAIL varchar2 (2000); Table altered. SQL> alter table employees modify PHONE_NUMBER varchar2 (2000); Table altered. SQL> -- update test table EMPLOYEESSQL> update employees set FIRST_NAME = LPAD ('1', 2000, '*'), LAST_NAME = LPAD ('1', 2000 ,'*'), EMAIL = LPAD ('1', 2000, '*'), PHONE_NUMBER = LPAD ('1', 2000, '*'); 107 rows updated. SQL> commit; Commit complete. SQL> -- analysis test table EMPLOYEESSQL> analyze Table employees list chained rows into chained_rows; table analyzed. SQL> -- Query: the row migration SQL> select count (*) from chained_rows where table_name = 'ployees '; COUNT (*) ---------- explain SQL> -- the row migration simulation is successful, I save this process AS a script reset_employees. SQL: CREATE TABLE EMPLOYEES TABLESPACE TEST16K PCTFREE 0 AS SELECT * FROM HR. EMPLOYEES; alter table employees modify FIRST_NAME varchar2 (2000); alter table employees modify LAST_NAME varchar2 (2000); alter table employees modify EMAIL varchar2 (2000 ); alter table employees modify PHONE_NUMBER varchar2 (2000); update employees set FIRST_NAME = LPAD ('1', 2000, '*'), LAST_NAME = LPAD ('1', 2000, '*'), EMAIL = LPAD ('1', 2000, '*'), PHONE_NUMBER = LPAD ('1', 2000 ,'*'); -- execute the script directly after simulating row migration.

The above generates a line migration simulation script

The first method to eliminate row migration:

-- Prepare the script Solution1.sqlCREATE TABLE employees_tmp tablespace test16k as select * FROM employees where rowid in (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'employees '); delete from employees where rowid in (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ployees'); insert into employees select * FROM EMPLOYEES_TMP; drop table EMPLOYEES_TMP; delete from CHAINED_ROWS WHERE TABLE_NAME = 'ployees'; COMMIT; -- execute the script Solution1.sqlSQL> @ Solution1.sqlTable created.106 rows deleted.106 rows created. table dropped.106 rows deleted. commit complete. SQL> -- analysis test table EMPLOYEESSQL> analyze Table employees list chained rows into chained_rows; table analyzed. SQL> -- Query knows that row migration has eliminated SQL> select count (*) from chained_rows where table_name = 'ployees'; COUNT (*) ---------- 0SQL>

The second method to eliminate row migration:

-- Clear the test environment SQL> truncate table chained_rows; Table truncated. SQL> drop table employees; Table dropped. SQL> commit; Commit complete. SQL> -- the preceding cleanup process is also saved as the script clear_employees. SQL: truncate table chained_rows; drop table employees; commit; -- This script will be used for subsequent cleanup operations -- simulate row migration SQL> @ reset_employees.sqlTable created. table altered. table altered. table altered. table altered.107 rows updated. SQL> commit; Commit complete. SQL> -- prepare script Solution2.sqlcreate table employees_tmp tablespace test16k as select * from employees; truncate table employees; insert into employees select * from employees_tmp; drop table employees_tmp; commit; -- execute the script Solution2.sqlSQL> @ Solution2.sqlTable created. table truncated.107 rows created. table dropped. commit complete. SQL> -- analysis test table EMPLOYEESSQL> analyze Table employees list chained rows into chained_rows; table analyzed. SQL> -- Query knows that row migration has eliminated SQL> select count (*) from chained_rows where table_name = 'ployees'; COUNT (*) ---------- 0SQL>