Documentation Source Text

Check-in [0998ee24a4]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to documentation for recent changes, especially the unlikely() function and STAT4. Also fix some typos.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0998ee24a46368a20fb79e09ab925952ba6150d8
User & Date: drh 2013-10-19 16:21:02.007
Context
2013-10-27
03:44
Fix a typo in the documentation for the ABS() function. (check-in: 9e1d78e903 user: drh tags: trunk)
2013-10-19
16:21
Updates to documentation for recent changes, especially the unlikely() function and STAT4. Also fix some typos. (check-in: 0998ee24a4 user: drh tags: trunk)
15:21
Add documentation on the new SQLITE_USE_FCNTL_TRACE compile-time option. (check-in: 67d47ef62f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
208
209
210
211
212
213
214
215


216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237


238
239
240
241
242
243
244
245
[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>
##############################################################################







|
>
>


|
|


|
|













|
>
>
|







208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
[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>
##############################################################################
590
591
592
593
594
595
596
597


598
599
600
601
602
603
604
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







|
>
>







594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
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
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
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







|







2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
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
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
3623
3624
3625
3626
3627

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







|




|







3614
3615
3616
3617
3618
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633

<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>
Changes to pages/optoverview.in.
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642

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}







|
|
|




|






|
|







613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642

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}
Changes to pages/queryplanner-ng.in.
655
656
657
658
659
660
661









662
663
664
665
666
667
668
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>








>
>
>
>
>
>
>
>
>







655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
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>