Documentation Source Text

set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2013-10-17 (3.8.1)} {
<li>Added the [unlikely()] and [likelihood()] SQL functions to be used
    as hints to the query planner.
<li>Enhancements to the query planner:
<ul>
<li>Take into account the fact WHERE clause terms that cannot be used with indices
    still probably reduce the number of output rows.
<li>Estimate the sizes of table and index rows and use the smallest applicable B-Tree
    for full scans and "count(*)" operations.
</ul>
<li>Added the [soft_heap_limit pragma].
<li>Added support for [SQLITE_ENABLE_STAT4]
<li>Added support for "sz=NNN" parameters at the end of 
    [sqlite_stat1 | sqlite_stat1.stat] fields
    used to specify the average length in bytes for table and index rows.
<li>Avoid running foreign-key constraint checks on an UPDATE if none of the
    modified columns are associated with foreign keys.
<li>Added the [SQLITE_MINIMUM_FILE_DESCRIPTOR] compile-time option
<li>Added the win32-longpath VFS on windows, permitting filenames up to 32K
    characters in length.
<li>The [Date And Time Functions] are enhanced so that the current time

<li>The sqlite3_analyzer utility program is updated to provide better descriptions
    and to compute a more accurate estimate for "Non-sequential pages"
<li>Refactor the implementation of PRAGMA statements to improve parsing performance.
<li>The directory used to hold temporary files on unix can now be set using
    the SQLITE_TMPDIR environment variable,  which takes precedence over the
    TMPDIR environment variable.  The [sqlite3_temp_directory] global variable
    still has higher precedence than both environment variables, however.
<li>Added the [PRAGMA stats] statement.
<li><b>Bug fix:</b> Return the correct answer for "SELECT count(*) FROM table" even if
    there is a [partial index] on the table. Ticket
    [http://www.sqlite.org/src/info/a5c8ed66ca|a5c8ed66ca].
}

chng {2013-09-03 (3.8.0.2)} {
<li>Fix a bug in the optimization that attempts to omit unused LEFT JOINs

COMPILE_OPTION {SQLITE_ENABLE_STAT3} {
  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from the left-most column of each index and store that data
  in the [sqlite_stat3] table.  The query planner will then use the
  histogram data to help it make better index choices.  Note, however,
  that the use of histogram data in query planner violates the
  [query planner stability guarantee] which is important to some applications.
  <p>
}

COMPILE_OPTION {SQLITE_ENABLE_STAT4} {
  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from all columns of every index and store that data
  in the [sqlite_stat4] table.  The query planner will then use the
  histogram data to help it make better index choices.  The downside of
  this compile-time option is that it violates the
  [query planner stability guarantee] making it more difficult to ensure
  consistent performance is mass-produced applications.
  <p>
  SQLITE_ENABLE_STAT4 is an enhancement of [SQLITE_ENABLE_STAT3].  STAT3
  only recorded histogram data for the left-most column of each index
  whereas the STAT4 enhancement records histograph data from all columns
  of each index.
  The [SQLITE_ENABLE_STAT3] compile-time option is a no-op and is ignored
  if the SQLITE_ENABLE_STAT4 compile-time option is used.

integer is the approximate average number of rows in the index that have 
the same value for the first two columns.  The N-th integer (for N>1) 
is the approximate average number of rows in 
the index which have the same value for the first N-1 columns.  For
a K-column index, there will be K+1 integers in the stat column.  If
the index is unique, then the last integer will be 1.

<p>^The list of integers in the stat column can optionally be followed
by the keyword "unordered".  ^The "unordered" keyword, if it is present,
must be separated from the last integer by a single space.  ^If the
"unordered" keyword is present, then the query planner assumes that
the index is unordered and will not use the index for a range query
or for sorting.

<p>^The list of integers in the stat column can also optionally be followed
by a token of the form "sz=NNN" where NNN is an unsigned integer.  The
"sz=NNN" token, if present, must be separated from the last integer by a
single space.  ^The "sz=NNN" token means that the average row size over all 
records of the table or
index is NNN bytes per row.  ^The SQLite query planner might use the
estimated row size information provided by the "sz=NNN" token
to help it choose smaller tables and indices that require less disk I/O.

<p>If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
column contains a single integer which is the approximate number of
rows in the table identified by sqlite_stat1.tbl.

<tcl>hd_fragment stat2tab {sqlite_stat2}</tcl>
<h4>2.5.4 The sqlite_stat2 table</h4>

  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2013-10-17} {Release 3.8.1} {
  <p>SQLite [version 3.8.1] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional, though you should upgrade
  if you are using [partial indices] as there was a 
  [http://www.sqlite.org/src/info/a5c8ed66ca|bug] related to partial
  indices in the previous release that could result in an incorrect answer
  for count(*) queries.

  <p>The [next generation query planner] that was premiered in the previous
  release continues to work well.  
  The new query planner has been tweaked slightly
  in the current release to help it make better decisions in some
  cases, but is largely unchanged.  Two new SQL functions, [likelihood()] and
  [unlikely()], have been added to allow developers to give hints to the
  query planner without forcing the query planner into a particular decision.

  <p>Version 3.8.1 is the first SQLite release to take into account the
  estimated size of table and index rows when choosing a query plan.
  Row size estimates are based on the declared datatypes of columns.
  For example, a column of type VARCHAR(1000) is assumed
  to use much more space than a column of type INT.  The datatype-based
  row size estimate can be
  overridden by appending a term of the form "sz=NNN" (where NNN is the
  average row size in bytes) to the end of the [sqlite_stat1|sqlite_stat1.stat]
  record for a table or index.  Currently, row sizes are only used to help the
  query planner choose between a table or one of its indices when doing a
  table scan or a count(*) operation, though future releases are likely
  to use the estimated row size in other contexts as well.  The new
  [PRAGMA stats] statement can be used to view row size estimates.

  <p>Version 3.8.1 adds the [SQLITE_ENABLE_STAT4] compile-time option.
  STAT4 is very similar to STAT3 in that it uses samples from indices to 
  try to guess how many rows of the index will be satisfy by WHERE clause
  constraints.  The difference is that STAT4 samples all columns of the
  index whereas the older STAT3 only sampled the left-most column.  Users
  of STAT3 are encouraged to upgrade to STAT4.  Application developers should
  use STAT3 and STAT4 with caution since both options, by design, violate 
  the [query planner stability guarantee], making it more difficult to ensure
  uniform performance is widely-deployed and mass-produced embedded
  applications.
}

newsitem {2013-09-03} {Release 3.8.0.2} {
  <p>SQLite [version 3.8.0.2] contains a one-line fix to a bug in the
  new optimization that tries to omit unused LEFT JOINs from a query.
}

newsitem {2013-08-29} {Release 3.8.0.1} {
  <p>SQLite [version 3.8.0.1] fixes some obscure bugs that were uncovered by
  users in the 3.8.0 release.  Changes from 3.8.0 are minimal.

}

Pragma stats {
    <p>^(<b>PRAGMA stats;</b>)^ </p>
    <p>This pragma returns auxiliary information about tables and
    indices.  The returned information is used during testing to help
    verify that the query planner is operating correctly.  The format
    and meaning of this pragma will likely change from release
    to the next. Because of its volatility, the behavior and output
    format of this pragma are deliberately undocumented.  This pragma is
    intended for interactive, debugging, and testing use only.
    Applications should avoid using this pragma.</p>
}

Pragma page_count {
    <p>^(<b>PRAGMA page_count;</b></p>
    <p>Return the total number of pages in the database file.</p>)^
}