FirebirdSQL
Add TABLE option to SET STATISTICS to recalculate statistics of all indexes of a table
Currently, when you want to recalculate statistics, you need to call SET STATISTICS INDEX <index-name> for each individual index. I think it would be useful to add a SET STATISTICS TABLE <table-name> to recalculate statistics for all indexes of a table.
This is a more complex topic than you have described. In the future, statistics may be collected not only for indexes, but also for the tables themselves. Other DBMS have stored statistics on the selectivity of individual columns of the table.
This is a more complex topic than you have described. In the future, statistics may be collected not only for indexes, but also for the tables themselves. Other DBMS have stored statistics on the selectivity of individual columns of the table.
If that ever happens (because I've been hearing this for over a decade), we can modify the clause as follows:
- Bare
SET STATISTICS TABLE <table-name>recalculates all statistics related to the table (so for indexes, table itself, whatever else gets added in the future) - Add a
{TABLE | INDEX} ONLYoption (i.e.SET STATISTICS TABLE <table-name> [{TABLE | INDEX} ONLY]), whereTABLErecalculates only the table statistics,INDEXonly the index statistics, and if we ever add something else like column statistics, we can add that option to the list.
I do not remember recalculating just one table index statistics. Whenever I do, I recalculate all indexes in a database. I also think it would be helpful to have ability to do it for all database indexes using a single line of SQL.
It would be actually quite nice to have the possibility to update all indexes in one liner, without needing to bother with the system tables.
Like to disable all trikers and indecies etc... And enable them back.
Would help offline db maintenance and structure changes
Collecting statistics may be more complex than just calculating index selectivity. Perhaps we should implement something like DBMS_STATS package as in Oracle.
Perhaps we should implement something like DBMS_STATS package as in Oracle.
...or (as in Oracle) compare expected stats and real stats got during query execution and adjust them on-the-fly.
