Documentation Source Text

<p> The query planner loads the content of the statistics tables
into memory when the schema is read.  ^Hence, when an application
changes the statistics tables directly, SQLite will not immediately
notice the changes. ^An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_master</b>. </p>

<tcl>hd_fragment autoanalyze {automatically running ANALYZE}</tcl>
<h2>Automatically Running ANALYZE</h2>

<p>The [PRAGMA optimize] command will automatically run ANALYZE on individual
tables on an as-needed basis.  The recommended practice is for applications
to invoke the [PRAGMA optimize] statement just before closing each database
connection.</p>

<p>Each SQLite [database connection] records cases when the query planner would
benefit from having accurate results of ANALYZE at hand.  These records
are held in memory and accumulate over the life of a database connection.
The [PRAGMA optimize] command looks at those records and runs ANALYZE on only
those tables for which new or updated ANALYZE data seems likely to be useful.
In most cases [PRAGMA optimize] will not run ANALYZE, but it will occasionally
do so either for tables that have never before been analyzed, or for tables
that have grown significantly since they were last analyzed.</p>

<p>Since the actions of [PRAGMA optimize] are determined to some extent by
prior queries that have been evaluated on the same database connection, it
is recommended that [PRAGMA optimize] be deferred until the database connection
is closing and has thus had an opportunity to accumulate as must usage information
as possible.  It is also reasonable to set a timer to run [PRAGMA optimize]
every few hours, or every few days, for database connections that stay open
for a long time.</p>

<p>Applications that desire more control can run [PRAGMA optimize(0x03)] to 
obtain a list of ANALYZE commands that SQLite thinks are appropriate to run,
but without actually running those commands.  If the returned set is 
non-empty, the application can then make a decision about whether or not
to run the suggested ANALYZE commands, perhaps after prompting the user
for guidance.</p>

<p>The [PRAGMA optimize] command was first introduced with 
SQLite 3.18.0 ([dateof:3.18.0]) and is a no-op for all prior releases
of SQLite.</p>

<h2>Anticipated Future Enhancements</h2>

<p>All existing versions of SQLite do a full table scan for ANALYZE.  This can be
slow for multi-gigabyte and larger databases.  Future versions of SQLite might
use random sampling rather than a full table scan to obtain estimates for the
database shape, especially on larger tables.  The results would approximate, but 
will be close enough for query planning purposes.  As of 2017-03-20, this
concept has been tested in experimental branches and appears to work well, but
has not been folded into an official release.</p>

<tcl>
##############################################################################
Section {ATTACH DATABASE} attach {attached *ATTACH}

RecursiveBubbleDiagram attach-stmt
</tcl>

         information from the current session in the
         database file so that it will be available to "optimize"
         pragmas run by future database connections.
  <li value='8'><p>
         <em>(Not yet implemented)</em>
         Create indexes that might have been helpful to recent queries.
  </ol>
  <p>The default MASK is and always shall be 0xfffe.  The 0xfffe mask means
  perform all of the optimizations listed above except Debug Mode.  If new
  optimizations are added in the future that should be off by default, those
  new optimizations will be given a mask of 0x10000 or larger.</p>

  <p>To see all optimizations that would have been done without actually
  doing them, run "PRAGMA optimize(-1)".  To use only the ANALYZE
  optimization, run "PRAGMA optimize(0x02)".</p>
  
  <p><b>Determination Of When To Run Analyze</b></p>
  <p> In the current implementation, a table is analyzed if and only if 
      all of the following are true:
  <ul>
  <li><p>
    MASK bit 0x02 is set.
  <li><p>
    The query planner used [sqlite_stat1]-style statistics for one or
    more indexes of the table at some point during the lifetime of
    the current connection.