Documentation Source Text

  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from each index and store that data
  in the <b>sqlite_stat3</b> table.  The query planner will then use the
  histogram data to help it make better index choices.
}

COMPILE_OPTION {SQLITE_ENABLE_STAT4} {
  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from each index and store that data
  in the <b>sqlite_stat4</b> table.  The query planner will then use the
  histogram data to help it make better index choices.
  <p>
  [SQLITE_ENABLE_STAT3] is a no-op if this compile-time option is used.
}

COMPILE_OPTION {SQLITE_ENABLE_TREE_EXPLAIN} {
  This option adds support for the [SQLITE_TESTCTRL_EXPLAIN_STMT] test-control
  in the SQLite core.  When the [command-line shell] is also compiled with
  this option, the ".explain" dot-command enables a mode that uses the
  [SQLITE_TESTCTRL_EXPLAIN_STMT] interface to display an ASCII-art diagram
  of the parse tree for each SQL query statement that is run in the shell.

<p>The format for sqlite_stat2 is recorded here for legacy reference.  
Recent versions of SQLite no longer support sqlite_stat2 and the
sqlite_stat2 table, it is exists, is simply ignored.

<tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl>
<h4>2.5.5 The sqlite_stat3 table</h4>

<p>The sqlite_stat3 is only only used if SQLite is compiled
with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4]
and if the SQLite version number is 3.7.9 or greater.
The sqlite_stat3 table is neither read nor written by any
version of SQLite before 3.7.9.
If the [SQLITE_ENABLE_STAT4] compile-time option is used and the
SQLite version number is 3.8.1 or greater, then sqlite_stat3 might
be read but not written.
The sqlite_stat3 table contains additional information
about the distribution of keys within an index, information that the
query planner can use to devise better and faster query algorithms.
The schema of the sqlite_stat3 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);

the sample.  
The sqlite_stat3.nLt holds the approximate number of entries in the
index whose left-most column is less than the sample.
The sqlite_stat3.nDLt column holds the approximate
number of distinct left-most entries in the index that are less than
the sample.

<p>There can be an arbitrary number of sqlite_stat3 entries per index.
The [ANALYZE] command will typically generate sqlite_stat3 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment stat4tab {sqlite_stat4} SQLITE_STAT4</tcl>
<h4>2.5.6 The sqlite_stat4 table</h4>

<p>The sqlite_stat4 is only created and is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT4] and if the SQLite version number is
3.8.1 or greater.  The sqlite_stat4 table is neither read nor written by any
version of SQLite before 3.8.1.
The sqlite_stat4 table contains additional information
about the distribution of keys within an index, information that the
query planner can use to devise better and faster query algorithms.
The schema of the sqlite_stat4 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>

<p>There are usually multiple entries in the sqlite_stat4 table for each index.
The sqlite_stat4.sample column holds the content of an index entry in
the [record format].  The index entry stored is taken from the
index identified by the sqlite_stat4.idx and the sqlite_stat4.tbl columns.
If the sqlite_stat4.idx and sqlite_stat4.tbl
columns hold the same value, then that row contains a sample from
the [INTEGER PRIMARY KEY] of the table.
The sqlite_stat4.nEq column holds a list of integer where the K-th integer
is the approximate number of entries in the index whose left-most K columns
exactly match the K left-most columns of the sample.
The sqlite_stat4.nLt holds a list of integers where the K-th integer is
the approximate number of entries in the
index whose K left-most columns are collectively less than the 
K left-most columns of the sample.
The sqlite_stat4.nDLt column holds a list of intgers where the K-th 
integers is the approximate
number of entries in the index that are distinct in the first K columns and
that are whose left-most K columns are collectively less than the left-most
K columns of the sample.

<p>The sqlite_stat4 is a generalization of the sqlite_stat3 table.  The
sqlite_stat3 table provides information about the left-most column of an
index whereas the sqlite_stat4 table provides information about all columns
of the index.

<p>There can be an arbitrary number of sqlite_stat4 entries per index.
The [ANALYZE] command will typically generate sqlite_stat4 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment rollbackjournal {rollback journal format}</tcl>
<h2>3.0 The Rollback Journal</h2>

<p>The rollback journal is a file associated with each SQLite database
file that hold information used to restore the database file to its initial
state during the course of a transaction.

and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named "[sqlite_stat1]".  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT3] option and without the [SQLITE_ENABLE_STAT4]
option, then additional histogram data is
collected and stored in [sqlite_stat3].
 ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT4] option, then additional histogram data is
collected and stored in [sqlite_stat4].
Older versions of SQLite would make use of the [sqlite_stat2] table
when compiled with [SQLITE_ENABLE_STAT2] but all recent versions of
SQLite ignore the sqlite_stat2 table.
Future enhancements may create
additional [internal tables] with the same name pattern except with
final digit larger than "4".</p>

<p> ^The [ALTER TABLE] command does
not work on the sqlite_stat1 or sqlite_stat3 tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
^(The [DROP TABLE] command works on sqlite_stat1 and

  the range constraint on column x should reduce the search space by
  a factor of 10,000 whereas the range constraint on column y should
  reduce the search space by a factor of only 10.  So the ex2i1 index
  should be preferred.
}
PARAGRAPH {
  ^SQLite will make this determination, but only if it has been compiled
  with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4].
  ^The [SQLITE_ENABLE_STAT3] and [SQLITE_ENABLE_STAT4] options causes
  the [ANALYZE] command to collect a histogram of column content in the
  [sqlite_stat3] or [sqlite_stat4] tables and to use this histogram to 
  make a better guess at the best query to use for range constraints
  such as the above.  The main difference between STAT3 and STAT4 is
  that STAT3 records histogram data for only the left-most column of
  an index whereas STAT4 records histogram data for all columns of an
  index.  For single-column indexes, STAT3 and STAT4 work the same.
}
PARAGRAPH {
  ^The histogram data is only useful if the right-hand side of the constraint
  is a simple compile-time constant or [parameter] and not an expression.
}
PARAGRAPH {
  ^Another limitation of the histogram data is that it only applies to the

<p>SQLite will always pick the same query plan for any
given SQL statement as long as:
<ol type="a">
<li>the database schema does not change in significant ways such as 
    adding or dropping indices,</li>
<li>the ANALYZE command is not rerun, </li>
<li>SQLite is not compiled with [SQLITE_ENABLE_STAT3]
    or [SQLITE_ENABLE_STAT4], and</li>
<li>the same version of SQLite is used.</li>
</ol>
The SQLite stability guarantee means that if all of your queries run 
efficiently
during testing, and if your application does not change the schema,
then SQLite will not suddenly decide to start using a different
query plan, possibly causing a performance problem, after your application