Documentation Source Text

     valid arguments to DEFAULT.
<li> Added the sorter-reference optimization as a compile-time option.
     Only available if compiled with SQLITE_ENABLE_SORTER_REFERENCES.
<li> Improve the format of the [EXPLAIN QUERY PLAN] raw output, so that
     it gives better information about the query plan and about the
     relationships between the various components of the plan.
<li> The [CLI] automatically intercepts the raw [EXPLAIN QUERY PLAN] 
     output an reformats it into an ASCII-art graph.
     output and reformats it into an ASCII-art graph.
<li> Enhance the query planner to allow the [OR optimization] to proceed
     even if the OR expression has also been converted into an IN
     expression.  Uses of the OR optimization are now also 
     [eqp-or-opt|more clearly shown] in the [EXPLAIN QUERY PLAN] output.
<p><b>Performance:</b>
<li> [UPDATE] avoids writing database pages that do not actually change.
     For example, "UPDATE t1 SET x=25 WHERE y=?" becomes a no-op if the

<p> In automatic EXPLAIN QUERY PLAN mode, the shell automatically runs
a separate EXPLAIN QUERY PLAN query for each statement you enter and
displays the result before actually running the query.  Use the
".eqp off" command to turn automatic EXPLAIN QUERY PLAN mode back off.

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

<h2>Table and Index Scans</h2>

<p>
  When processing a SELECT (or other) statement, SQLite may retrieve data from
  database tables in a variety of ways. It may scan through all the records in

</codeblock>)^

<p>
  ^The order of the entries indicates the nesting order. In
  this case, the scan of table t1 using index i2 is the outer loop (since it
  appears first)
  and the full-table scan of table t2 is the inner loop (since it appears
  last).  In the
  last).
  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.
  The output from EXPLAIN QUERY PLAN shows how the query is acctually
  The output from EXPLAIN QUERY PLAN shows how the query is actually
  evaluated, not how it is specified in the SQL statement.
^(<codeblock>
    sqlite&gt; EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
    QUERY PLAN
    |--SEARCH TABLE t1 USING INDEX i2 (a=? AND b>?)
    `--SCAN TABLE t2
</codeblock>)^