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: |
0998ee24a46368a20fb79e09ab925952 |
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
Changes to pages/lang.in.
︙ | ︙ | |||
208 209 210 211 212 213 214 | [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 | | > > | | | | | > > | | 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 | 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 | | > > | 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 | 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 | | | 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 | <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> | | | | 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 | 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 | | | | | | | | 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> |
︙ | ︙ |