Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch expert-in-shell Excluding Merge-Ins
This is equivalent to a diff from 9cc758b326 to cd6f445782
2017-12-21
| ||
18:50 | Add docs for experimental ".expert" command. (check-in: 7f57c342de user: dan tags: trunk) | |
2017-12-19
| ||
17:34 | Add docs for a proposed addition to the SQLite shell tool. (Closed-Leaf check-in: cd6f445782 user: dan tags: expert-in-shell) | |
2017-12-16
| ||
17:20 | Add documentation for the "swarmvtab" extension. (check-in: 9cc758b326 user: dan tags: trunk) | |
2017-12-12
| ||
01:16 | Changes to walformat.html document. (check-in: c0a1fede37 user: drh tags: trunk) | |
Changes to pages/cli.in.
︙ | ︙ | |||
856 857 858 859 860 861 862 863 864 865 866 867 868 869 | <h2> SQLAR Update Command </h2> <p> This command works the same way as the --create command, except that it does not delete the current archive before commencing. New versions of files silently replace existing files with the same names, but otherwise the initial contents of the archive (if any) remain intact. <tcl>hd_fragment dotother</tcl> <h1>Other Dot Commands</h1> <p>There are many other dot-commands available in the command-line shell. See the ".help" command for a complete list for any particular version and build of SQLite. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 | <h2> SQLAR Update Command </h2> <p> This command works the same way as the --create command, except that it does not delete the current archive before commencing. New versions of files silently replace existing files with the same names, but otherwise the initial contents of the archive (if any) remain intact. <tcl>hd_fragment expert</tcl> <h1>Index Recommendations (SQLite Expert)</h1> <p>For most non-trivial SQL databases, the key to performance is creating the right SQL indexes. In this context "the right SQL indexes" means those that cause the queries that an application needs to optimize run fast. The ".expert" command can assist with this by proposing indexes that might assist with specific queries, were they present in the database. <p>The ".expert" command is issued first, followed by the SQL query on a separate line. For example, consider the following session: <codeblock> sqlite> CREATE TABLE x1(a, b, c); <i>-- Create table in database </i> sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; <i>-- Analyze this SELECT </i> CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?) sqlite> CREATE INDEX x1ab ON x1(a, b); <i>-- Create the recommeded index </i> sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; <i>-- Re-analyze the same SELECT </i> (no new indexes) 0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?) </codeblock> <p>In the above, the user creates the database schema (a single table - "x1"), and then uses the ".expert" command to analyze a query, in this case "SELECT * FROM x1 WHERE a=? AND b>?". The shell tool recommends that the user create a new index (index "x1_idx_000123a7") and outputs the plan that the query would use in [EXPLAIN QUERY PLAN] format. The user then creates an index with an equivalent schema and runs the analysis on the same query again. This time the shell tool does not recommend any new indexes, and outputs the plan that SQLite will use for the query given the existing indexes. <p>The ".expert" command accepts the following options: <table striped=1> <tr><th> Option <th> Purpose <tr><td> --verbose <td> If present, output a more verbose report for each query analyzed. <tr><td> --sample PERCENT <td> By default, the ".expert" command recommends indexes based on the query and database schema alone. This is similar to the way the [SQLite query planner] selects indexes for queries if the user has not run the [ANALYZE] command on the database to generate data distribution statistics. <div style="margin-top:1ex"> If this option is pass a non-zero argument, the ".expert" command generates similar data distribution statistics for all indexes considered based on PERCENT percent of the rows currently stored in each database table. For databases with unusual data distributions, this may lead to better index recommendations, particularly if the application intends to run ANALYZE. <div style="margin-top:1ex"> For small databases and modern CPUs, there is usually no reason not to pass "--sample 100". However, gathering data distribution statistics can be expensive for large database tables. If the operation is too slow, try passing a smaller value for the --sample option. </table> <p>Th functionality described in this section may be integrated into other applications or tools using the <a href="http://www.sqlite.org/src/dir?ci=trunk&name=ext/expert"> SQLite expert extension</a> code. <tcl>hd_fragment dotother</tcl> <h1>Other Dot Commands</h1> <p>There are many other dot-commands available in the command-line shell. See the ".help" command for a complete list for any particular version and build of SQLite. |
︙ | ︙ |