9.3 How do I…Correct row migration in tables?

Problem

I have used the techniques in How-To 9.2 to analyze some database tables and have discovered that some of the tables in my database do indeed contain chained or migrated rows. How can I correct this state so that each table row resides entirely in a single block?

Technique

Using the sample tables from How-To 9.2, including the CHAINED_ROWS table, move the table rows in question to a temporary table, delete them from the original table, and copy them back to the original table from the temporary table.

Steps

1. Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the CHP9_3.SQL script and the CHP9_4.SQL script. See the previous How-To for an in-depth explanation of the SQL statements in these scripts.

2. While still in SQL*Plus, issue these two START commands to execute the CHP9_5 script for the CHAIN09 and MIGRATE09 tables: start chp9_5 migrate09 and start chp9_5 chain09. This CHP9_5 script appears in Listing 9.2.

Listing 9.2 The CHP9_5 script attempts to correct row migration

1 DROP TABLE temp;

2 --

3 CREATE TABLE temp AS

4 SELECT * FROM &1

5 WHERE rowid IN

6 (SELECT head_rowid FROM chained_rows

7 WHERE table_name = upper('&1'));

8 --

9 DELETE FROM &1

10 WHERE rowid IN

11 (SELECT head_rowid FROM chained_rows

12 WHERE table_name = upper('&1'));

13 --

14 INSERT INTO &1

15* SELECT * FROM temp;

The script makes multiple use of a single substitution variable that contains the name of the table containing the migrated rows. You’ll call this the target table for the remainder of this discussion. The SQL function UPPER appears throughout the script to convert the target table name to uppercase. This is necessary if the user invokes this script with the table name in lowercase because the CHAINED_ROWS table stores the table name in uppercase.

Line 1 drops the table TEMP created by previous invocations of this script. Lines 3 through 8 recreate the TEMP table and populate it with any rows from the target table whose ROWIDs appear in the CHAINED_ROWS table. Lines 9 through 12 delete these same rows from the target table. Lines 14 and 15, finally, insert the problematic rows back into the target table.

3. While still in SQL*Plus, use the START command to load and execute the script CHP9_4.SQL. This script analyzes the CHAIN09 and MIGRATE09 tables again to help determine what effect, if any, the preceding steps had on these tables.

How It Works

Step 1 recreates the CHAIN09 and MIGRATE09 tables from the previous How-To. Step 2 attempts to correct row migration by essentially giving the Oracle8 server another chance to insert the offending rows, identified in the CHAINED_ROWS table, back into the target table. Step 3 analyzes the two sample tables after the curative measures applied in step 2. The results of the re-analysis of the two sample tables appear in Figure 9.7.

Notice that the CHAIN09 table is still listed in the CHAINED_ROWS table. Why? The answer is that even after the steps in this How-To gave Oracle8 another opportunity to insert this problematic row from the CHAIN09 table, the row still occupies more than one data block. Oracle8 did not migrate this row because of an update statement like the row from the MIGRATE09 table. Oracle8 chained this row because it contains 4000 bytes of data and the database block size is only 2048 bytes. The row is too large to fit in any database block; the DBA can move it to another table and re-insert it in the target table until the Oracle9 release date, and the row will still be chained.

Comments

Row migration can contribute to degraded database performance, but it is often resolvable using the preceding techniques. If it is not feasible to create a table to temporarily store the migrated rows, as in this How-To, then the export and import utilities can help resolve row migration problems. After the DBA resolves migrated row difficulties with one of these techniques, an increase in the PCTFREE parameter can decrease migration frequency in the future. (Recall that the value of this parameter is equal to the amount of database block space, expressed as a percent, that Oracle8 attempts to keep free for future updates.)

Row chaining is more problematic because the only way to address it is usually to rebuild the database with a larger database block size. This intricate process requires a full database export, database recreation, and database import. Moreover, if the database’s primary function is online transaction processing, a larger block size can impede database operation. This performance degradation could more than offset any gains arising from elimination of row chaining. In general, this discussion emphasizes the need for benchmarking in a test environment to identify and assess unintended side effects of database parameter modifications.