Re: move table for chianed rows?

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 -- Received on Mon Mar 26 2007 - 09:25:54 CDT