Andy Hassall 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
>
> 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
Thank you Andy and Igor for your suggestions. That is precisely
what I was looking for ... something I could give to my students
so they can build-them on demand.
Thanks again.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Received on Sun Jan 23 2005 - 17:54:46 CST
