Re: move table for chianed rows?

On Mar 26, 10:51 am, "NITIN" <mahima.ni..._at_gmail.com> wrote:
> On Mar 26, 10:25 am, "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.
>
> > What alter table move does is fully documented in the manuals, but
> > basically it just copies the table to a new allocation which by
> > default is in the same tablespace and uses the same storage
> > information. As it copies the rows it will repack the blocks up to
> > pctfree potentially resulting in the use of less space. Any space
> > savings will depend on the past DML activity against the table.
> > Migrated rows will be unmigrated. All indexes will be invalid after a
> > move so the indexes have to be rebuilt. (Alter index rebuild or drop/
> > create).
>
> > HTH -- Mark D Powell --
>
> Hi Mark
>
> You said " As it copies the rows it will repack the blocks up to> pctfree potentially resulting in the use of less space. Any space
> > savings will depend on the past DML activity against the table.
> > Migrated rows will be unmigrated. "
>
> Can you please explain this better
>
> thanks,
> Nitin- Hide quoted text -
>
> - Show quoted text -

There is a chapter in the Concepts manual that explains how Oracle handles space allocation within a data block. The chapter provides full details on pctfree and pctused. The chapter also discusses the relationship between blocks, extents, and segments.

Basically pctfree is the percentage of a data block reserved for the growth of rows upon update. As Oracle inserts rows into a block it will not generally insert beyond this percentage. The default is 10%. When a row is updated and the row grows from 80 to 90 bytes the extra 10 bytes come out the pctfree.

When rows are deleted the block is not used for new inserts until the amount of used space in the block drops below pctused. So if the row length was such that the difference between pctfre and pctused was the size of 5 rows and 4 rows were deleted the block would not be available for inserts. But on a rebuild if the rows from this block would be copied into another block such that there was no unused space in the block except for the space reserved by pctfree.

ASSM manages this for you and changes the particulars but the basic idea is the same.

HTH -- Mark D Powell -- Received on Mon Mar 26 2007 - 18:31:09 CDT