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

"Jeffrey Yee" <jeffyee_at_hotmail.com> wrote in message news:ec4cec95.0312030149.1d423999_at_posting.google.com... > "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3fc6aef8$0$13634$afc38c87_at_news.optusnet.com.au>...
> > "Jeffrey Yee" <jeffyee_at_hotmail.com> wrote in message
> > news:ec4cec95.0311262135.1d885d0a_at_posting.google.com...
> > > 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.

If there was no such thing as PCTFREE for indexes then when the index was created every used block would be 100% full. Thus you guarantee that an insert will create a leaf split. leaf splits are expensive and therefore it is a good thing to avoid them where possible. This is why you specify pctfree - so that there will be some space in every block at least initially for new rows.

HTH

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK