Documentation Source Text
Check-in [4e286cee85]
Not logged in

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

Overview
SHA1 Hash:4e286cee85a1fc3626375ba1932fcfa3203fdaef
Date: 2013-08-30 14:28:53
User: drh
Comment:Added a 3.8.1 change log. Added documentation on SQLITE_MINIMUM_FILE_DESCRIPTOR.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in

42
43
44
45
46
47
48


































































49
50
51
52
53
54
55
    hd_enable_main 1
    incr nChng
    if {$nChng==1 && [file exists $DEST/$filename]} {
      file copy -force $DEST/$filename $DEST/releaselog/current.html
    }
  }
}



































































chng {2013-08-26 (3.8.0)} {
<li>Add support for [partial indexes]</li>
<li>Cut-over to the [next generation query planner] for faster and better query plans.
<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    rows generated by each loop in a join.
<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
    hd_enable_main 1
    incr nChng
    if {$nChng==1 && [file exists $DEST/$filename]} {
      file copy -force $DEST/$filename $DEST/releaselog/current.html
    }
  }
}

chng {2013-10-?? (3.8.1)} {
<li>Add support for SQLITE_ENABLE_STAT4
<li>Add the [SQLITE_MINIMUM_FILE_DESCRIPTOR] compile-time option
<li>Add the win32-longpath VFS on windows.
}

chng {2013-08-29 (3.8.0.1)} {
<li>Add support for [partial indexes]</li>
<li>Cut-over to the [next generation query planner] for faster and better query plans.
<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    rows generated by each loop in a join.
<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.
<li>Added the [SQLITE_STMTSTATUS_VM_STEP] option to [sqlite3_stmt_status()].
<li>Added the [cache_spill pragma].
<li>Added the [query_only pragma].
<li>Added the [defer_foreign_keys pragma] and the
    [sqlite3_db_status](db, [SQLITE_DBSTATUS_DEFERRED_FKS],...) C-language interface.
<li>Added the "percentile()" function as a [loadable extension] in the ext/misc
    subdirectory of the source tree.
<li>Added the [SQLITE_ALLOW_URI_AUTHORITY] compile-time option.
<li>Add the [sqlite3_cancel_auto_extension(X)] interface.
<li>A running SELECT statement that lacks a FROM clause (or any other statement that
    never reads or writes from any database file) will not prevent a read
    transaction from closing.
<li>Add the [SQLITE_DEFAULT_AUTOMATIC_INDEX] compile-time option.  Setting this option
    to 0 disables automatic indices by default.
<li>Issue an [SQLITE_WARNING_AUTOINDEX] warning on the [SQLITE_CONFIG_LOG] whenever
    the query planner uses an automatic index.
<li>Added the [SQLITE_FTS3_MAX_EXPR_DEPTH] compile-time option.
<li>Added an optional 5th parameter defining the collating sequence to the 
    next_char() extension SQL function.
<li>The [SQLITE_BUSY_SNAPSHOT] extended error code is returned in WAL mode when
    a read transaction cannot be upgraded to a write transaction because the read is
    on an older snapshot.
<li>Enhancements to the sqlite3_analyzer utility program to provide size
    information separately for each individual index of a table, in addition to
    the aggregate size.
<li>Allow read transactions to be freely opened and closed by SQL statements run 
    from within the implementation of [application-defined SQL functions] if the
    function is called by a SELECT statement that does not access any database table.
<li>Disable the use of posix_fallocate() on all (unix) systems unless the
    HAVE_POSIX_FALLOCATE compile-time option is used.
<li>Update the ".import" command in the [command-line shell] to support multi-line
    fields and correct RFC-4180 quoting and to issue warning and/or error messages
    if the input text is not strictly RFC-4180 compliant.
<li>Bug fix: In the [unicode61] tokenizer of [FTS4], treat all private code points
    as identifier symbols.
<li>Bug fix: Bare identifiers in ORDER BY clauses bind more tightly to output column
    names, but identifiers in expressions bind more tightly to input column names.
    Identifiers in GROUP BY clauses always prefer output column names, however.
<li>Bug fixes: Multiple problems in the legacy query optimizer were fixed by the 
    move to [NGQP].
</ul><p>The above are changes since [version 3.7.17].  The differences
between 3.8.0 and 3.8.0.1 are as follows:</p><ul>
<li>Fix an off-by-one error that caused quoted empty string at the end of a 
CRNL-terminated line of CSV input to be misread by the command-line shell.
<li>Fix a query planner bug involving a LEFT JOIN with a BETWEEN or LIKE/GLOB
constraint and then another INNER JOIN to the right that involves an OR constraint.
<li>Fix a query planner bug that could result in a segfault when querying tables
with a UNIQUE or PRIMARY KEY constraint with more than four columns.

<li>SQLITE_SOURCE_ID: 
    "2013-08-29 17:35:01 352362bc01660edfbda08179d60f09e2038a2f49"
<li>SHA1 for sqlite3.c: 99906bf63e6cef63d6f3d7f8526ac4a70e76559e
}

chng {2013-08-26 (3.8.0)} {
<li>Add support for [partial indexes]</li>
<li>Cut-over to the [next generation query planner] for faster and better query plans.
<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    rows generated by each loop in a join.
<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.

Changes to pages/changes.in

42
43
44
45
46
47
48






49
50
51
52
53
54
55
    hd_enable_main 1
    incr nChng
    if {$nChng==1 && [file exists $DEST/$filename]} {
      file copy -force $DEST/$filename $DEST/releaselog/current.html
    }
  }
}







chng {2013-08-29 (3.8.0.1)} {
<li>Add support for [partial indexes]</li>
<li>Cut-over to the [next generation query planner] for faster and better query plans.
<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    rows generated by each loop in a join.
<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.







>
>
>
>
>
>







42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
    hd_enable_main 1
    incr nChng
    if {$nChng==1 && [file exists $DEST/$filename]} {
      file copy -force $DEST/$filename $DEST/releaselog/current.html
    }
  }
}

chng {2013-10-?? (3.8.1)} {
<li>Add support for SQLITE_ENABLE_STAT4
<li>Add the [SQLITE_MINIMUM_FILE_DESCRIPTOR] compile-time option
<li>Add the win32-longpath VFS on windows.
}

chng {2013-08-29 (3.8.0.1)} {
<li>Add support for [partial indexes]</li>
<li>Cut-over to the [next generation query planner] for faster and better query plans.
<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    rows generated by each loop in a join.
<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.

Changes to pages/compile.in

191
192
193
194
195
196
197













198
199
200
201
202
203
204
  in that if one thread is constantly changing the schema, another thread
  might spin on reparses and repreparations of a prepared statement and
  never get any real work done.  This parameter prevents an infinite loop
  by forcing the spinning thread to give up after a fixed number of attempts
  at recompiling the prepared statement.  The default setting is 50 which is
  more than adequate for most applications.
}














COMPILE_OPTION {SQLITE_POWERSAFE_OVERWRITE=<i>&lt;0 or 1&gt;</i>} {
  This option changes the default assumption about [powersafe overwrite]
  for the underlying filesystems for the unix and windows [VFSes].
  Setting SQLITE_POWERSAFE_OVERWRITE to 1 causes SQLite to assume that
  application-level writes cannot changes bytes outside the range of
  bytes written even if the write occurs just before a power loss.







>
>
>
>
>
>
>
>
>
>
>
>
>







191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
  in that if one thread is constantly changing the schema, another thread
  might spin on reparses and repreparations of a prepared statement and
  never get any real work done.  This parameter prevents an infinite loop
  by forcing the spinning thread to give up after a fixed number of attempts
  at recompiling the prepared statement.  The default setting is 50 which is
  more than adequate for most applications.
}

COMPILE_OPTION {SQLITE_MINIMUM_FILE_DESCRIPTOR=<i>N</i>} {
  The unix [VFS] will never use a file descriptor less than <i>N</i>.  The
  default value of <i>N</i> is 3.
  <p>
  Avoiding the use of low-numbered file descriptors is a defense against
  accidental database corruption.  If a database file was opened using
  file descriptor 2, for example, and then an assert() failed and invoked
  write(2,...), that would likely cause database corruption.  Using only
  higher-valued file descriptors avoids that problem.  The protection against
  using low-numbered file descriptiors can be disabled by setting this
  compile-time option to 0.
}

COMPILE_OPTION {SQLITE_POWERSAFE_OVERWRITE=<i>&lt;0 or 1&gt;</i>} {
  This option changes the default assumption about [powersafe overwrite]
  for the underlying filesystems for the unix and windows [VFSes].
  Setting SQLITE_POWERSAFE_OVERWRITE to 1 causes SQLite to assume that
  application-level writes cannot changes bytes outside the range of
  bytes written even if the write occurs just before a power loss.

Changes to pages/compile.in

191
192
193
194
195
196
197













198
199
200
201
202
203
204
...
590
591
592
593
594
595
596











597
598
599
600
601
602
603
  in that if one thread is constantly changing the schema, another thread
  might spin on reparses and repreparations of a prepared statement and
  never get any real work done.  This parameter prevents an infinite loop
  by forcing the spinning thread to give up after a fixed number of attempts
  at recompiling the prepared statement.  The default setting is 50 which is
  more than adequate for most applications.
}














COMPILE_OPTION {SQLITE_POWERSAFE_OVERWRITE=<i>&lt;0 or 1&gt;</i>} {
  This option changes the default assumption about [powersafe overwrite]
  for the underlying filesystems for the unix and windows [VFSes].
  Setting SQLITE_POWERSAFE_OVERWRITE to 1 causes SQLite to assume that
  application-level writes cannot changes bytes outside the range of
  bytes written even if the write occurs just before a power loss.
................................................................................
  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_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.







>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>







191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
...
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
  in that if one thread is constantly changing the schema, another thread
  might spin on reparses and repreparations of a prepared statement and
  never get any real work done.  This parameter prevents an infinite loop
  by forcing the spinning thread to give up after a fixed number of attempts
  at recompiling the prepared statement.  The default setting is 50 which is
  more than adequate for most applications.
}

COMPILE_OPTION {SQLITE_MINIMUM_FILE_DESCRIPTOR=<i>N</i>} {
  The unix [VFS] will never use a file descriptor less than <i>N</i>.  The
  default value of <i>N</i> is 3.
  <p>
  Avoiding the use of low-numbered file descriptors is a defense against
  accidental database corruption.  If a database file was opened using
  file descriptor 2, for example, and then an assert() failed and invoked
  write(2,...), that would likely cause database corruption.  Using only
  higher-valued file descriptors avoids that problem.  The protection against
  using low-numbered file descriptiors can be disabled by setting this
  compile-time option to 0.
}

COMPILE_OPTION {SQLITE_POWERSAFE_OVERWRITE=<i>&lt;0 or 1&gt;</i>} {
  This option changes the default assumption about [powersafe overwrite]
  for the underlying filesystems for the unix and windows [VFSes].
  Setting SQLITE_POWERSAFE_OVERWRITE to 1 causes SQLite to assume that
  application-level writes cannot changes bytes outside the range of
  bytes written even if the write occurs just before a power loss.
................................................................................
  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.

Changes to pages/fileformat2.in

1233
1234
1235
1236
1237
1238
1239
1240

1241
1242
1243



1244
1245
1246
1247
1248
1249
1250
....
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271

































1272
1273
1274
1275


1276





1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
<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 created and is only used if SQLite is compiled

with [SQLITE_ENABLE_STAT3] 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.



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>Future versions of SQLite might change to store a string containing
multiple integers values in the sqlite_stat3.nDLt column, a string in which
the first integer will be the number of prior index entries that are
distinct in the left-most column, the second integer is the number of

































prior index entries that are distinct in the first two columns, the
third integer is the number of prior index entries that are distinct
in the first three columns, and so forth  With such an extension, the
nDLt field will become similar in function to the sqlite_stat1.stat field.








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







|
>
|
|

>
>
>







 







|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
<
>
>

>
>
>
>
>
|
|









1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
....
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309



1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
<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.

Changes to pages/index.in

91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_8_0.html">Version 3.8.0</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.17 is optional.
Upgrading from all other prior versions of SQLite
is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>







|

|







91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_8_1.html">Version 3.8.1</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.17 and 3.8.0.1 is optional.
Upgrading from all other prior versions of SQLite
is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>

Changes to pages/index.in

91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_8_0_1.html">Version 3.8.0.1</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.17 and 3.8.0 is optional.
Upgrading from all other prior versions of SQLite
is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>







|

|







91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_8_1.html">Version 3.8.1</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.17 and 3.8.0.1 is optional.
Upgrading from all other prior versions of SQLite
is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>

Changes to pages/lang.in

197
198
199
200
201
202
203

204
205



206
207
208
209
210
211
212
213
214
215
216
217
218
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, then additional histogram data is
collected and stored in [sqlite_stat3].



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 "3".</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







>
|

>
>
>





|







197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
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

Changes to pages/news.in

14
15
16
17
18
19
20





21
22
23
24
25
26
27
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}






newsitem {2013-08-26} {Release 3.8.0} {
  <b>Do not fear the zero!</b>

  <p>SQLite [version 3.8.0] might easily have been called "3.7.18" instead.
  However, this release features the cutover of the
  [next generation query planner] or [NGQP], and there is a small chance of







>
>
>
>
>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2013-08-29} {Release 3.8.0.1} {
  <p>SQLite [version 3.8.0.1] fixes some obscure bugs that were uncovered by
  users in the 3.8.0 release.  Changes from 3.8.0 are minimal.
}

newsitem {2013-08-26} {Release 3.8.0} {
  <b>Do not fear the zero!</b>

  <p>SQLite [version 3.8.0] might easily have been called "3.7.18" instead.
  However, this release features the cutover of the
  [next generation query planner] or [NGQP], and there is a small chance of

Changes to pages/optoverview.in

794
795
796
797
798
799
800
801

802
803
804




805
806
807
808
809
810
811
  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].  ^The [SQLITE_ENABLE_STAT3] option causes

  the [ANALYZE] command to collect a histogram of column content in the
  [sqlite_stat3] table and to use this histogram to make a better
  guess at the best query to use for range constraints such as the above.




}
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







|
>

|
|
>
>
>
>







794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
  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

Changes to pages/queryplanner-ng.in

202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
...
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
...
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
...
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
...
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
nodes in the graph.</p>

<p>The problem of finding the best query plan is equivalent to finding
a minimum-cost path through the graph that visits each node
exactly once.</p>

<p>(Side note:  The costs estimates in the TPC-H Q8 graph were computed
by the query planner in SQLite 3.7.16 and converted using a base-10 logarithm.)
</p>

<h3>3.2 Complications</h3>

<p>The presentation of the query planner problem above is a simplification.
The costs are estimates.  We cannot
know what the true cost of running a loop is until we actually run the loop.
................................................................................
GROUP BY, or DISTINCT clause. So for TPC-H Q8,
the graph above is a reasonable representation of what needs to be computed.
The general case involves a lot of extra complication, which for clarity
is neglected in the remainder of this article.</p>

<h3>3.3 Finding The Best Query Plan</h3>

<p>Prior to version 3.8.0, SQLite always used the
the "Nearest Neighbor" or "NN" heuristic when searching for the best query plan.
The NN heuristic makes a single traversal of the graph, always choosing
the lowest-cost arc as the next step.  
The NN heuristic works surprisingly well in most cases.
And NN is fast, so that SQLite is able to quickly find good plans
for even large 64-way joins.  In contrast, other SQL database engines that
do more extensive searching tend to bog down when the
................................................................................
The notation
in the previous sentence means that the R table is run in the outer loop,
N1 is in the next inner loop, N2 is in the third loop, and so forth down
to P which is in the inner-most loop.  The shortest path through the
graph (as found via exhaustive search) is  P-L-O-C-N1-R-S-N2
with a cost of 27.38.  The difference might not seem like much, but 
remember that
the costs are logarithmic, so the shortest path is nearly 14,000 times
faster than that path found using the NN heuristic.</p>

<p>One solution to this problem is to change SQLite to do an exhaustive
search for the best path.  But an exhaustive search requires time 
proportional to
K! (where K is the number of tables in the join) and so when you get 
beyond a 10-way join, the time
................................................................................
<center>
<img src="images/qp/fqp1.gif">
</center>

<p>
In the "without ANALYZE" case on the left, the NN algorithm chooses 
loop P (PLINK) as the outer loop because 4.9 is less than 5.2, resulting
in path P-T which is algorithm-1. NN only looks a the single best choice
at each step so it completely misses the fact that 
5.2+4.4 makes a slightly cheaper plan than 4.9+4.8. But the N3 algorithm
keeps track of the 5 best paths for a 2-way join, so it ends up
selecting path T-P because of its slightly lower overall cost.
Path T-P is algorithm-2.
</p>

................................................................................
in the TPC-H Q8 graph.)</p>

<h3>4.2 Fixing The Problem</h3>

<p>Running [ANALYZE] on the repository database immediately fixed the
performance problem.  However, we want Fossil to be robust and to always
work quickly regardless of whether or not its repository has been analyzed.
For this reason, the query was modify to use the CROSS JOIN operator 
instead of the plain JOIN operator.
SQLite will not reorder the tables of a CROSS JOIN.
This is a long-standing feature of SQLite that is specifically designed
to allow knowledgeable programmers
to enforce a particular loop nesting order.  Once the join
was changed to CROSS JOIN (the addition of a single keyword) the NGQP was
forced to chose the faster algorithm-1 regardless of whether or not







|







 







|







 







|







 







|







 







|







202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
...
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
...
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
...
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
...
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
nodes in the graph.</p>

<p>The problem of finding the best query plan is equivalent to finding
a minimum-cost path through the graph that visits each node
exactly once.</p>

<p>(Side note:  The costs estimates in the TPC-H Q8 graph were computed
by the query planner in SQLite 3.7.16 and converted using a natural logarithm.)
</p>

<h3>3.2 Complications</h3>

<p>The presentation of the query planner problem above is a simplification.
The costs are estimates.  We cannot
know what the true cost of running a loop is until we actually run the loop.
................................................................................
GROUP BY, or DISTINCT clause. So for TPC-H Q8,
the graph above is a reasonable representation of what needs to be computed.
The general case involves a lot of extra complication, which for clarity
is neglected in the remainder of this article.</p>

<h3>3.3 Finding The Best Query Plan</h3>

<p>Prior to version 3.8.0, SQLite always used
the "Nearest Neighbor" or "NN" heuristic when searching for the best query plan.
The NN heuristic makes a single traversal of the graph, always choosing
the lowest-cost arc as the next step.  
The NN heuristic works surprisingly well in most cases.
And NN is fast, so that SQLite is able to quickly find good plans
for even large 64-way joins.  In contrast, other SQL database engines that
do more extensive searching tend to bog down when the
................................................................................
The notation
in the previous sentence means that the R table is run in the outer loop,
N1 is in the next inner loop, N2 is in the third loop, and so forth down
to P which is in the inner-most loop.  The shortest path through the
graph (as found via exhaustive search) is  P-L-O-C-N1-R-S-N2
with a cost of 27.38.  The difference might not seem like much, but 
remember that
the costs are logarithmic, so the shortest path is nearly 750 times
faster than that path found using the NN heuristic.</p>

<p>One solution to this problem is to change SQLite to do an exhaustive
search for the best path.  But an exhaustive search requires time 
proportional to
K! (where K is the number of tables in the join) and so when you get 
beyond a 10-way join, the time
................................................................................
<center>
<img src="images/qp/fqp1.gif">
</center>

<p>
In the "without ANALYZE" case on the left, the NN algorithm chooses 
loop P (PLINK) as the outer loop because 4.9 is less than 5.2, resulting
in path P-T which is algorithm-1. NN only looks at the single best choice
at each step so it completely misses the fact that 
5.2+4.4 makes a slightly cheaper plan than 4.9+4.8. But the N3 algorithm
keeps track of the 5 best paths for a 2-way join, so it ends up
selecting path T-P because of its slightly lower overall cost.
Path T-P is algorithm-2.
</p>

................................................................................
in the TPC-H Q8 graph.)</p>

<h3>4.2 Fixing The Problem</h3>

<p>Running [ANALYZE] on the repository database immediately fixed the
performance problem.  However, we want Fossil to be robust and to always
work quickly regardless of whether or not its repository has been analyzed.
For this reason, the query was modified to use the CROSS JOIN operator 
instead of the plain JOIN operator.
SQLite will not reorder the tables of a CROSS JOIN.
This is a long-standing feature of SQLite that is specifically designed
to allow knowledgeable programmers
to enforce a particular loop nesting order.  Once the join
was changed to CROSS JOIN (the addition of a single keyword) the NGQP was
forced to chose the faster algorithm-1 regardless of whether or not

Changes to pages/queryplanner-ng.in

99
100
101
102
103
104
105
106

107
108
109
110
111
112
113

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







|
>







99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

<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