Re: Re-use of Leaf Nodes

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 13 May 2004 05:11:43 +1000
Message-ID: <40a276e3$0$23833$afc38c87@news.optusnet.com.au>

Douglas Hawthorne wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:40a217c8$0$23828$afc38c87_at_news.optusnet.com.au...
> SNIP.
>

>>Oh lord! It never stops, does it?! Just when you think you've got
>>something nailed, some other degree of freedom pops loose!
>>

>
> SNIP.
>
> Howard,
>
> I investigated the INDEX REBUILD further and found the following results:
> TEST_RUN# REBUIL UNIQUENESS PCT_FREE Leaf Blks
> ---------- ------ ---------- ---------- ---------
> 1 Before NONUNIQUE 10 81
> 2 After NONUNIQUE 10 90
> 3 Before NONUNIQUE 0 81
> 4 After NONUNIQUE 0 80
> 5 Before UNIQUE 10 76
> 6 After UNIQUE 10 84
> 7 Before UNIQUE 0 76
> 8 After UNIQUE 0 76
>
> I ran the test run as before and then rebuilt the index while recording the
> statistics from DBMS_STATS before and after the index rebuild.
>
> I was able to reproduce your result of having the index grow bigger by 8 or
> 9 blocks when PCTFREE=10 (the default). The only time I got an improvement
> was when I rebuilt a non-unique index with PCTFREE=0.
>
> The unique index is smaller than the non-unique one.
>
> This was done on 10.1.0.2 on WinXP Pro SP1.
>
> Douglas Hawthorne

I have to confess my head is spinning from all of this stuff now. I think I'll go back and ponder the delights of block dumps for a while. You know where you are with them. Have you noticed that the increase in index size is about 10% of its final size? You're getting 9 block inflations of the index when your index ends up being 90 blocks big. I got a 368 block inflation when my index ended up being 3622 blocks.

And you only see this result when PCTFREE is 10%.

I think there's a relationship there, don't you? I wonder what happens when you build the index (initially AND at the end) with PCTFREE of, say, 30%? (Yikes. I can't ask questions like that and not find out, so OK, I did the test and we get an end-of-test index inflation of ...[wait for it].... 30% (a 480 block index became a 690 block one).

I still can't see why the index balloons by PCTFREE with a rebuild when the loading of records into the index should have been respecting PCTFREE in the first place anyway (except that the figures suggest it wasn't) .... My head hurts.

Richard, are you there??

Regards
HJR Received on Wed May 12 2004 - 14:11:43 CDT