Re: move table for chianed rows?

On Mar 27, 2:42 am, "Andrea" <netsecur..._at_tiscali.it> wrote:
> On 26 Mar, 16:25, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Mar 26, 7:11 am, "Andrea" <netsecur..._at_tiscali.it> wrote:
>
> > > Hi,
>
> > > sorry for my question (i'm a newbie DBA) but i've read on forums that
> > > for resolve chained rows on tables, it is enough move table in this
> > > method:
>
> > > alter table FOO move;
>
> > > is true? but what does it do exactly this command??
>
> > > thanks for any support!
>
> > > bye
> > > andrew
>
> > Actually a chained row is a row to big to fit into a single Oracle
> > block and as such rebuilding the table will not eliminate chained
> > rows.
>
> > Unfortunately Oracle places the count of migrated rows in the
> > dba_tables.chain.cnt column. A migrated row is a row that after an
> > update operation would no longer fit back into the block it was in so
> > Oracle migrates the row to a new block leaving a rowid marker behind
> > that points to the new row location. Accesses via an index go to the
> > old location and then have to do an extra IO to find the row in its
> > new location.
>
> > Before rebuilding a table to eliminate chained rows you need to look
> > at the average row length and make sure it is less than the available
> > space within a block. Otherwise you would be wasting your time.
>
> ok, so.. if i understand...
> for eliminate chained rows i have to:
>
> 1) Estimate avg row length through dbms_stats.gather_tables or analyze
> 2) Edit the storage of table (pctfree)
> 3) alter table FOO move;
>
> is right?
>
> and you advise to use ASM ?
>
> thanks very much!- Hide quoted text -
>
> - Show quoted text -

Wrong. To eliminate CHAINED rows you need a larger db_block_size. To eliminate MIGRATED rows you need to alter the PCTFREE/PCTUSED values for the table in question then either move the table or drop and recreate it. Migrated rows != chained rows. Mark D. Powell has already explained this difference to you.

David Fitzjarrell Received on Tue Mar 27 2007 - 16:44:28 CDT