Re: Re-use of Leaf Nodes

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 12 May 2004 13:57:41 +1000
Message-ID: <40a1a0a9$0$27657$afc38c87@news.optusnet.com.au>

Douglas Hawthorne wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:40a167db$0$25007$afc38c87_at_news.optusnet.com.au...
> SNIP.
>

>>Just thought I'd mention it, anyway. Hopefully, others might repeat the
>>test (or something like it) and submit their results for the 3 inserts-1
>>delete-3 inserts test run.
>>
>>Regards
>>HJR

>
>
> Howard,
>
> My results for 10.1.0.2 on WinXP Pro SP1 using a database block size of 8K
> are:
> ID Test Done Min Inv# Max Inv# Leaf Blks B Level CF # Rows
> --- --------------- -------- -------- --------- ------- ------- -------
> 1 Insert 10,000 1 10,000 18 1 47 10,000
> 2 Insert 10,000 1 20,000 37 1 95 20,000
> 3 Insert 10,000 1 30,000 56 1 143 30,000
> 4 Delete 10,000 10,001 30,000 39 1 97 20,000
> 5 Insert 10,000 10,001 40,000 57 1 146 30,000
> 6 Insert 10,000 10,001 50,000 76 1 194 40,000
> 7 Insert 10,000 10,001 60,000 95 1 242 50,000
>
> I put PCTUSED 0 on my index definition.
>
> Douglas Hawthorne

Thanks for taking the time to do this, Douglas. But a bit more detail might be needed I think.

You're going up 19 leaf nodes per 10,000 inserts (less than me, but I guess that's PCTFREE of 0 for you. I am supposing you meant PCTFREE of 0 rather than PCTUSED, because you can't specify PCTUSED for an index: it is implicitly zero). Your first 10,000 inserts after the 10,000 deletes only increments the count by 1 leaf node (you were at 56 you then get to 57), which confirms the previous test. You then revert to 19-node increments, as before (you only deleted 10,000 rows so only one insert of 10,000 rows was needed before the normal pattern resumed). So, so far, so good: we are in agreement.

Where I think it a little confusing is where you show the leaf blocks decreasing after the bulk delete... because, of course, the bulk delete cannot possibly physically de-allocate nodes from the index structure (which is the whole point of course. If they were physically de-allocated, Brian, Mike, Don, whoever, wouldn't be advocating a rebuild on 'right-hand indexes' in the first place).

So I'd be interested to know what you did to make that happen. Or whether it's a uniquely 10g thing to now automatically de-allocate blocks from an index! News to me if so.

For the record, on 10g in XP SP1 and running the same script as I had earlier (except with one additional calculation of the leaf blocks after the bulk delete as per your example), I get the following results:

Create table & populate...... Finish up with ........ 3164 Leaf Blocks

Insert 10,000..... Finish up with ........ 3185 Leaf Blocks (+21)
Insert 10,000..... Finish up with ........ 3206 Leaf Blocks (+21)
Insert 10,000..... Finish up with ........ 3228 Leaf Blocks (+22)
Delete 19,999..... Finish up with ........ 3228 Leaf Blocks (NIL)
Insert 10,000..... Finish up with ........ 3228 Leaf Blocks (NIL)
Insert 10,000..... Finish up with ........ 3232 Leaf Blocks (+4)
Insert 10,000..... Finish up with ........ 3254 Leaf Blocks (+22)

Rebuild the index. Finish up with ........ 3622 Leaf Blocks (+368)

Which shows exactly the same pattern as my run on 9i Windows 2000 Server SP4.

What did you get when you rebuilt your index at the end, by the way? Mine was an absolute whopper, as you can see!

Anyway: interesting that reduction in leaf blocks after your bulk delete. It certainly doesn't happen on 9i. And it didn't happen on my 10g either. You weren't rebuilding between inserts/deletes were you? The point of this test is to show that no rebuild is necessary, because the vacated leaf nodes are re-usable if left to their own devices. Just wondering

Regards
HJR Received on Tue May 11 2004 - 22:57:41 CDT