Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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 ?
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.
