Could the team add (a section ?) to this page:

https://sqlite.org/draft/lang_analyze.html

I would like a list of SQL commands which would remove everything that the ANALYZE command might write no matter which version of SQLite the ANALYZE command was run in or which options were used. My guess is that this would be four DROP TABLE IF EXISTS … commands.

Can I also ask whether VACUUM preserves the results of ANALYZE ?

Undo ANALYZE for all historical versions of SQLite using:

DROP TABLE IF EXISTS sqlite_stat1;
DROP TABLE IF EXISTS sqlite_stat2;
DROP TABLE IF EXISTS sqlite_stat3;
DROP TABLE IF EXISTS sqlite_stat4;

There are no plans to add "sqlite_stat5" anytime soon. But I reserve the right to do that if it becomes necessary.

I don't want to add this to the documentation because it seems like unnecessary complication. Why would anybody ever want to delete the results of ANALYZE? What do you hope to accomplish by doing that? If the ANALYZE results have become obsolete, the reasonable thing to do is to rerun ANALYZE.

Recommended practice is to run the following commands prior to shutting down an application that uses SQLite:

PRAGMA analysis_limit=200; -- optional
PRAGMA optimize;

The "PRAGMA optimize" command will run ANALYZE when needed (which is to say "rarely"). Usually "PRAGMA optimize" is a no-op. Setting the analysis_limit to 200 guarantees that when ANALYZE does run, it runs quickly, even for large databases.

ANALYZE results are preserved across VACUUM.

Fair enough. Thanks for the fast and useful reply.

I have a reason to remove the results of ANALYZE. Suppose you had rows in a database (or perhaps just one table) which held private information. You can DELETE all the rows FROM the tables, then do a VACUUM. But at some point a program may have run ANALYZE on the database. The data in one of the sqlite_stat? tables might give away something interesting about the data that used to be there, even if it's only how many rows there once was in a table.

Your suggestion to simply re-run ANALYZE doesn't help because ANALYZE doesn't delete tables that a previous version of SQLite may have made up. For instance, ANALYZE on a current version of SQLite may overwrite sqlite_stat4 but would leave sqlite_stat1 in place.

There are no plans to add "sqlite_stat5" anytime soon. But I reserve the right to do that if it becomes necessary.

Perhaps this SQL could be improved for future-proofing reasons to simply:

DROP TABLE IF EXISTS sqlite_stat1;
DROP TABLE IF EXISTS sqlite_stat2;
DROP TABLE IF EXISTS sqlite_stat3;
DROP TABLE IF EXISTS sqlite_stat4;
DROP TABLE IF EXISTS sqlite_stat5;
DROP TABLE IF EXISTS sqlite_stat6;
DROP TABLE IF EXISTS sqlite_stat7;

Or if it was me, run a single for-loop, something like:

  var n = 1;
  while DoQuery("DROP TABLE sqlite_stat{n};") = success { n += 1 };