Documentation Source Text

[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
sqlite_stat3 as of SQLite version 3.7.9.)^
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.  
See "[Manual Control Of Query Plans Using SQLITE_STAT Tables]" for
further information.</p>

<p> ^Statistics gathered by ANALYZE are not automatically updated as
the content of the database changes.  If the content of the database
changes significantly, or if the database schema changes, then one should
consider rerunning the ANALYZE command in order to update the statistics.</p>

<p> The query planner might not notice manual changes to the
sqlite_stat1 and/or sqlite_stat3 tables.  ^An application


can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_master</b>. </p>

<p> 

<tcl>
##############################################################################
................................................................................
BubbleDiagram create-index-stmt 1
BubbleDiagram indexed-column
</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.</p>



<tcl>hd_fragment {descidx} {descending indices} {descending index}</tcl>
<p>^Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order.  ^The sort order may or may not be ignored depending
on the database file format, and in particular the [schema format number].
^The "legacy" schema format (1) ignores index
sort order.  ^The descending index schema format (4) takes index sort order
................................................................................
funcdef {char(X1,X2,...,XN)} {} {
  ^(The char(X1,X2,...,XN) function returns a string composed of characters having the
   unicode code point values of integers X1 through XN, respectively.)^
}

funcdef {coalesce(X,Y,...)} {} {
  ^The coalesce() function returns a copy of its first non-NULL argument, or
  NULL if all arguments are NULL.  ^Coalesce() must be at least 
  2 arguments.
}

funcdef {glob(X,Y)} {} {
  ^The glob(X,Y) function is equivalent to the
  expression "<b>Y GLOB X</b>".
  Note that the X and Y arguments are reversed in the glob() function
................................................................................

<ol>
<li><p>The [query planner checklist] describes steps that application
developers should following to help resolve query planner problems.
Notice the that the use of INDEXED BY is a last resort, to be used only
when all other measures fail.</p>

<li><p>[upluscontrol|The unary + operator]
can be used to disqualify terms in the WHERE clause from use by indices.
Careful use of unary + can sometimes help prevent the query planner from
choosing a poor index without restricting it to using one specific index.
Careful placement of unary + operators is a better method for controlling 
which indices are used a query.</p>

<li><p>The [sqlite3_stmt_status()] C/C++ interface together with the
[SQLITE_STMTSTATUS_FULLSCAN_STEP] and [SQLITE_STMTSTATUS_SORT] verbs
can be used to detect at run-time when an SQL statement is not
making effective use of indices.  Many applications may prefer to
use the [sqlite3_stmt_status()] interface to detect index misuse
rather than the INDEXED BY phrase described here.</p>

[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".
All of these tables are collectively referred to as "statistics tables".
</p>

<p> ^The [ALTER TABLE] command does
not work on the statistics tables tables,
but all the content of the statistics 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 statistics tables
as of SQLite version 3.7.9.)^
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.  
See "[Manual Control Of Query Plans Using SQLITE_STAT Tables]" for
further information.</p>

<p> ^Statistics gathered by ANALYZE are not automatically updated as
the content of the database changes.  If the content of the database
changes significantly, or if the database schema changes, then one should
consider rerunning the ANALYZE command in order to update the statistics.</p>

<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>

<p> 

<tcl>
##############################################################################
................................................................................
BubbleDiagram create-index-stmt 1
BubbleDiagram indexed-column
</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
If the optional WHERE clause is included, then the index is a "[partial index]".
</p>

<tcl>hd_fragment {descidx} {descending indices} {descending index}</tcl>
<p>^Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order.  ^The sort order may or may not be ignored depending
on the database file format, and in particular the [schema format number].
^The "legacy" schema format (1) ignores index
sort order.  ^The descending index schema format (4) takes index sort order
................................................................................
funcdef {char(X1,X2,...,XN)} {} {
  ^(The char(X1,X2,...,XN) function returns a string composed of characters having the
   unicode code point values of integers X1 through XN, respectively.)^
}

funcdef {coalesce(X,Y,...)} {} {
  ^The coalesce() function returns a copy of its first non-NULL argument, or
  NULL if all arguments are NULL.  ^Coalesce() must have at least 
  2 arguments.
}

funcdef {glob(X,Y)} {} {
  ^The glob(X,Y) function is equivalent to the
  expression "<b>Y GLOB X</b>".
  Note that the X and Y arguments are reversed in the glob() function
................................................................................

<ol>
<li><p>The [query planner checklist] describes steps that application
developers should following to help resolve query planner problems.
Notice the that the use of INDEXED BY is a last resort, to be used only
when all other measures fail.</p>

<li><p>[upluscontrol|The unary "+" operator]
can be used to disqualify terms in the WHERE clause from use by indices.
Careful use of unary + can sometimes help prevent the query planner from
choosing a poor index without restricting it to using one specific index.
Careful placement of unary + operators is a better method for controlling 
which indices are used by a query.</p>

<li><p>The [sqlite3_stmt_status()] C/C++ interface together with the
[SQLITE_STMTSTATUS_FULLSCAN_STEP] and [SQLITE_STMTSTATUS_SORT] verbs
can be used to detect at run-time when an SQL statement is not
making effective use of indices.  Many applications may prefer to
use the [sqlite3_stmt_status()] interface to detect index misuse
rather than the INDEXED BY phrase described here.</p>

HEADING 2 {Manual Control Of Query Plans Using SQLITE_STAT Tables} \
  manctrl {Manual Control Of Query Plans Using SQLITE_STAT Tables}

PARAGRAPH {
  SQLite provides the ability for advanced programmers to exercise control
  over the query plan chosen by the optimizer. One method for doing this
  is to fudge the [ANALYZE] results in the [sqlite_stat1] and
  [sqlite_stat3] tables.  That approach is not recommended except
  for the one scenario described in the next paragraph.
}
PARAGRAPH {
  For a program that uses an SQLite database as its 
  [application file-format],
  when a new database instances is first created the [ANALYZE]
  command is ineffective because the database contain no data from which
  to gather statistics.  In that case, one could construct a large prototype
  database containing typical data during development and run the 
  [ANALYZE] command on this prototype database to gather statistics,
  then save the prototype statistics as part of the application.
  After deployment, when the application goes to create a new database file,
  it can run the [ANALYZE] command in order to create the [sqlite_stat1]
  and [sqlite_stat3] tables, then copy the precomputed statistics obtained
  from the prototype database into these new statistics tables.
  In that way, statistics from large working data sets can be preloaded
  into newly created application files.
}

HEADING 2 {Manual Control Of Query Plans Using CROSS JOIN} \
  crossjoin {Manual Control Of Query Plans Using CROSS JOIN} {CROSS JOIN}

HEADING 2 {Manual Control Of Query Plans Using SQLITE_STAT Tables} \
  manctrl {Manual Control Of Query Plans Using SQLITE_STAT Tables}

PARAGRAPH {
  SQLite provides the ability for advanced programmers to exercise control
  over the query plan chosen by the optimizer. One method for doing this
  is to fudge the [ANALYZE] results in the [sqlite_stat1], 
  [sqlite_stat3], and/or [sqlite_stat4] tables.  That approach is not 
  recommended except for the one scenario described in the next paragraph.
}
PARAGRAPH {
  For a program that uses an SQLite database as its 
  [application file-format],
  when a new database instance is first created the [ANALYZE]
  command is ineffective because the database contain no data from which
  to gather statistics.  In that case, one could construct a large prototype
  database containing typical data during development and run the 
  [ANALYZE] command on this prototype database to gather statistics,
  then save the prototype statistics as part of the application.
  After deployment, when the application goes to create a new database file,
  it can run the [ANALYZE] command in order to create the statistics
  tables, then copy the precomputed statistics obtained
  from the prototype database into these new statistics tables.
  In that way, statistics from large working data sets can be preloaded
  into newly created application files.
}

HEADING 2 {Manual Control Of Query Plans Using CROSS JOIN} \
  crossjoin {Manual Control Of Query Plans Using CROSS JOIN} {CROSS JOIN}

with content that describes a typical
database for your application - perhaps content that you extracted after
running ANALYZE on a well-populated template database in the lab.</p>

<li><p><b>Instrument your code.</b>
Add logic that lets you know quickly and easily which queries are taking
too much time.  Then work on just those specific queries.</p>










<li><p><b>Use the [CROSS JOIN] syntax to enforce a particular
loop nesting order on queries that might use low-quality indices in an
unanalyzed database.</b>
SQLite [treats the CROSS JOIN operator specially], forcing the table to 
the left to be an outer loop relative to the table on the right.</p>

with content that describes a typical
database for your application - perhaps content that you extracted after
running ANALYZE on a well-populated template database in the lab.</p>

<li><p><b>Instrument your code.</b>
Add logic that lets you know quickly and easily which queries are taking
too much time.  Then work on just those specific queries.</p>

<li><p><b>Use [unlikely()] and [likelihood()] SQL functions.</b>
SQLite normally assumes that terms in the WHERE clause that cannot be used
by indices have a strong probability of being true.  If this assumption
is incorrect, it could lead to a suboptimal query plan.  The
[unlikely()] and [likelihood()] SQL functions can be used to provide
hints to the query planner about WHERE clause terms that are probably
not true, and thus aid the query planner is selecting the best possible
plan.

<li><p><b>Use the [CROSS JOIN] syntax to enforce a particular
loop nesting order on queries that might use low-quality indices in an
unanalyzed database.</b>
SQLite [treats the CROSS JOIN operator specially], forcing the table to 
the left to be an outer loop relative to the table on the right.</p>