Oracle row Migration & Row link detection and elimination

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>

The third method to eliminate row migration:

-- Clear the test environment SQL> @ clear_employees.sqlTable truncated. table dropped. commit complete. SQL> -- simulate row migration SQL> @ reset_employees.sqlTable created. table altered. table altered. table altered. table altered.107 rows updated. SQL> commit; Commit complete. SQL> -- EXP export test table EMPLOYEES [oracle @ Server ~] $ Exp SCOTT tables = employees file = scott_employees.dmpExport: Release 11.2.0.3.0-Production on Wed Aug 14 20:03:05 2013 Copyright (c) 1982,201 1, Oracle and/or its affiliates. all rights reserved. password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in ZHS16GBK chara Cter set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path... exporting table EMPLOYEES 107 rows exportedExport terminated tables without warnings. [oracle @ Server ~] $ -- Delete the test table EMPLOYEESSQL> drop Table employees purge; table dropped. SQL> commit; Commit complete. SQL> -- IMP import the test Table EMPLOYEES [oracle @ Server ~] $ Imp SCOTT tables = employees file = scott_employees.dmpImport: Release 11.2.0.3.0-Production on Wed Aug 14 20:05:25 2013 Copyright (c) 1982,201 1, Oracle and/or its affiliates. all rights reserved. password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT: V11.02.00 via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character set. importing SCOTT's objects into SCOTT. importing SCOTT's objects into SCOTT .. importing table "EMPLOYEES" 107 rows importedImport terminated successfully without warnings. [oracle @ Server ~] $ -- 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 fourth method to eliminate row migration

-- Clear the test environment SQL> @ clear_employees.sqlTable truncated. table dropped. commit complete. SQL> -- simulate row migration SQL> @ reset_employees.sqlTable created. table altered. table altered. table altered. table altered.107 rows updated. SQL> commit; Commit complete. SQL> -- check that the table has no index SQL> select table_name, index_name from user_indexes where table_name = 'ployees '; no rows selectedSQL> -- migrate the test table EMPLOYEES to the tablespace TBS_16SQL> alter Table employees move tablespace tbs_16; table altered. SQL> commit; Commit complete. SQL> -- Moving a table changes the rowids of the rows in the table. -- This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. -- The indexes on the table must be dropped or rebuilt. -- Likewise, any statistics for the table become invalid and new statistics shocould be collected after moving the table. -- analyze the 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>

Row link:

# You can eliminate chained rows only by increasing your data block size. # It might not be possible to avoid chaining in all situations.

# Chaining is often unavoidable with tables that have a LONG column or large CHAR or VARCHAR2 columns.

# Line links are inevitable unless you have enough data blocks to hold down records.

Let's look at an example:

-- The test table EMPLOYEES is currently in the Block_Size = 16 K tablespace. SQL> select. table_name,. tablespace_name, B. block_size from user_tables a join dba_tablespaces B on. tablespace_name = B. tablespace_name where. table_name = 'ployees'; TABLE_NAME TABLESPACE_NAME BLOCK_SIZE tables ---------- EMPLOYEES TEST16K 16384SQL> -- analysis test table EMPLOYEESSQL> analyze Table EMPLOYEES list chained rows into tables; table analyzed. SQL> -- Query: no link is available on the current test table EMPLOYEES. SQL> select count (*) from chained_rows where table_name = 'Employees '; COUNT (*) ---------- 0SQL> -- move the test table EMPLOYEES to the tablespace with Block_Size = 8 K. SQL> select tablespace_name, block_size from dba_tablespaces where tablespace_name = 'users '; TABLESPACE_NAME BLOCK_SIZE ---------------------------- ---------- USERS 8192SQL> alter table employees move tablespace users; Table altered. SQL> -- analysis test table EMPLOYEESSQL> analyze Table employees list chained rows into chained_rows; table analyzed. SQL> -- the query shows that the row link SQL> select count (*) from chained_rows where table_name = 'ployees '; COUNT (*) is generated (*) ---------- migrate SQL> -- migrate the test table EMPLOYEES back to the TEST16K tablespace SQL> alter Table employees move tablespace test16k; table altered. SQL> -- clear the Chained_rows table SQL> delete from chained_rows where table_name = 'ployees'; 107 rows deleted. SQL> -- analysis test table EMPLOYEESSQL> analyze Table employees list chained rows into chained_rows; table analyzed. SQL> -- Query That the row link has been cleared SQL> select count (*) from chained_rows where table_name = 'ployees '; COUNT (*) ---------- 0SQL>

THE END

This article is from the "Xin23 journal account" blog and will not be reproduced!