Re: leaf node splits
From: Andy Klock <andy_at_oracledepot.com>
Date: Thu, 26 Jan 2012 20:50:59 -0500
Message-ID: <CADo_RaPChKeJ0N14f3C4Hs9QkD=UpOvocADBK_hdRfXjnJv6NA_at_mail.gmail.com>
On Thu, Jan 26, 2012 at 4:20 PM, Dominic Brooks <dombrooks_at_hotmail.com>wrote:
> ...
>
> Every day, the first time I run this statement with this new data I will
> get some 3000 "leaf node splits" of which 1020 are "leaf node 90-10 splits"
> then I roll it back.
>
> Every subsequent time I repeat this statement and roll back, I insert the
> exact same data in the insert .. select but I get 1020 _at_"leaf node splits"
> and "1020 leaf node 90-10 splits'".
>
> Any thoughts on the difference?
>
>
I believe the "leaf node splits" counter is goes up even if they are "leaf node 90-10 splits". So, when your new data is inserted the first time 1980 of those are 50-50 splits. When you rollback that transaction those blocks aren't un-split so on subsequent runs those values fall right into their place in the index.
Date: Thu, 26 Jan 2012 20:50:59 -0500
Message-ID: <CADo_RaPChKeJ0N14f3C4Hs9QkD=UpOvocADBK_hdRfXjnJv6NA_at_mail.gmail.com>
On Thu, Jan 26, 2012 at 4:20 PM, Dominic Brooks <dombrooks_at_hotmail.com>wrote:
> ...
>
> Every day, the first time I run this statement with this new data I will
> get some 3000 "leaf node splits" of which 1020 are "leaf node 90-10 splits"
> then I roll it back.
>
> Every subsequent time I repeat this statement and roll back, I insert the
> exact same data in the insert .. select but I get 1020 _at_"leaf node splits"
> and "1020 leaf node 90-10 splits'".
>
> Any thoughts on the difference?
>
>
I believe the "leaf node splits" counter is goes up even if they are "leaf node 90-10 splits". So, when your new data is inserted the first time 1980 of those are 50-50 splits. When you rollback that transaction those blocks aren't un-split so on subsequent runs those values fall right into their place in the index.
As for the 90-10, that looks like you have a right handed index and is splitting with every insert.
Andy
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 26 2012 - 19:50:59 CST