Row chaining
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Row chaining

  1. #1
    Join Date
    Jul 2000
    Posts
    9

    Row chaining

    hi ,
    I have three table with Long Raw data type columns. All three table got Excessive row chaining. More than 50% of rows are chained
    /migrated.How to solve this problem. I tried to create the duplicate tables and tried to copy the tables but failed . How to solve this problem. Is there any way to ct=reate same table with different name like "Create table as(select * from ) with long raw data types"

    Please help me

    Thanks
    Sam

  2. #2
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    Why dont you create a datbase with a large block size, say 16K and try. Also experiment with pctfree and pctused.
    Agasimani
    OCP(10g/9i/8i/8)

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    To Reducing Migrated and Chained Rows...
    http://technet.oracle.com/docs/produ...20_io.htm#3552

    It may not be possible to avoid chaining in all situations. Chaining is often unavoidable with tables that have a LONG column or long CHAR or VARCHAR2 columns.

  4. #4
    Join Date
    Jul 2000
    Posts
    9
    Thanks

  5. #5
    Join Date
    Mar 2002
    Posts
    200
    Solving Row-Chaining for tables with LONG columns
    ====================================================

    The only option is export the data into another temporary table. TRUNCATE the table, ALTER the table to change LONG to VARCHAR2 column and re-populate the data back into the table. Tables with LONG columns cannot be data-cloned to another table. The following procedure gives the workaround for this:

    declare
    w_string varchar2(4000);
    cursor Cur1 is
    select C1 from X;
    begin
    open Cur1; loop
    fetch Cur1 into w_string;
    if Cur1%notfound then close Cur1; exit; end if;
    insert into T1 values (w_string);
    commit;
    end loop;
    end;
    /

    Quester

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    quester you cannot avoid chaining caused by very long rows by recreating the table, only wayis use a bigger block size

  7. #7
    Join Date
    Mar 2002
    Posts
    200
    Originally posted by pando
    quester you cannot avoid chaining caused by very long rows by recreating the table, only wayis use a bigger block size
    You are right Sir; but if the LONG column can consist of data always < 4000 characters, then my above said work-around can be implemented to eradicate the issue.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by quester
    You are right Sir; but if the LONG column can consist of data always < 4000 characters, then my above said work-around can be implemented to eradicate the issue.
    The above method is totaly useless. You are suggesting that instead of one LONG column that for example stores 20 KB of data, we should created the table with 5 VARCHAR2 columns to store that same data. But how will this solve row chaining isue??? The same row will still need to be chained into 5 or more blocks if your blocksize is 4K, for example. So this method solves nothing, it merely complicates thing further. Besides, the original poster said he has LONGRAW column, so replaicing this with VARCHAR2 columns is even more useless...

    The only real solution is to convert LONG and LONGRAWs into LOBs and store them in a separate segment from the core table data.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Mar 2002
    Posts
    200
    Originally posted by jmodic
    The above method is totaly useless. You are suggesting that instead of one LONG column that for example stores 20 KB of data, we should created the table with 5 VARCHAR2 columns to store that same data. But how will this solve row chaining isue??? The same row will still need to be chained into 5 or more blocks if your blocksize is 4K, for example. So this method solves nothing, it merely complicates thing further. Besides, the original poster said he has LONGRAW column, so replaicing this with VARCHAR2 columns is even more useless...

    The only real solution is to convert LONG and LONGRAWs into LOBs and store them in a separate segment from the core table data.
    Indeed I did not see these complexities. Never used LOB, think that's the most practical approach. Thanks for the corrections.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width