Re: Import & export caused more chained rows....

"Glen A Stromquist" <stromqgl_at_alpac.ca> wrote in message news:BKi97.10104$b_3.1184616_at_news0.telusplanet.net...
> I have a table with almost 600,000 rows, and showed a high number of
 chained
> rows. Database is 7.3.3 running on NT. There is a long raw as the last
> column in the database, when looking at the dataset through TOAD it shows
 up
> as (BLOB).
>
> I exported the table, then did a drop table cascade constraints with it.
> Rebuilt the table with PCTFREE increased from 10 to 20. Imported the data
 ,
> the rebuilt the indexes and constraints. Still showed a high number of
> chained rows, but the chained fetch ratio in TOAD server stats was way
 down
> and the warning gone. So.... blew the table away again, increased the
> PCTFREE to 40, did the whole thing over again, now I'm showing even more
> chained rows and my chained fetch ratio is way up, along with the "ptcfree
> to low for a table" warning. There are no other tables with a high chained
> row count.
>
> Funny thing is, I have a clone of the DB running on 8.1.7 on Win2000, when
 I
> dropped the table in that database and re imported with a pctfree of 20
 from
> 10 the chained rows all but went away. Why the difference?
>
> A colleague suggested that I set the PCTfree to a real low number for the
> import (5,10), do the import, then alter the table structure and increase
 it
> to a much higher number, (40 -60). I will try this but am open to any
 other
> suggestions others may have.
>
>
> thanks in advance
>
>

Can you check the avg(vsize(<long column>)) for that table? Chances are it is bigger than your database block size, so whatever you do, you won't resolve the chaining problem without recreating the database with a bigger blocksize.
Chances are also the block size on the two databases you try to compare is not identical.
You are aware you're running a desupported version?

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Mon Jul 30 2001 - 15:09:27 CDT