Re: Transactions requires much more UNDO space than expected....

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:jt6e92dia3l1i0qftq5kb6pfnl2ut6somr_at_4ax.com...

> Whereas while you insert record by record in an empty table with
> indexes, the normal insertion algorithm applies, so sooner or later
> you get bucket split, of a block that is already on disk. If you get
> bucket split, you get an extra level. That level needs to be written
> to disk, and the index needs to be reorganized, as you can't have a
> different number of levels in various branches. That is what I call
> rebalancing.

If a leaf block splits, then there need be no change in level, only an extra entry in the branch block above. If the branch block splits, there need be no change in level , only an extra entry in the branch block above it. If it is the TOP branch block (i.e. the root block) that needs to split, you need only introduce a new root block with two entries pointing to the two blocks that now share the content of the old root block to introduced the extra level. The index does NOT need to be reorganized.

> This is especially likely to occur as you index a surrogate key, which
> can only increment.
>

This sounds like a rehash of the misunderstanding exposed in

    http://www.jlcomp.demon.co.uk/13_unbalanced_indexes_i.html

> --
> Sybrand Bakker, Senior Oracle DBA

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html