*** DRAFT ***

SQLite Requirement Matrix Details
eqp.html

Index Summary Markup Original


R-28902-17274-01624-42364-46386-62149-20745-43372 tcl slt th3 src

EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may also appear with other statements that read data from database tables (e.g. UPDATE, DELETE, INSERT INTO ... SELECT).

/* IMP: R-28902-17274 */
# EVIDENCE-OF: R-28902-17274 EXPLAIN QUERY PLAN is most useful on a
# SELECT statement, but may also appear with other statements that read
# data from database tables (e.g. UPDATE, DELETE, INSERT INTO ...
# SELECT).

R-43064-28843-60545-11344-25729-47633-22876-03755 tcl slt th3 src

For each table read by the query, the output of EXPLAIN QUERY PLAN includes a record for which the value in the "detail" column begins with either "SCAN" or "SEARCH".

/* IMP: R-43064-28843 */
# EVIDENCE-OF: R-43064-28843 For each table read by the query, the
# output of EXPLAIN QUERY PLAN includes a record for which the value in
# the "detail" column begins with either "SCAN" or "SEARCH".

R-54961-28617-11438-13073-24028-16766-12231-36247 tcl slt th3 src

"SCAN" is used for a full-table scan, including cases where SQLite iterates through all records in a table in an order defined by an index.

/* IMP: R-54961-28617 */
# EVIDENCE-OF: R-54961-28617 "SCAN" is used for a full-table scan,
# including cases where SQLite iterates through all records in a table
# in an order defined by an index.

R-38242-53217-63508-42050-45799-27349-00700-51305 tcl slt th3 src

"SEARCH" indicates that only a subset of the table rows are visited.

/* IMP: R-38242-53217 */
# EVIDENCE-OF: R-38242-53217 "SEARCH" indicates that only a subset of
# the table rows are visited.

R-42029-48346-24855-30902-19718-06307-39903-13955 tcl slt th3 src

Each SCAN or SEARCH record includes the following information:

/* IMP: R-42029-48346 */
# EVIDENCE-OF: R-42029-48346 Each SCAN or SEARCH record includes the
# following information: The name of the table, view, or subquery that
# data is read from. Whether or not an index or automatic index is used.
# Whether or not the covering index optimization applies. Which terms of
# the WHERE clause are used for indexing.

R-23667-57149-13692-40760-52077-20986-45375-02136 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SCAN t1

/* IMP: R-23667-57149 */
# EVIDENCE-OF: R-23667-57149 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1; QUERY PLAN `--SCAN t1

R-16170-02914-14290-07150-03100-60317-35403-36262 tcl slt th3 src

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING INDEX i1 (a=?)

/* IMP: R-16170-02914 */
# EVIDENCE-OF: R-16170-02914 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; QUERY
# PLAN `--SEARCH t1 USING INDEX i1 (a=?)

R-31214-22204-30542-52251-23834-20094-62182-37235 tcl slt th3 src

sqlite> CREATE INDEX i2 ON t1(a, b);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX i2 (a=?)

/* IMP: R-31214-22204 */
# EVIDENCE-OF: R-31214-22204 sqlite> CREATE INDEX i2 ON t1(a, b);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; QUERY
# PLAN `--SEARCH t1 USING COVERING INDEX i2 (a=?)

R-57880-35169-30628-02848-50703-50828-58750-28510 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

/* IMP: R-57880-35169 */
# EVIDENCE-OF: R-57880-35169 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; QUERY PLAN |--SEARCH t1
# USING INDEX i2 (a=? AND b>?) `--SCAN t2

R-12621-54688-01749-38007-62215-41521-15622-18085 tcl slt th3 src

The order of the entries indicates the nesting order.

/* IMP: R-12621-54688 */
# EVIDENCE-OF: R-12621-54688 The order of the entries indicates the
# nesting order.

R-01569-01564-08645-29195-59730-50441-26159-21102 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

/* IMP: R-01569-01564 */
# EVIDENCE-OF: R-01569-01564 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; QUERY PLAN |--SEARCH t1
# USING INDEX i2 (a=? AND b>?) `--SCAN t2

R-06511-19994-39421-53412-16339-26783-08670-41596 tcl slt th3 src

sqlite> CREATE INDEX i3 ON t1(b);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
QUERY PLAN
`--MULTI-INDEX OR
   |--SEARCH t1 USING COVERING INDEX i2 (a=?)
   `--SEARCH t1 USING INDEX i3 (b=?)

/* IMP: R-06511-19994 */
# EVIDENCE-OF: R-06511-19994 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; QUERY
# PLAN `--MULTI-INDEX OR |--SEARCH t1 USING COVERING INDEX i2 (a=?)
# `--SEARCH t1 USING INDEX i3 (b=?)

R-00068-42258-60542-46185-56559-40653-31263-18427 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
|--SCAN t2
`--USE TEMP B-TREE FOR ORDER BY

/* IMP: R-00068-42258 */
# EVIDENCE-OF: R-00068-42258 sqlite> EXPLAIN QUERY PLAN SELECT c, d
# FROM t2 ORDER BY c; QUERY PLAN |--SCAN t2 `--USE TEMP B-TREE FOR ORDER
# BY

R-28198-38283-36055-63639-34430-05979-02434-46737 tcl slt th3 src

sqlite> CREATE INDEX i4 ON t2(c);
sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
QUERY PLAN
`--SCAN t2 USING INDEX i4

/* IMP: R-28198-38283 */
# EVIDENCE-OF: R-28198-38283 sqlite> CREATE INDEX i4 ON t2(c);
# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; QUERY
# PLAN `--SCAN t2 USING INDEX i4

R-40094-47392-07559-50532-14131-32118-23991-53984 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
|--SCAN TABLE t2 USING COVERING INDEX i4
|--SCALAR SUBQUERY
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
`--CORRELATED SCALAR SUBQUERY
   `--SEARCH t1 USING INDEX i3 (b=?)

/* IMP: R-40094-47392 */
# EVIDENCE-OF: R-40094-47392 sqlite> EXPLAIN QUERY PLAN SELECT
# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
# |--SCAN TABLE t2 USING COVERING INDEX i4 |--SCALAR SUBQUERY |
# `--SEARCH t1 USING COVERING INDEX i2 (a=?) `--CORRELATED SCALAR
# SUBQUERY `--SEARCH t1 USING INDEX i3 (b=?)

R-40969-30202-57015-62747-35327-16253-50512-36730 tcl slt th3 src

Unless the flattening optimization is applied, if a subquery appears in the FROM clause of a SELECT statement, SQLite can either run the subquery and stores the results in a temporary table, or it can run the subquery as a co-routine. The following query is an example of the latter. The subquery is run by a co-routine. The outer query blocks whenever it needs another row of input from the subquery. Control switches to the co-routine which produces the desired output row, then control switches back to the main routine which continues processing. ^(

sqlite> EXPLAIN QUERY PLAN SELECT count(*)
      > FROM (SELECT max(b) AS x FROM t1 GROUP BY a) AS qqq
      > GROUP BY x;
QUERY PLAN
|--CO-ROUTINE qqq
|  `--SCAN t1 USING COVERING INDEX i2
|--SCAN qqqq
`--USE TEMP B-TREE FOR GROUP BY

/* IMP: R-40969-30202 */
# EVIDENCE-OF: R-40969-30202 Unless the flattening optimization is
# applied, if a subquery appears in the FROM clause of a SELECT
# statement, SQLite can either run the subquery and stores the results
# in a temporary table, or it can run the subquery as a co-routine. The
# following query is an example of the latter. The subquery is run by a
# co-routine. The outer query blocks whenever it needs another row of
# input from the subquery. Control switches to the co-routine which
# produces the desired output row, then control switches back to the
# main routine which continues processing. ^(sqlite> EXPLAIN QUERY
# PLAN SELECT count(*) > FROM (SELECT max(b) AS x FROM t1 GROUP BY a)
# AS qqq > GROUP BY x; QUERY PLAN |--CO-ROUTINE qqq | `--SCAN t1
# USING COVERING INDEX i2 |--SCAN qqqq `--USE TEMP B-TREE FOR GROUP BY

R-11180-58968-08076-59379-05408-00108-51498-63998 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1) AS t3, t1;
QUERY PLAN
|--SEARCH t2 USING INDEX i4 (c=?)
`--SCAN t1

/* IMP: R-11180-58968 */
# EVIDENCE-OF: R-11180-58968 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1) AS t3, t1; QUERY PLAN |--SEARCH t2 USING
# INDEX i4 (c=?) `--SCAN t1

R-24480-07076-00742-03250-14200-63505-37261-50908 tcl slt th3 src

sqlite> SELECT * FROM
      >   (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x,
      >   (SELECT * FROM t2 WHERE c=1 ORDER BY d LIMIT 2) AS y;
QUERY PLAN
|--MATERIALIZE x
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
|--MATERIALIZE y
|  |--SEARCH t2 USING INDEX i4 (c=?)
|  `--USE TEMP B-TREE FOR ORDER BY
|--SCAN x
`--SCAN y

/* IMP: R-24480-07076 */
# EVIDENCE-OF: R-24480-07076 sqlite> SELECT * FROM > (SELECT *
# FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x, > (SELECT * FROM t2
# WHERE c=1 ORDER BY d LIMIT 2) AS y; QUERY PLAN |--MATERIALIZE x |
# `--SEARCH t1 USING COVERING INDEX i2 (a=?) |--MATERIALIZE y |
# |--SEARCH t2 USING INDEX i4 (c=?) | `--USE TEMP B-TREE FOR ORDER BY
# |--SCAN x `--SCAN y

R-01209-64669-42495-37683-53345-29915-56024-34849 tcl slt th3 src

Each component query of a compound query (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned computed separately and is given its own line in the EXPLAIN QUERY PLAN output.

/* IMP: R-01209-64669 */
# EVIDENCE-OF: R-01209-64669 Each component query of a compound query
# (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned computed
# separately and is given its own line in the EXPLAIN QUERY PLAN output.

R-46219-16052-13457-14850-07693-45237-02962-07960 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  `--SCAN t1 USING COVERING INDEX i1
   `--UNION USING TEMP B-TREE
      `--SCAN t2 USING COVERING INDEX i4

/* IMP: R-46219-16052 */
# EVIDENCE-OF: R-46219-16052 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; QUERY PLAN `--COMPOUND QUERY |--LEFT-MOST
# SUBQUERY | `--SCAN t1 USING COVERING INDEX i1 `--UNION USING TEMP
# B-TREE `--SCAN t2 USING COVERING INDEX i4

R-59034-53199-42361-61766-63157-50392-49995-33404 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
QUERY PLAN
`--MERGE (EXCEPT)
   |--LEFT
   |  `--SCAN t1 USING COVERING INDEX i1
   `--RIGHT
      |--SCAN t2
      `--USE TEMP B-TREE FOR ORDER BY

/* IMP: R-59034-53199 */
# EVIDENCE-OF: R-59034-53199 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; QUERY PLAN `--MERGE (EXCEPT)
# |--LEFT | `--SCAN t1 USING COVERING INDEX i1 `--RIGHT |--SCAN t2
# `--USE TEMP B-TREE FOR ORDER BY