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

On 19 Jun 2006 12:56:23 -0700, bdbafh_at_gmail.com wrote:

>
>Sybrand Bakker wrote:
>> On 19 Jun 2006 10:43:24 -0700, bdbafh_at_gmail.com wrote:
>>
>> >> 2 Observing the redo per record is still unreliable (because of the
>> >> rebalancing of indexes), but it is the most reliable method. So indeed
>> >> you need to multiply the redo per record by the number of records.
>> >> Would be nice to know whether you are less off from the actual redo by
>> >> this calculation.
>> >
>> >Please explain what you mean by "rebalancing of indexes".
>> >
>>
>> >> 3 If you populate an empty index you force Oracle to rebalance the
>> >> B*-tree often.
>> >
>> >Please explain whay you mean by "rebalance the B*-tree".
>>
>> Should I explain what a balanced tree is, and what bucket split is?
>> Is that a serious question, or are you trying to have me abstract
>> common information textbooks?
>>
>> --
>> Sybrand Bakker, Senior Oracle DBA
>
>To me, if something is "re-balanced" that would imply that it was
>balenced and then out of balance. I am not looking for you to extract
>common information, just to discuss how you think that the index was
>"out of balance" so as to need "re-balancing" or "balancing".
>(50-50 split and 90-10 splits aside).
>
>-bdbafh

Obviously, if you load your data in a table without indexes, and create your index after data-insertion, the complete B*-tree will be calculated (by sorting the keys in a temporary segment) and written only once.
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.
This is especially likely to occur as you index a surrogate key, which can only increment.

--
Sybrand Bakker, Senior Oracle DBA