lacakus wrote:
Can I achieve "updating statistics" by : alter index inactive; alter index active; ?
Ann W. Harrison answers:
Yes, but that is harder to do on-line than just setting the statistics. Since the index is unavailable while it's being rebuilt, the rebuild can't be done while there are prepared queries that depend on it. So rebuilding indexes is (more or less) an off line operation.
lacakus continues:
In docs says "Reactivating a deactivated index rebuilds and rebalances an index."
Ann W. Harrison continues:
Yes, that's true, but no, it's probably not nearly as important as you think it is.
Many of us had introductory data structure courses that showed an index design where the index developed was variable depth ... some branches got really long, others were very short. Access time depended on the branch and "rebalancing" to make all the branches the same length was critical to performance. In the real world, nobody is that dumb.
In Firebird, an index is made up of pages - the same size as all pages in the database. It starts as a single page. Index entries (called nodes) are added to the page in order by key. When the page fills, two other index pages are added.
The first one is on the same level - it will be the "right sibling" and will contain higher key values than the existing page. Ordinarily, about half the contents of the original page are copied to the new page. However, if the new node would have been the last node on the old page, only it goes on the new page.
The second new page is at the next higher level. It will initially contain two nodes - one pointing to the first of the lower level pages and one pointing to the second. Each of those nodes will also contain the key value of the first node on the page it points to, plus the record number associated with that key value.
The index continues to build in that fashion - when a page overfills, it splits and create a new right sibling. The next level up gets a new node pointing to the new page.
When gstat reports that an index has three levels, it means that there is one top page, one level of intermediate pages, and one level of pages with nodes that point to records.
When nodes are deleted from an index page, Firebird checks that the fill level on the page is more that .25 of the page. If it is not, and if there is enough space on the left sibling page, then the contents of the mostly empty page are moved to the left sibling and the parent pointers are fixed to eliminate the now empty page. The page is released to the free space area and reused as necessary.
Index garbage collection doesn't matter much for indexes on data that is added and deleted in random order, but it's very useful in the case where keys from new records have higher values than keys from old records and old records are deleted. Without index clean up, the left hand side of the index becomes "hollow" and the index can be deeper than necessary because of the intermediate level pointers to empty pages.
There are a number of limits on garbage collection. The most important is that it will never eliminate a whole level. If you have a three level index and delete all the data, it will continue to be a three level index with one empty page on each level. So, if you actually delete all the data from a table, deactivate and reactivate the indexes. Nobody else is using that table anyway.
Another limit is that the left most pages at each level never go away. That's an obvious result of collapsing to the left, and also a clean way to avoid losing a whole level.
Summary:
A Firebird index never goes "out of balance" like an index built top down with variable length branches.
Firebird collapses strings of mostly empty index pages, so indexes don't develop "hollow" areas where the upper level points to empty pages.
A freshly built index will be more dense than an index built in random order, but not more dense than an index where each new key entered is larger than all existing entries (e.g. a primary key with a generator). If entries are made and removed in random order, having a bit of free space on index pages reduces page splits, which is a good thing.