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

"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
Received on Thu Nov 27 2003 - 20:11:41 CST