Re: Re-use of Leaf Nodes

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:40a1a0a9$0$27657$afc38c87_at_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
Howard,

The difference appears to come about because I was using the DBMS_STATS.GATHER_INDEX_STATS procedure. I am able to reproduce the phenomena you discovered by using the ANALYZE TABLE command.

With the ANALYZE TABLE command, I get the following results:  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 20,000     20,001   30,000        56       1      49  10,000
  5 Insert 10,000     20,001   40,000        57       1      98  20,000
  6 Insert 10,000     20,001   50,000        57       1     147  30,000
  7 Insert 10,000     20,001   60,000        76       1     195  40,000

With the DBMS_STATS.GATHER_INDEX_STATS procedure, I get the following results:
 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 20,000     20,001   30,000        20       1      49  10,000
  5 Insert 10,000     20,001   40,000        38       1      98  20,000
  6 Insert 10,000     20,001   50,000        57       1     147  30,000
  7 Insert 10,000     20,001   60,000        76       1     195  40,000

The command I used was:
EXEC dbms_stats.gather_index_stats( -

        ownname => USER, -
        indname => 'TEST_LEAF_NODES_PK' -
     )

I realised my mistake with PCTUSED after I had posted.

Douglas Hawthorne Received on Wed May 12 2004 - 05:35:04 CDT