Re: Wanted: Chained Rows

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Sat, 22 Jan 2005 12:50:19 +0000
Message-ID: <57i4v0pof2u3s2bhg0194n5mlr4cg1gv2q@4ax.com>

On Sat, 22 Jan 2005 12:18:42 +0000, Igor Racic <igor.racicC_at_freesurf.fr_no_C> wrote:

>DA Morgan wrote:
>> Would anyone have, perchance, a simple demo that intentionally creates
>> chained rows that will be found by the ANALYZE TABLE or ANALYZE CLUSTER
>> utility? The block size is 8K.
>>
>> Please email to me and I will name names, if you wish, on the PSOUG web
>> site.
>
>SQL> create table t ( a1 varchar2( 4000 ) );
>
>Table created.
>
>SQL> begin
> 2 for i in 1..4
> 3 loop
> 4 insert into t values ( '1' );
> 5 end loop;
> 6 end;
> 7 /
>
>PL/SQL procedure successfully completed.
>
>SQL> commit;
>
>Commit complete.

[...]
>SQL> update t
> 2 set a1 = rpad ( 'a', 4000, 'B' );
>
>4 rows updated.

[...]
>SQL> select CHAIN_CNT from user_tables where table_name = 'T';
>
> CHAIN_CNT
>----------
> 2
>
>1 row selected.

That's not row chaining, that's row migration. USER_TABLES.CHAIN_CNT shows both chained and migrated rows.

The manual says:

"Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID" You had 4 rows there with a 1 character VARCHAR2, then you updated the size of each to 4000 characters. For an 8k block size, at least two rows will have to migrate, since you've got 16000 bytes of data plus headers and other overhead. The rows are moved to new block(s), leaving behind a pointer so the old ROWID is still valid. This is row migration.

Row chaining is where a single row cannot fit in a single block. The trivial example is to create a row larger than your block size; chaining inevitably occurs.

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

SQL> create table t (c1 varchar2(4000), c2 varchar2(4000));

Table created.

SQL> insert into t values (lpad('x',4000,'x'), lpad('x',4000,'x'));

1 row created.

SQL> commit;

Commit complete.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T';

CHAIN_CNT

If you want to see chained rows distinct from migrated rows, use a different method:

SQL> @?/rdbms/admin/utlchain.sql

Table created.

SQL> analyze table t list chained rows;

Table analyzed.

SQL> select * from chained_rows;

OWNER_NAME                     TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME                   PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_T
------------------------------ ------------------ ---------
TEST                           T

N/A                            AAAJ3MAAEAAAADnAAA 22-JAN-05

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Sat Jan 22 2005 - 06:50:19 CST