Defragging Non-partitioned Tables

To defrag a table or eliminate chained rows, you normally export the table, drop the table, and import the table. You can also move a table from one tablespace to another to defrag and eliminate row chaining. The following example shows the table EMP_CHAINED_ROWS with 22 chained rows, in tablespace USERS, with one index INDX_SAL. The table is moved from tablespace USERS to DATA, and the index must be rebuilt. The following query shows there are 22 rows chained in the table EMP_CHAINED_ROWS.

SQL> SELECT CHAIN_CNT, TABLESPACE_NAME

  2  FROM   USER_TABLES

  3* WHERE  TABLE_NAME = 'EMP_CHAINED_ROWS';

CHAIN_CNT  TABLESPACE_NAME

---------- ---------------

22         USERS

To determine the indexes on table EMP_CHAINED_ROWS table, use the following query. When you defrag a table using the ALTER MOVE command, indexes on the non-partitioned table must be rebuilt.

SQL> SELECT INDEX_NAME, TABLESPACE_NAME

  2  FROM   USER_INDEXES

  3* WHERE  TABLE_NAME = 'EMP_CHAINED_ROWS';

INDEX_NAME TABLESPACE_NAME

---------- ---------------

INDX_SAL   INDX

Move the table, EMP_CHAINED_ROWS, from the USERS tablespace to the DATA tablespace and also defrag the EMP_CHAINED_ROWS table:

SQL> ALTER TABLE EMP_CHAINED_ROWS MOVE TABLESPACE DATA;

Table altered.

You can also enter the following command if you want to defrag the table, but keep the table in its current tablespace.

SQL> ALTER TABLE EMP_CHAINED_ROWS MOVE;

Because an index exists on the EMP_CHAINED_ROWS table, and the table is moved to another tablespace, when the index is used, you receive an error.

SQL> SELECT *

  2  FROM   EMP_CHAINED_ROWS

  3  WHERE  SAL = 5000;

     FROM   EMP_CHAINED_ROWS

             *

ERROR at line 2:

ORA-01502: index 'SYSTEM.INDX_SAL' or partition of such index is

   in unusable state

Now, you must rebuild the index. In Oracle8i, you can use the ONLINE option, allowing transactions to update the underlying table of the index while the index is being rebuilt.

SQL> ALTER INDEX INDX_SAL REBUILD TABLESPACE INDX ONLINE;

Index altered.

Reanalyze the table to determine if the chained rows are repaired.

SQL> ANALYZE TABLE EMP_CHAINED_ROWS COMPUTE STATISTICS;

Table analyzed.

Because the following query shows a chain count of zero, row chaining no longer exists in EMP_CHAINED_ROWS. It's more than about time!

SQL> SELECT CHAIN_CNT, TABLESPACE_NAME

  2  FROM   USER_TABLES

  3* WHERE  TABLE_NAME = 'EMP_CHAINED_ROWS';

CHAIN_CNT  TABLESPACE_NAME

---------  ---------------

        0  DATA

---

The above text is an excerpt from: Oracle SQL Tuning & CBO Internals ISBN 0-9745993-3-6 by Kimberly Floss