Re: Chained Rows

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1078266089.98353_at_yasure>...
> Oliver Stratmann wrote:
> > Hello All,
> >
> > we have a table which has about 600.000 rows and the size of about 700
> > MByte.
> > This table was initialized first with not all columns being filled and
> > afterwards calculation-results
> > were added into empty columns.
> > Though before the table was reorganized by a MOVE and the PCTFREE-parameter
> > was set to 40
> > after the calculation 100% of the rows seem to be chained (result of
> > "ANALYZE TABLE T LIST CHAINED ROWS IN CHAINED_ROWS").
> > I read a few FAQs and Docs about this problem but probably i've misread them
> > or overread the important parts of them.
> > I couldn't find an answer.
> > Are there any hints you could give?
> > Thanks in advance!
> >
> > Bye!
> > Oli
> >
> > PS:
> > The Storage-Clause in more detail:
> > PCTFREE 40
> > PCTUSED 40
> > INITRANS 1
> > MAXTRANS 255
> > TABLESPACE tbspace_name
> > STORAGE (
> > INITIAL 314572800
> > NEXT 104857600
> > PCTINCREASE 0
> > MINEXTENTS 1
> > MAXEXTENTS 2000000
>
> For now ... rebuild the table. Then recalculate PCTFREE and PCTUSED
> so that they work. I can't understand why you would use 40 for both
> parameters.
>
> For more information on them go to:
> http://www.psoug.org/reference/tables.html

Oliver, what is the average row size for the rows in this table? Also what is your Oracle block size? Remember that the available space to hold rows is the Oracle Block size minus the overhead minus the pctfree. If you average row length exceeded this amount then results like you saw are likely.

If the rows are small enought to fit more than one to a block then since most of the existing rows are probably filled out what you really want to do is re-create the table with a very low pctfree and load the older filled out rows. Then modify the pctfree larger and load the non-filled out rows. This will give you the most compacted format of the table with space for growth where it is needed.

If the rows are long then chaining is just a fact of life and not a problem though it could be one additional reason to migrate to an 8k block if you are using 4k.

HTH -- Mark D Powell -- Received on Wed Mar 03 2004 - 08:15:29 CST