Re-use of Leaf Nodes (was: Index Management)

It occurred to me after I posted that the test could have been a little more rigorous. I mean, I only inserted one lot of 10,000 rows and claimed the index growth was to be expected as 20 leaf nodes. Suppose it was only 20 leaf nodes the first time, and much less the next few times? The fact that it was then only 4 extra leaf nodes when I inserted after the delete would have been meaningless.

Also, I only inserted 10,000 rows after having deleted 20,000. A rigorous test would have inserted 10,000 then *another* 10,000, and then yet another 10,000. If the "I can re-use empty nodes" theory is sound, you should see little or no index growth for the first two inserts, and then a resumption of normal index growth with the third insert, because by that stage there are no more empty nodes left from the original 20,000 row delete.

So anyway, I refined the test a little. I did 3 lots of 10,000 row inserts. I then deleted 20,000 rows. I then did 3 lots of 10,000 row inserts.

The index started at 398 nodes as before. Adding 10,000 rows took that to 418 nodes (+20) Adding another 10,000 rows took that to 438 nodes (+20) Adding another 10,000 rows took that to 458 nodes (+20).

The "expected" increase on the index from adding 20,000 rows really was 20 leaf nodes, therefore, and not just a one-off fluke.

Then I deleted my 20,000 rows as before, leaving the index still at 458 nodes.

Adding 10,000 rows took the index to 462 nodes (+4) Adding another 10,000 rows took the index to 462 nodes (+0) Adding another 10,000 rows took the index to 480 nodes (+18)

So the first two sets of inserts, which merely replaced the 20,000 deleted rows, made the index grow by only the 4 nodes I originally reported. They really are therefore re-using vacated space in the index. (look especially at the result of the second 10,000-row insert). At that point, though, there should be no remaining empty blocks -and sure enough, the next set of inserts made the index grow by something much closer to the "expected" 20 (in fact, only 18, but then I'm dealing in round numbers of rows and can't therefore expect one set of inserts to completely fill a leaf node).

Oh yes: and the rebuild of the index I did by way of a grand finale sent the index up from 480 leaf nodes to a whopping 533 nodes (+53, if my maths is any judge). So the growth of the index after a rebuild wasn't a one-off happenstance, either.

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 Received on Tue May 11 2004 - 18:55:18 CDT