Rebuilding an index might reduce the number of leaf nodes by about 20%
– 30%. The most you can possibly expect from this reduction is 20%-30% for very expensive operations like a FULL INDEX SCAN. The typical
INDEX UNIQUE SCAN gain of an index rebuild is 0%-2% because the depth
of the index is not reduced by the rebuild.
In my understanding , the btree structure/shape is almost uniqe. why would rebuild the btree reduce leaf numbers ?
is there any uncertainty around the btree build process ?
Best Answer
A living index often accumulates empty spaces in the leaf nodes — e.g. due to deletes. Rebuilding the index will make sure all leafs are filled up to the configured threshold.
Note that I wrote it might reduce the number of leaf nodes. If have only inserts on this table, you won't even get this benefit.
Also keep in mind that the overall context of this article is an argument against rebuilding indexes without really good reason.