Documentation Source Text

  [AUTOINCREMENT] functionality. When this 
  is macro is defined, columns declared as 
  "[INTEGER PRIMARY KEY] AUTOINCREMENT"
  behave in the same way as columns declared as "[INTEGER PRIMARY KEY]" when a 
  NULL is inserted. The sqlite_sequence system table is neither created, nor
  respected if it already exists.
}

COMPILE_OPTION {SQLITE_OMIT_AUTOMATIC_INDEX} {
  This option is used to omit the 
  [automatic indexing] functionality.
}

COMPILE_OPTION {SQLITE_OMIT_AUTOINIT} {
  For backwards compatibility with older versions of SQLite that lack
  the [sqlite3_initialize()] interface, the [sqlite3_initialize()] interface
  is called automatically upon entry to certain key interfaces such as
  [sqlite3_open()], [sqlite3_vfs_register()], and [sqlite3_mprintf()].
  The overhead of invoking [sqlite3_initialize()] automatically in this

proc PARAGRAPH {text} {
  # regsub -all "/(\[a-zA-Z0-9\]+)/" $text {<i>\1</i>} t2
  regsub -all "\\*(\[^\n*\]+)\\*" $text {<tt><b><big>\1</big></b></tt>} t3
  hd_resolve "<p>$t3</p>\n"
}
set level(0) 0
set level(1) 0
proc HEADING {n name args} {
  if {[llength $args]>0} {
    eval hd_fragment $args
  }
  global level
  incr level($n)
  for {set i [expr {$n+1}]} {$i<10} {incr i} {
    set level($i) 0
  }
  if {$n==0} {
    set num {}

}

HEADING 1 {Choosing between multiple indices} multi_index

PARAGRAPH {
  Each table in the FROM clause of a query can use at most one index
  (except when the <a href="#or_opt">OR-clause optimization</a> comes into
  play)
  and SQLite strives to use at least one index on each table.  Sometimes,
  two or more indices might be candidates for use on a single table.
  For example:
}
CODE {
  CREATE TABLE ex2(x,y,z);
  CREATE INDEX ex2i1 ON ex2(x);

PARAGRAPH {)^
  ^In order for these optimizations to occur, they must appear in exactly
  the form shown above - changing only the name of the table and column.
  ^It is not permissible to add a WHERE clause or do any arithmetic on the
  result.  ^The result set must contain a single column.
  ^The column in the MIN or MAX function must be an indexed column.
}

HEADING 1 {Automatic Indices} autoindex {automatic indexing} {Automatic indexing}

PARAGRAPH {
  ^(When no indices are available to aid the evaulation of a query, SQLite
  will consider creating a automatic index that lasts only for the duration
  of a single SQL statement and use that index to help boost the query
  performance.)^  Since the cost of constructing the automatic index is
  O(NlogN) (where N is the number of entries in the table) and the cost of
  doing a full table scan is only O(N), an automatic index will
  only be created if SQLite expects that the lookup will be run more than
  logN times during the course of the SQL statement. Consider an example:
}
CODE {
  CREATE TABLE t1(a,b);
  CREATE TABLE t2(c,d);
  -- Insert many rows into both t1 and t2
  SELECT * FROM t1, t2 WHERE a=c;
}
PARAGRAPH {
  In the query above, if both t1 and t2 have approximately N rows, then
  without any indices the query will require O(N*N) time.  On the other
  hand, creating an index on table t2 requires O(NlogN) time and then using 
  that index to evaluate the query requires an additional O(NlogN) time.
  In the absence of [ANALYZE] information, SQLite guesses that N is one
  million and hence it believes that constructing the automatic index will
  be the cheaper approach.
}
PARAGRAPH {
  An automatic index might also be used for a subquery:
}
CODE {
  CREATE TABLE t1(a,b);
  CREATE TABLE t2(c,d);
  -- Insert many rows into both t1 and t2
  SELECT a, (SELECT d FROM t2 WHERE c=b) FROM t1;
}
PARAGRAPH {
  In this example, the t2 table is used in a subquery to translate values
  of the t1.b column.  If each table contains N rows, SQLite expects that
  the subquery will run N times, and hence it will believe it is faster
  to construct an automatic, transient index on t2 first and then using
  that index to satisfy the N instances of the subquery.
}
PARAGRAPH {
  The automatic indexing capability can be disabled at run-time using
  the [automatic_index pragma] and can be omitted from the build at
  compile-time using the [SQLITE_OMIT_AUTOMATIC_INDEX] compile-time option.
}
</tcl>

database name is omitted, "main" is assumed.  ^In some pragmas, the database
name is meaningless and is simply ignored.</p>

<tcl>Section {Pragmas to modify library operation} modify</tcl>
</tcl>

<ul>
<tcl>Subsection automatic_index</tcl>
<li><p>^(<b>PRAGMA automatic_index;
       <br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>
    <p>Query, set, or clear the [automatic indexing] capability.)^

    <p>^[Automatic indexing] is enabled by default.
     ^This pragma only influences the query plan as statements are
     prepared or reprepared.  Existing prepared statements must be
     reprepared for a change in the automatic_index setting to affect
     their operation.
</li>

<tcl>Subsection auto_vacuum</tcl>
<li><p><b>PRAGMA auto_vacuum;<br>
          PRAGMA auto_vacuum = </b>
           <i>0 | NONE | 1 | FULL | 2 | INCREMENTAL</i><b>;</b></p>

    <p>Query or set the auto-vacuum status in the database.</p>