Add TABLE option to SET STATISTICS to recalculate statistics of all indexes of a table

[mrotteveel]

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.

[sim1984]

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.

[mrotteveel]

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} ONLY option (i.e. SET STATISTICS TABLE <table-name> [{TABLE | INDEX} ONLY]), where TABLE recalculates only the table statistics, INDEX only the index statistics, and if we ever add something else like column statistics, we can add that option to the list.

[ertankucukoglu]

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.

[TommiPrami]

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

[sim1984]

Collecting statistics may be more complex than just calculating index selectivity. Perhaps we should implement something like DBMS_STATS package as in Oracle.

[aafemt]

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.