Row Chaining

SQL> show parameter db_block_size

 NAME                                 TYPE        VALUE

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

db_block_size                        integer     8192

 

SQL> create table row_chain

          (col1 char(2000),col2 char(2000),col3 char(2000),col4 char(2000));

 Table created.

 

SQL> ANALYZE TABLE row_chain COMPUTE STATISTICS;

 Table analyzed.

 

SQL> select num_rows,chain_cnt

           from dba_tables

          where table_name='ROW_CHAIN';

 

  NUM_ROWS  CHAIN_CNT

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

0                    0

 

SQL> INSERT INTO row_chain values('a','b','c','d');

1 row created.

 

SQL> COMMIT;

Commit complete.

 

SQL> ANALYZE TABLE row_chain COMPUTE STATISTICS;

Table analyzed.

 

SQL> select num_rows,chain_cnt

           from dba_tables

           where table_name='ROW_CHAIN';

 

  NUM_ROWS  CHAIN_CNT

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

1                    1

 

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlchain.sql

 

SQL> ANALYZE TABLE row_chain LIST CHAINED ROWS;

Table analyzed.

 

SQL> SELECT owner_name,

         table_name,

         count(head_rowid) row_count

         FROM chained_rows

        GROUP BY owner_name,table_name;

 

OWNER_NAME                     TABLE_NAME                      ROW_COUNT

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

SYS                            ROW_MIG_CHAIN_DEMO                      3

 

SQL>Alter system set db_16K_cache_size=8m

 

SQL> create index i_row_chain on row_chain(col1);

 Index created.

 

SQL> create tablespace row_chain

  2  datafile 'C:\oracle\product\10.2.0\oradata\dba20\ROW_CHAIN_01.DBF'

  3  SIZE 3M BLOCKSIZE 16K;

 Tablespace created.

 

SQL> alter table row_chain move tablespace row_chain;

 Table altered.

 

SQL> ANALYZE TABLE row_chain COMPUTE STATISTICS;

ANALYZE TABLE row_chain COMPUTE STATISTICS

*

ERROR at line 1:

ORA-01502: index 'SYS.I_ROW_CHAIN' or partition of such index is in unusable

State

 

SQL> alter index i_row_chain rebuild;

 Index altered.

 

SQL> ANALYZE TABLE row_chain COMPUTE STATISTICS;

 Table analyzed.

 

 SQL> select num_rows,chain_cnt

              from dba_tables

               where table_name='ROW_CHAIN';

 

  NUM_ROWS  CHAIN_CNT

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

         1          0

Comments