Re: Oracle Chaining

aprinsloo_at_sagetelecom.net wrote:
> Would a exp and and imp for the table achieve thhe same result as the
> "alter move"?
>

Please don't top post (that means, either interleave your answers with what you are responding to, or scroll down to the bottom before you begin typing).

More or less. You might consider the distribution of future updates among your current data. If there won't be many, you might waste a lot of space with a high percent free, you might consider importing data that won't be updated later with a low percent free to fix the problem, then set it up higher for data that will be updated, if you can determine that about your data and the query option of exp is suitable.

Also, Anurag's comment about row chaining v. migration is something to investigate. It is important that you understand your data's attributes and constraints, it's not a good thing to just imp/exp and hope for the best. If it doesn't fix your problem and you've wasted a lot of space, you might decrease performance as Oracle scans more empty space in some situations.

>
>
> Matthias Hoys wrote:
> > <aprinsloo_at_sagetelecom.net> wrote in message
> > news:1165959325.251501.186110_at_80g2000cwy.googlegroups.com...
> > > Oracle 9i standard.
> > >
> > > Ive noticed by looking at DBA_TABLES that some tables have excessive
> > > CHAIN_CNT, the highest been 3266085 on a 45 million row table.
> > > I've set the pct_free to 35 up from 10 but the CHAIN_CNT continues to
> > > climb, is there anything else I can try ?
> > >
> > > Thx in advance
> > >
> >
> > The pct_free change will only affect new blocks. The increase you are seeing
> > is for existing blocks that get updated. Permanent solution : rebuild the
> > whole table with a higher pct_free with ALTER TABLE ... MOVE ... However,
> > you will need enough temporary space for this operation + you will need to
> > rebuild the table indexes and recalculate the stats afterwards. Good luck
> > :-)
> >
> >
> > Matthias

jg

--
@home.com is bogus.
'Check the box' auditing:
http://www.signonsandiego.com/uniontrib/20061213/news_1b13fannie.html