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

avg(vsize(<long column>)) will not work on a long raw, and the db block size is the same on both DB's.(4k)
I'm at a loss as to why the 7.3.3 table increases the chained row count no matter what the settings, while the same dataset on the 8.1.7 decreases when dropped & re imported.

I've tried changing the pctfree and pctused to 40 & 60, 10 & 60, 5 & 60, 10 & 75 on each database, on the 7.3.3 the chain count stays high or even increases and on the 8.1.7 it stays reasonably low.

I know I'm on an unsupported version, I plan on upgrading as soon as our new hardware arrives, and the client app supports it, currently 8.0.5 is the highest version it supports.

thanks

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:996523771.13927.0.pluto.d4ee154e_at_news.demon.nl...
>
> "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 Thu Aug 02 2001 - 17:21:45 CDT