Re: Is there such thing as row migration for indexes?

"Howard J. Rogers" <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>...
> "Jeffrey Yee" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > Hi,
> >
> > I'm wondering if row migration can exist for indexes.
> 
> It can't.
> 
> What causes row migration in a table? Updates do... the row needs to grow in
> size, and there's not room enough in its existing block to allow that growth
> to occur.
> 
> Do index entries *ever* get updated? No.
> 
> Because if they did, then it would be possible to update 'Bob' to "Robert',
> and you'd therefore have an "R" iindex entry sitting in between "Adam" and
> "Charles"... and that clearly makes a mockery of the entire concept of an
> index.
> 
> Therefore, an update to a piece of table data causes us to delete the entire
> corresponding leaf entry, and to re-insert a new one in the correct place
> (ie, delete the entry for 'Bob' -actually, just mark it as deleted, not
> actually clear it out- and insert a brand new entry for 'Robert' in between
> the existing entries for 'Quentin' and 'Steve').
> 
> And row migration never happens when you do deletes and inserts. Therefore,
> there is no row migration within an index.
> 
> There can, of course, be row *movement*. If the fresh insert of 'Robert'
> requires us to slot his entry between existing entries, then it is possible
> that there is not enough room in the leaf node to accomodate that new
> entry... at which point we have to perform a block split, which would
> perhaps result in the 'Steve' entry being moved to a new leaf node, thus
> vacating a space to allow the 'Robert' entry to be inserted.
> 
> But that again is just a delete of Steve's original entry, and a
> re-insertion somewhere new. And deletes and inserts don't cause migration
> (which ought to be strictly defined as 'a pointer in one block that directs
> us to another').
> 
> > Since we can
> > specify pctfree, I would assume this is possible.
> 
> PCTFREE for an index is desgined to leave space in a leaf node so that the
> insertion of the 'Robert' entry would not have needed to create space (ie
> cause a block split) nbefore it could be inserted... the space was already
> there, because you'd already provisioned for it with PCTFREE.
> 
> That's quite a different function from PCTFREE for a table, which tells us
> when to stop inserting into a block. With an index, you can never stop
> inserting into a block, because new arrivals *have* to be inserted into
> whatever block makes positional sense for the value being inserted. If
> there's no room in the block you have to insert into, tough... you hvae to
> make some room. Which is what block splits do.
> 
> >If so, I wonder how
> > I can check for row migration in indexes, since the dba_indexes does
> > not have the row_chain column, which is populated when analyze.
> 
> The column is actually called CHAIN_CNT in dba_tables, not row_chain. And
> the fact that there's no column of that name in dba_indexes should have
> given you a clue.
> 
> Regards
> HJR
> 
> 
> >
> > Please advice. Thank you.
> >
> >
> > Best Regards,
> > Jeffrey Yee

Dear Howard,

Thank you very much for the explanation. It makes a lot of sense. What
I'm still confuse is why is there a need to specify pctfree for index,
since any modification on the index is either an insert or
delete/insert combination. Please advice once again. Thank you in
advance.