Documentation Source Text

Check-in [498cd0709e]
Login

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

Overview
Comment:Updates to requirements marks.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 498cd0709e865203cb906cbd53cc30c5cb557ee9
User & Date: drh 2013-08-02 23:41:17.397
Context
2013-08-03
18:42
Always publish the most recent changes in releaselog/current.html. (check-in: 27f4c1dbbc user: drh tags: trunk)
2013-08-02
23:41
Updates to requirements marks. (check-in: 498cd0709e user: drh tags: trunk)
18:27
Enable partial indices. (check-in: f990657458 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/eqp.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18


19


20


21
22
23
24
25
26
27
<tcl>hd_keywords {explain query plan} {EXPLAIN QUERY PLAN}</tcl>
<title>EXPLAIN QUERY PLAN</title>
<style>codeblock { display:block;white-space:pre;font-family:fixed }</style>

<table_of_contents>

<h1>The EXPLAIN QUERY PLAN Command</h1>

<p style="margin-left:10ex;margin-right:10ex">
<b>Warning: The data returned by the EXPLAIN QUERY PLAN command is 
intended for interactive debugging only. It may change dramatically between
SQLite releases. Applications should not depend on the results of an EXPLAIN
QUERY PLAN command.</b>

<p>The [EXPLAIN|EXPLAIN QUERY PLAN] SQL command is used to obtain a high-level
description of the strategy or plan that SQLite uses to implement a specific
SQL query. Most significantly, it reports on the way in which the query uses
database indices. In interpreting and using this information to optimize 


database schemas and queries, users might find the documents describing how


SQLite [indexing|plans] and [optimizer|optimizes] queries useful.



<p>^An EXPLAIN QUERY PLAN command returns zero or more rows of four columns
each. ^The column names are "selectid", "order", "from", "detail". ^The
first three columns contain an integer value. ^The final
column, "detail", contains a text value which carries most of
the useful information.











|
|
|



|
|
>
>
|
>
>
|
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<tcl>hd_keywords {explain query plan} {EXPLAIN QUERY PLAN}</tcl>
<title>EXPLAIN QUERY PLAN</title>
<style>codeblock { display:block;white-space:pre;font-family:fixed }</style>

<table_of_contents>

<h1>The EXPLAIN QUERY PLAN Command</h1>

<p style="margin-left:10ex;margin-right:10ex">
<b>Warning: The data returned by the EXPLAIN QUERY PLAN command is 
intended for interactive debugging only.  The output format may change
between SQLite releases. Applications should not depend on the output
format of the EXPLAIN QUERY PLAN command.</b>

<p>The [EXPLAIN|EXPLAIN QUERY PLAN] SQL command is used to obtain a high-level
description of the strategy or plan that SQLite uses to implement a specific
SQL query.  Most significantly, EXPLAIN QUERY PLAN reports on the way in 
which the query uses database indices. This document is a guide to
understanding and interpreting the EXPLAIN QUERY PLAN output.  Background
information is available separately:

<ul>
<li> Notes on the [optimizer | query optimizer].
<li> How [indexing] works.
<li> The [next generation query planner].
</ul>

<p>^An EXPLAIN QUERY PLAN command returns zero or more rows of four columns
each. ^The column names are "selectid", "order", "from", "detail". ^The
first three columns contain an integer value. ^The final
column, "detail", contains a text value which carries most of
the useful information.

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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
  following information:

<ul>
  <li> The name of the table data is read from.
  <li> Whether or not an index or [automatic indexing|automatic index] is used.
  <li> Whether or not the [covering index] optimization applies.
  <li> Which terms of the WHERE clause are used for indexing.
  <li> The estimated number of rows that SQLite will visit.
</ul>)^

<p>
  For example, the following EXPLAIN QUERY PLAN command operates on a SELECT
  statement that is implemented by performing a full-table scan on table t1:
^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
    0|0|0|SCAN TABLE t1 (~100000 rows)
</codeblock>)^

<p>
  The example above shows
  SQLite estimating that the full-table scan will visit approximately 
  100,000 records. If the query were able to use an index, then the 
  SCAN/SEARCH record would include the name of the index and, for a
  SEARCH record, an indication of how the subset of rows visited is
  identified. For example:
^(<codeblock>
    sqlite&gt; CREATE INDEX i1 ON t1(a);
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
    0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
</codeblock>)^

<p>
  The previous example, SQLite uses index "i1" to optimize
  a WHERE clause term of the form (a=?) - in this case "a=1". SQLite 
  estimates that about 10 records will match the "a=1" term.
  The previous example could not use a [covering index], but the following
  example can, and that fact is reflected in the output:
^(<codeblock>
    sqlite&gt; CREATE INDEX i2 ON t1(a, b);
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
    0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
</codeblock>)^

<p>
  All joins in SQLite are [join order|implemented using nested scans]. When a
  SELECT query that features a join is analyzed using EXPLAIN QUERY PLAN, one
  SCAN or SEARCH record is output for each nested loop. For example:
^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
    0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)
    0|1|1|SCAN TABLE t2 (~1000000 rows)
</codeblock>)^

<p>
  ^The second column of output (column "order") indicates the nesting order. In
  this case, the scan of table t1 using index i2 is the outer loop (order=0)
  and the full-table scan of table t2 (order=1) is the inner loop. The third
  column (column "from"), indicates the position in the FROM clause of the
  SELECT statement that the table associated with each scan occurs in. In the
  case above, table t1 occupies the first position in the FROM clause, so the
  value of column "from" is 0 in the first record. Table t2 is in the
  second position, so the "from" column for the corresponding SCAN record is
  set to 1. In the following example, the positions of t1 and t2 in the FROM 
  clause of the SELECT are reversed. The query strategy remains the same, but
  the values in the "from" column of the output are adjusted accordingly.
^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
    0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)
    0|1|0|SCAN TABLE t2 (~1000000 rows)
</codeblock>)^

<p>
  In the example above, SQLite estimates that the outer loop scan will visit
  approximately 3 rows, and that the inner loop will visit
  approximately 1,000,000. If
  you observe that SQLite's estimates are wildly inaccurate (and appear to be
  causing it to generate sub-optimal query plans), your queries may benefit
  from running the [ANALYZE] command on the database.

<p>
  If the WHERE clause of a query contains an OR expression, then SQLite might
  use the [or-connected-terms|"OR by union"] strategy (also described 
  [or optimization|here]). In this case there will be two SEARCH records, one
  for each index, with the same values in both the "order" and "from" columns.
  For example: 
^(<codeblock>
    sqlite&gt; CREATE INDEX i3 ON t1(b);
    sqlite&gt; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
    0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
    0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
</codeblock>)^

<h2>Temporary Sorting B-Trees</h2>

<p>
  If a SELECT query contains an ORDER BY, GROUP BY or DISTINCT clause, 
  SQLite may need to use a temporary b-tree structure to sort the output
  rows. Or, it might [sorting|use an index]. Using an index is 
  almost always much more efficient than performing a sort.
  If a temporary b-tree is required, a record is added to the EXPLAIN
  QUERY PLAN output with the "detail" field set to a string value of
  the form "USE TEMP B-TREE FOR xxx", where xxx is one of "ORDER BY",
  "GROUP BY" or "DISTINCT". For example:

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
    0|0|0|SCAN TABLE t2 (~1000000 rows)
    0|0|0|USE TEMP B-TREE FOR ORDER BY
</codeblock>)^

<p>
  In this case using the temporary b-tree can be avoided by creating an index
  on t2(c), as follows:

^(<codeblock>
    sqlite&gt; CREATE INDEX i4 ON t2(c);
    sqlite&gt; EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
    0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows)
</codeblock>)^

<h2>Subqueries</h2>

<p>
  In all the examples above, the first column (column "selectid") is always
  set to 0. ^If a query contains sub-selects, either as part of the FROM
  clause or as part of SQL expressions, then the output of EXPLAIN QUERY
  PLAN also includes a report for each sub-select. ^Each sub-select is assigned
  a distinct, non-zero "selectid" value. ^The top-level SELECT statement is
  always assigned the selectid value 0. For example:

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
    0|0|0|SCAN TABLE t2 (~1000000 rows)
    0|0|0|EXECUTE SCALAR SUBQUERY 1
    1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
    0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
    2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
</codeblock>)^

<p>
  The example above contains a pair of scalar subqueries assigned selectid 
  values 1 and 2. As well as a SCAN record, there are also 2 "EXECUTE" 
  records associated with the top level subquery (selectid 0), indicating
  that subqueries 1 and 2 are executed by the top level query in a scalar







<







|












|











|








|
|
















|
|


<
<
<
<
<
<
<
<









|
|
















|










|














|

|

|







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
122
123
124
125








126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
  following information:

<ul>
  <li> The name of the table data is read from.
  <li> Whether or not an index or [automatic indexing|automatic index] is used.
  <li> Whether or not the [covering index] optimization applies.
  <li> Which terms of the WHERE clause are used for indexing.

</ul>)^

<p>
  For example, the following EXPLAIN QUERY PLAN command operates on a SELECT
  statement that is implemented by performing a full-table scan on table t1:
^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
    0|0|0|SCAN TABLE t1
</codeblock>)^

<p>
  The example above shows
  SQLite estimating that the full-table scan will visit approximately 
  100,000 records. If the query were able to use an index, then the 
  SCAN/SEARCH record would include the name of the index and, for a
  SEARCH record, an indication of how the subset of rows visited is
  identified. For example:
^(<codeblock>
    sqlite&gt; CREATE INDEX i1 ON t1(a);
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
    0|0|0|SEARCH TABLE t1 USING INDEX i1
</codeblock>)^

<p>
  The previous example, SQLite uses index "i1" to optimize
  a WHERE clause term of the form (a=?) - in this case "a=1". SQLite 
  estimates that about 10 records will match the "a=1" term.
  The previous example could not use a [covering index], but the following
  example can, and that fact is reflected in the output:
^(<codeblock>
    sqlite&gt; CREATE INDEX i2 ON t1(a, b);
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
    0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
</codeblock>)^

<p>
  All joins in SQLite are [join order|implemented using nested scans]. When a
  SELECT query that features a join is analyzed using EXPLAIN QUERY PLAN, one
  SCAN or SEARCH record is output for each nested loop. For example:
^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
    0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
    0|1|1|SCAN TABLE t2
</codeblock>)^

<p>
  ^The second column of output (column "order") indicates the nesting order. In
  this case, the scan of table t1 using index i2 is the outer loop (order=0)
  and the full-table scan of table t2 (order=1) is the inner loop. The third
  column (column "from"), indicates the position in the FROM clause of the
  SELECT statement that the table associated with each scan occurs in. In the
  case above, table t1 occupies the first position in the FROM clause, so the
  value of column "from" is 0 in the first record. Table t2 is in the
  second position, so the "from" column for the corresponding SCAN record is
  set to 1. In the following example, the positions of t1 and t2 in the FROM 
  clause of the SELECT are reversed. The query strategy remains the same, but
  the values in the "from" column of the output are adjusted accordingly.
^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
    0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
    0|1|0|SCAN TABLE t2
</codeblock>)^









<p>
  If the WHERE clause of a query contains an OR expression, then SQLite might
  use the [or-connected-terms|"OR by union"] strategy (also described 
  [or optimization|here]). In this case there will be two SEARCH records, one
  for each index, with the same values in both the "order" and "from" columns.
  For example: 
^(<codeblock>
    sqlite&gt; CREATE INDEX i3 ON t1(b);
    sqlite&gt; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
    0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
    0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
</codeblock>)^

<h2>Temporary Sorting B-Trees</h2>

<p>
  If a SELECT query contains an ORDER BY, GROUP BY or DISTINCT clause, 
  SQLite may need to use a temporary b-tree structure to sort the output
  rows. Or, it might [sorting|use an index]. Using an index is 
  almost always much more efficient than performing a sort.
  If a temporary b-tree is required, a record is added to the EXPLAIN
  QUERY PLAN output with the "detail" field set to a string value of
  the form "USE TEMP B-TREE FOR xxx", where xxx is one of "ORDER BY",
  "GROUP BY" or "DISTINCT". For example:

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
    0|0|0|SCAN TABLE t2
    0|0|0|USE TEMP B-TREE FOR ORDER BY
</codeblock>)^

<p>
  In this case using the temporary b-tree can be avoided by creating an index
  on t2(c), as follows:

^(<codeblock>
    sqlite&gt; CREATE INDEX i4 ON t2(c);
    sqlite&gt; EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
    0|0|0|SCAN TABLE t2 USING INDEX i4
</codeblock>)^

<h2>Subqueries</h2>

<p>
  In all the examples above, the first column (column "selectid") is always
  set to 0. ^If a query contains sub-selects, either as part of the FROM
  clause or as part of SQL expressions, then the output of EXPLAIN QUERY
  PLAN also includes a report for each sub-select. ^Each sub-select is assigned
  a distinct, non-zero "selectid" value. ^The top-level SELECT statement is
  always assigned the selectid value 0. For example:

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
    0|0|0|SCAN TABLE t2
    0|0|0|EXECUTE SCALAR SUBQUERY 1
    1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
    0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
    2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
</codeblock>)^

<p>
  The example above contains a pair of scalar subqueries assigned selectid 
  values 1 and 2. As well as a SCAN record, there are also 2 "EXECUTE" 
  records associated with the top level subquery (selectid 0), indicating
  that subqueries 1 and 2 are executed by the top level query in a scalar
206
207
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
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
  temporary table in place of the subquery to execute the parent query.)^ This
  is shown in the output of EXPLAIN QUERY PLAN by substituting a 
  "SCAN SUBQUERY" record for the "SCAN TABLE" record that normally appears
  for each element in the FROM clause. For example:

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
    1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)
    0|0|0|SCAN SUBQUERY 1 (~1000000 rows)
    0|0|0|USE TEMP B-TREE FOR GROUP BY
</codeblock>)^

<p>
  If the [flattening optimization] is used on a subquery in the FROM clause
  of a SELECT statement, then the output of EXPLAIN QUERY PLAN reflects this.
  For example, in the following there is no "SCAN SUBQUERY" record even though
  there is a subquery in the FROM clause of the top level SELECT. Instead, since
  the flattening optimization does apply in this case, the EXPLAIN QUERY PLAN
  report shows that the top level query is implemented using a nested loop join
  of tables t1 and t2.

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
    0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)
    0|1|1|SCAN TABLE t1 (~1000000 rows)
</codeblock>)^

<h2>Compound Queries</h2>

<p>
  ^Each component query of a [compound query] (UNION, UNION ALL, EXCEPT or 
  INTERSECT) is assigned its own selectid and reported on separately. ^A
  single record is output for the parent (compound query) identifying the
  operation, and whether or not a temporary b-tree is used to implement
  it. For example:

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
    1|0|0|SCAN TABLE t1 (~1000000 rows)
    2|0|0|SCAN TABLE t2 (~1000000 rows)
    0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
</codeblock>)^

<p>
  The "USING TEMP B-TREE" clause in the above output indicates that a 
  temporary b-tree structure is used to implement the UNION of the results
  of the two sub-selects. If the temporary b-tree were not required, as
  in the following example, the clause is not present.

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
    1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)
    2|0|0|SCAN TABLE t2 (~1000000 rows)
    2|0|0|USE TEMP B-TREE FOR ORDER BY
    0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
</codeblock>)^

<h1>Sample Code</h1>

<p>Sometimes, within a large application, it may be inconvenient to modify







|
|














|
|













|
|











|
|







203
204
205
206
207
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
250
251
252
253
254
255
256
257
258
259
260
261
262
  temporary table in place of the subquery to execute the parent query.)^ This
  is shown in the output of EXPLAIN QUERY PLAN by substituting a 
  "SCAN SUBQUERY" record for the "SCAN TABLE" record that normally appears
  for each element in the FROM clause. For example:

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
    1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
    0|0|0|SCAN SUBQUERY 1
    0|0|0|USE TEMP B-TREE FOR GROUP BY
</codeblock>)^

<p>
  If the [flattening optimization] is used on a subquery in the FROM clause
  of a SELECT statement, then the output of EXPLAIN QUERY PLAN reflects this.
  For example, in the following there is no "SCAN SUBQUERY" record even though
  there is a subquery in the FROM clause of the top level SELECT. Instead, since
  the flattening optimization does apply in this case, the EXPLAIN QUERY PLAN
  report shows that the top level query is implemented using a nested loop join
  of tables t1 and t2.

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
    0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?)
    0|1|1|SCAN TABLE t1
</codeblock>)^

<h2>Compound Queries</h2>

<p>
  ^Each component query of a [compound query] (UNION, UNION ALL, EXCEPT or 
  INTERSECT) is assigned its own selectid and reported on separately. ^A
  single record is output for the parent (compound query) identifying the
  operation, and whether or not a temporary b-tree is used to implement
  it. For example:

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
    1|0|0|SCAN TABLE t1
    2|0|0|SCAN TABLE t2
    0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
</codeblock>)^

<p>
  The "USING TEMP B-TREE" clause in the above output indicates that a 
  temporary b-tree structure is used to implement the UNION of the results
  of the two sub-selects. If the temporary b-tree were not required, as
  in the following example, the clause is not present.

^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
    1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
    2|0|0|SCAN TABLE t2
    2|0|0|USE TEMP B-TREE FOR ORDER BY
    0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
</codeblock>)^

<h1>Sample Code</h1>

<p>Sometimes, within a large application, it may be inconvenient to modify
Changes to pages/lang.in.
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
<p><b>4. Removal of duplicate rows (DISTINCT processing).</b>
<tcl>hd_fragment distinct</tcl>
<tcl>hd_keywords {DISTINCT}</tcl>

<p>^One of the ALL or DISTINCT keywords may follow the SELECT keyword in a 
simple SELECT statement. ^If the simple SELECT is a SELECT ALL, then the
entire set of result rows are returned by the SELECT. ^If neither ALL or
DISTINCT are present, then the behaviour is as if ALL were specified. 
^If the simple SELECT is a SELECT DISTINCT, then duplicate rows are removed
from the set of result rows before it is returned. ^For the purposes of
detecting duplicate rows, two NULL values are considered to be equal. ^The
normal rules for selecting a collation sequence to compare text values with
apply.

<h3>Compound Select Statements







|







3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
<p><b>4. Removal of duplicate rows (DISTINCT processing).</b>
<tcl>hd_fragment distinct</tcl>
<tcl>hd_keywords {DISTINCT}</tcl>

<p>^One of the ALL or DISTINCT keywords may follow the SELECT keyword in a 
simple SELECT statement. ^If the simple SELECT is a SELECT ALL, then the
entire set of result rows are returned by the SELECT. ^If neither ALL or
DISTINCT are present, then the behavior is as if ALL were specified. 
^If the simple SELECT is a SELECT DISTINCT, then duplicate rows are removed
from the set of result rows before it is returned. ^For the purposes of
detecting duplicate rows, two NULL values are considered to be equal. ^The
normal rules for selecting a collation sequence to compare text values with
apply.

<h3>Compound Select Statements
Changes to pages/pragma.in.
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
}

Pragma mmap_size {
    <p>^(<br><b>PRAGMA </b><i>database</i><b>.mmap_size;
         <br>PRAGMA </b><i>database</i><b>.mmap_size=</b><i>N</i></p>

    <p>Query or change the maximum number of bytes that are set
       aside for memory-mapped I/O on a single database.  The first form
       (without an argument) queries the current limit.  The second
       form (with a numeric argument) sets the limit for the specified
       database, or for all databases if the optional database name is
       omitted.  In the second form, if the database name is omitted, the
       limit that is set becomes the default limit for all databases that
       are added to the [database connection] by subsequent [ATTACH]
       statements.</p>

    <p>The argument N is the maximum number of bytes of the database file
       that will be accessed using memory-mapped I/O.  If N is zero then
       memory mapped I/O is disabled.  If N is negative, then the limit
       reverts to the default value determined by the most recent
       [sqlite3_config]([SQLITE_CONFIG_MMAP_SIZE]), or to the compile
       time default determined by [SQLITE_DEFAULT_MMAP_SIZE] if not
       start-time limit has been set.</p>

    <p>The [PRAGMA mmap_size] statement will never increase the amount
       of address space used for memory-mapped I/O above the







|
|


|




|
|
|







644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
}

Pragma mmap_size {
    <p>^(<br><b>PRAGMA </b><i>database</i><b>.mmap_size;
         <br>PRAGMA </b><i>database</i><b>.mmap_size=</b><i>N</i></p>

    <p>Query or change the maximum number of bytes that are set
       aside for memory-mapped I/O on a single database.)^  ^The first form
       (without an argument) queries the current limit.  ^The second
       form (with a numeric argument) sets the limit for the specified
       database, or for all databases if the optional database name is
       omitted.  ^In the second form, if the database name is omitted, the
       limit that is set becomes the default limit for all databases that
       are added to the [database connection] by subsequent [ATTACH]
       statements.</p>

    <p>^The argument N is the maximum number of bytes of the database file
       that will be accessed using memory-mapped I/O.  ^If N is zero then
       memory mapped I/O is disabled.  ^If N is negative, then the limit
       reverts to the default value determined by the most recent
       [sqlite3_config]([SQLITE_CONFIG_MMAP_SIZE]), or to the compile
       time default determined by [SQLITE_DEFAULT_MMAP_SIZE] if not
       start-time limit has been set.</p>

    <p>The [PRAGMA mmap_size] statement will never increase the amount
       of address space used for memory-mapped I/O above the