Row chaining

[sri_purna]

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

[agasimani]

Why dont you create a datbase with a large block size, say 16K and try. Also experiment with pctfree and pctused.

[ggnanaraj]

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.

[sri_purna]

Thanks

[quester]

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

[pando]

quester you cannot avoid chaining caused by very long rows by recreating the table, only wayis use a bigger block size

[quester]

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.

[jmodic]

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.

[quester]

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.