Documentation Source Text

<li>Performance optimizations for [ORDER BY], [VACUUM], [CREATE INDEX],
    [PRAGMA integrity_check], and [PRAGMA quick_check].
<li>Fix many obscure problems discovered while [SQL fuzzing].
<li>Identify all methods for important objects in the interface documentation.
    ([sqlite3_context|example])
<li>Made the [American Fuzzy Lop fuzzer]
    a standard part of SQLite's [testing|testing strategy].
<li>Add the ".binary" and ".limits" commands to the [command-line shell].
<li>Make the "dbstat" virtual table part of standard builds when
    compiled with the [SQLITE_ENABLE_DBSTAT_VTAB] option.
}

chng {2015-04-08 (3.8.9)} {
<li>Add VxWorks-7 as an officially supported and tested platform.
<li>Added the [sqlite3_status64()] interface.
<li>Fix memory size tracking so that it works even if SQLite uses more
    than 2GiB of memory.

  <li> [sqlite3_column_database_name16()] </li>
  <li> [sqlite3_column_table_name()] </li>
  <li> [sqlite3_column_table_name16()] </li>
  <li> [sqlite3_column_origin_name()] </li>
  <li> [sqlite3_column_origin_name16()] </li>
  </ul>
}

COMPILE_OPTION {SQLITE_ENABLE_DBSTAT_VTAB} {
  This option enables the [dbstat virtual table].
}

COMPILE_OPTION {SQLITE_ENABLE_EXPLAIN_COMMENTS} {
  This option adds extra logic to SQLite that inserts comment text into the
  output of [EXPLAIN].  These extra comments use extra memory, thus
  making [prepared statements] larger and very slightly slower, and so they are
  turned off by default and in most application.  But some applications, such
  as the [command-line shell] for SQLite, value clarity of EXPLAIN output

<title>The DBSTAT Virtual Table</title>
<tcl>hd_keywords dbstat {dbstat virtual table}</tcl>
<h2>The DBSTAT Virtual Table</h2>

<p>
The <b>dbstat</b> virtual table is available on all 
[database connections] when SQLite is build using the
[SQLITE_ENABLE_DBSTAT_VTAB] compile-time option.
The dbstat virtual table provides low-level information 
about btree and overflow pages in the main database file.
The dbstat virtual table itself can exist in any database
(TEMP is recommended) but it always provides information
about only the main database.

<p>
The recommanded way to create an instance of the dbstat
virtual table is as follows:

<blockquote><pre>
CREATE VIRTUAL TABLE temp.stat USING dbstat;
</pre></blockquote>

<p>
Note the "temp." qualifier before the virtual table name ("stat").  This
qualifier causes the virtual table to be temporary - to only exist for
the duration of the current database connection.  This is the
recommended approach.

<p>
The schema for the dbstat virtual table is this:
<blockquote><pre>
CREATE TABLE dbstat(
  name       STRING,     -- Name of table or index
  path       INTEGER,    -- Path to page from root
  pageno     INTEGER,    -- Page number
  pagetype   STRING,     -- 'internal', 'leaf' or 'overflow'
  ncell      INTEGER,    -- Cells on page (0 for overflow)
  payload    INTEGER,    -- Bytes of payload on this page
  unused     INTEGER,    -- Bytes of unused space on this page
  mx_payload INTEGER,    -- Largest payload size of all cells on this page
  pgoffset   INTEGER,    -- Offset of page in file
  pgsize     INTEGER     -- Size of the page
);
</pre></blockquote>

<p>
There is a single row of the dbstat table for each page in the
main database file.  Freelist pages, the lock page, and
pointer-map pages of the database file do not appear in the
dbstat virtual table.

<h3>The "path" column of the dbstat virtual table</h3>

<p>
The "path" column describes the path taken from the 
root node of the btree structure to each page.  The
"path" of the root node itself is '/'.

The "path" for the left-most child page of the root of
a btree page is '/000/'. (Btrees store content ordered from left to right
so the pages to the left have smaller keys than the pages to the right.)
The next to left-most child of the root page is '/001', and so on,
each sibling page identified by a 3-digit hex  value.
The children of the 451st left-most sibling have paths such
as '/1c2/000/, '/1c2/001/' etc.

Overflow pages are specified by appending a '+' character and a 
six-digit hexadecimal value to the path to the cell they are linked
from. For example, the three overflow pages in a chain linked from 
the left-most cell of the 450th child of the root page are identified
by the paths:

<blockquote><pre>
'/1c2/000+000000'         // First page in overflow chain
'/1c2/000+000001'         // Second page in overflow chain
'/1c2/000+000002'         // Third page in overflow chain
</pre></blockquote>

If the paths are sorted using the BINARY collation sequence, then
the overflow pages associated with a cell will appear earlier in the
sort-order than its child page:

<blockquote><pre>
'/1c2/000/'               // Left-most child of 451st child of root
</pre></blockquote>

<h3>Example uses of the dbstat virtual table</h3>

<p>
To find the total number of pages used to store table "xyz", use:

<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT count(*) FROM stat WHERE name='xyz';
</pre></blockquote>

<p>
To see how efficiently the content of a table is stored on disk,
compute the amount of space used to hold actual content divided
by the total amount of disk space used.  The closer this number
is to 100%, the more efficient the packing.  

<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT sum(pgsize-unused)/sum(pgsize) FROM stat WHERE name='xyz';
</pre></blockquote>

<p>
To find the average fan-out for a table, run:

<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT avg(ncell) FROM stat WHERE name='xyz' AND pagetype='internal';
</pre></blockquote>


<p>
Modern filesystems operate faster when disk accesses are sequential.
Hence, SQLite will run faster if the content of the database file
is on sequential pages.  To find out what fraction of the pages in
a database are sequential (and thus obtain a measurement that might
be useful in determining when to [VACUUM]), run a query like the following:

<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
INSERT INTO s(pageno) SELECT pageno FROM stat ORDER BY path;
SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*)
  FROM s AS s1, s AS s2
 WHERE s1.rowid+1=s2.rowid;
DROP TABLE s;
</pre></blockquote>

<title>SQLite Home Page</title>

<table border="0" width="100%">
<tr><td valign="top">
<h3>Welcome</h3>
<p>SQLite is a software library that implements a
<a href="selfcontained.html">self-contained</a>, 
<a href="serverless.html">serverless</a>,
<a href="zeroconf.html">zero-configuration</a>,
<a href="transactional.html">transactional</a>
SQL database engine.
SQLite is the [most widely deployed]