Re: Wanted: Chained Rows

On Sat, 22 Jan 2005 12:50:19 +0000, Andy Hassall <andy_at_andyh.co.uk> wrote:

>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
>----------
> 1
>
> If you want to see chained rows distinct from migrated rows, use a different
>method:
>
>SQL> @?/rdbms/admin/utlchain.sql

 Actually, don't. On further examination this produces the rowids of both chained and migrated rows.

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