Re: ** CPU impact of I/O change

AFAIK, During update, if updated row does not fit in the same block, then all columns of that row [all row pieces ] moved to a different block and original row will point to migrated row using new rowid [ row migration ]. One exception is that if updated row piece does not fit in ANY block (larger than available free space in an empty block) then row is chained from the original piece itself and row migration does not happen in this special case.

Still rowid based access to that row will access original rowid and then access migrated row pieces using rowid pointers from that original row location. Meaning, indices need not be rebuilt.

HTH
Thanks
Riyaj Shamsudeen
The Pythian Group

On Tue, Apr 15, 2008 at 6:56 PM, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:

> I beg to differ slightly.
>
> a) migrated rows are fixed by a truncate and reload. What Dan probably
> meant is that the reload does not prevent the re-occurrence of row migration
> unless the root cause is addressed, perhaps by as little as increasing
> pctfree.
>
> b) just to prevent the misconception - not that Dan has said anything to
> that effect - that chained rows are only created at insert. An update also
> can cause a row to chain as well as migrate. Just imagine a row with 3
> varchar2(4000) fields which at insert time are populated with a single space
> each. If a later update expands each field to its 4000 byte maximum the
> resulting row does no longer fit into a single block and needs to be
> chained. I don't know if the row at that point is also migrated. Should be
> easy to find out.
>
> As for changing the HWM, if you are on 10g+ with ASSM-LMTs then you can
> use shrink rather than a reorg to reset the HWM.
>
> At 06:10 AM 4/15/2008, Daniel Fink wrote:
>
> The second question is "Will reorganizing tables fix the problem?". The
> answer to that is almost certainly not. Chained rows occur because the row
> is too large to fit into a block. Migrated rows occur when the update to a
> row needs more space than the available free space in the block. In the
> first case, either make the row smaller or the block (actually the available
> free space in the block) larger, two things that a truncate and reload won't
> help. In the second case, you have to change the application logic or the
> free space setting (pctfree) for the table. Migrated rows are a symptom of
> updates that cannot fit into the available free space in the block,
> something a truncate and reload won't help either.
>
> Changing the HWM only impacts full table scans. If the HWM has been set
> too high because of a one time event (huge delete) and it has a quantifiable
> impact, then a reorganization of the table is appropriate. If the HWM is set
> too high because large insert/delete operations are normal, the change will
> be temporary.
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>

--
http://www.freelists.org/webpage/oracle-l