Documentation Source Text

Check-in [d7f83670d7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Enhance the description of the sqlite_stat4 format for WITHOUT ROWID tables.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d7f83670d77b664af5f9a56effadd86e8766d490
User & Date: drh 2014-06-30 17:52:07
Context
2014-07-02
16:24
Fix some typos in queryplanner-ng.in. check-in: 8471968fb0 user: dan tags: trunk
2014-06-30
17:52
Enhance the description of the sqlite_stat4 format for WITHOUT ROWID tables. check-in: d7f83670d7 user: drh tags: trunk
16:26
Fix a couple minor typos in the download page regarding the 64-bit Windows DLL. check-in: c908300540 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/fileformat2.in.

   289    289   reading and writing format 2 was added in SQLite version 3.1.3 
   290    290   on 2005-02-19.</li>
   291    291   <li value=3>Format 3 adds the ability of extra columns added by
   292    292   [ALTER TABLE | ALTER TABLE ... ADD COLUMN] to have non-NULL default
   293    293   values.  This capability was added in SQLite version 3.1.4 
   294    294   on 2005-03-11.</li>
   295    295   <li value=4>^Format 4 causes SQLite to respect the
   296         -[descending indices | DESC keyword] on
   297         -index declarations.  (^The DESC keyword is ignored in indices for 
          296  +[descending indexes | DESC keyword] on
          297  +index declarations.  (^The DESC keyword is ignored in indexes for 
   298    298   formats 1, 2, and 3.)
   299    299   ^Format 4 also adds two new boolean record type values ([serial types]
   300    300   8 and 9.)  Support for format 4 was added in SQLite 3.3.0 on
   301    301   2006-01-10.</li>
   302    302   </ol>
   303    303   
   304    304   <p>^New database files created by SQLite use format 4 by default.
................................................................................
   491    491   b-trees are identified by their root page number.</p>
   492    492   
   493    493   <p>A b-tree page is either a table b-tree page or an index b-tree page.
   494    494   All pages within each complete b-tree are of the same type: either table
   495    495   or index.  There is a one table b-trees in the database file
   496    496   for each rowid table in the database schema, including system tables
   497    497   such as sqlite_master.  There is one index b-trees
   498         -in the database file for each index in the schema, including implied indices
          498  +in the database file for each index in the schema, including implied indexes
   499    499   created by uniqueness constraints.  There are no b-trees associated with
   500    500   [virtual tables].  Specific virtual table implementations might make use
   501    501   of [shadow tables] for storage, but those shadow tables will have separate
   502    502   entries in the database schema.  [WITHOUT ROWID] tables use index b-trees
   503    503   rather than a table b-trees, so there is one
   504    504   index b-tree in the database file for each [WITHOUT ROWID] table.
   505    505   The b-tree corresponding to the sqlite_master table is always a table
................................................................................
  1112   1112   <p>^(The sqlite_master.type column will be one
  1113   1113   of the following text strings:  'table', 'index', 'view', or 'trigger'
  1114   1114   according to the type of object defined.  The 'table' string is used
  1115   1115   for both ordinary and [virtual tables].)^</p>
  1116   1116   
  1117   1117   </p>^(The sqlite_master.name column will hold the name of the object.)^
  1118   1118   ^([UNIQUE] and [PRIMARY KEY] constraints on tables cause SQLite to create
  1119         -[internal indices] with names of the form "sqlite_autoindex_TABLE_N"
         1119  +[internal indexes] with names of the form "sqlite_autoindex_TABLE_N"
  1120   1120   where TABLE is replaced by the name of the table that contains the
  1121   1121   constraint and N is an integer beginning with 1 and increasing by one
  1122   1122   with each constraint seen in the table definition.)^
  1123   1123   ^(In a [WITHOUT ROWID] table, there is no sqlite_master entry for the
  1124   1124   PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside
  1125   1125   for the PRIMARY KEY as if the sqlite_master entry did exist.  This
  1126   1126   will affect the numbering of subsequent UNIQUE constraints.)^
................................................................................
  1132   1132   that the object is associated with.  ^For a table or view, the
  1133   1133   tbl_name column is a copy of the name column.  ^For an index, the tbl_name
  1134   1134   is the name of the table that is indexed.  ^For a trigger, the tbl_name
  1135   1135   column stores the name of the table or view that causes the trigger 
  1136   1136   to fire.</p>
  1137   1137   
  1138   1138   <p>^(The sqlite_master.rootpage column stores the page number of the root
  1139         -b-tree page for tables and indices.)^  ^For rows that define views, triggers,
         1139  +b-tree page for tables and indexes.)^  ^For rows that define views, triggers,
  1140   1140   and virtual tables, the rootpage column is 0 or NULL.</p>
  1141   1141   
  1142   1142   <p>^(The sqlite_master.sql column stores SQL text that describes the
  1143   1143   object.  This SQL text is a [CREATE TABLE], [CREATE VIRTUAL TABLE],
  1144   1144   [CREATE INDEX],
  1145   1145   [CREATE VIEW], or [CREATE TRIGGER] statement that if evaluated against
  1146   1146   the database file when it is the main database of a [database connection]
................................................................................
  1159   1159   <li>^All spaces following the first two keywords are converted into a single
  1160   1160   space.
  1161   1161   </ul>
  1162   1162   
  1163   1163   <p>^(The text in the sqlite_master.sql column is a copy of the original
  1164   1164   CREATE statement text that created the object, except normalized as
  1165   1165   described above and as modified by subsequent [ALTER TABLE] statements.)^
  1166         -^(The sqlite_master.sql is NULL for the [internal indices] that are
         1166  +^(The sqlite_master.sql is NULL for the [internal indexes] that are
  1167   1167   automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p>
  1168   1168   
  1169   1169   
  1170   1170   <tcl>hd_fragment intschema {internal schema objects} \
  1171         -{internal schema object} {internal index} {internal indices} \
         1171  +{internal schema object} {internal index} {internal indexes} \
  1172   1172   {internal table} {internal tables}</tcl>
  1173   1173   <h4>2.6.1 Internal Schema Objects</h4>
  1174   1174   
  1175         -<p>^In addition to the tables, indices, views, and triggers created by
         1175  +<p>^In addition to the tables, indexes, views, and triggers created by
  1176   1176   the application and/or the developer using CREATE statements SQL, the
  1177   1177   sqlite_master table may contain zero or more entries for 
  1178   1178   <i>internal schema objects</i> that are created by SQLite for its 
  1179   1179   own internal use.  ^The names of internal schema objects
  1180   1180   always begin with "sqlite_" and any table, index, view, or trigger
  1181   1181   whose name begins with "sqlite_" is an internal schema object.
  1182   1182   ^SQLite prohibits applications from creating objects whose names begin
................................................................................
  1243   1243   ^Application code can delete all entries from the sqlite_sequence table,
  1244   1244   but application code cannot drop the sqlite_sequence table.
  1245   1245   
  1246   1246   <tcl>hd_fragment stat1tab {sqlite_stat1} SQLITE_STAT1 </tcl>
  1247   1247   <h4>2.6.3 The sqlite_stat1 table</h4>
  1248   1248   
  1249   1249   <p>^The sqlite_stat1 is an internal table created by the [ANALYZE] command
  1250         -and used to hold supplemental information about tables and indices that the
         1250  +and used to hold supplemental information about tables and indexes that the
  1251   1251   query planner can use to help it find better ways of performing queries.
  1252   1252   ^Applications can update, delete from, insert into or drop the sqlite_stat1
  1253   1253   table, but may not create or alter the sqlite_stat1 table.
  1254   1254   ^The schema of the sqlite_stat1 table is as follows:
  1255   1255   
  1256   1256   <blockquote><pre>
  1257   1257   CREATE TABLE sqlite_stat1(tbl,idx,stat);
................................................................................
  1282   1282   <p>^The list of integers in the stat column can also optionally be followed
  1283   1283   by a token of the form "sz=NNN" where NNN is an unsigned integer.  The
  1284   1284   "sz=NNN" token, if present, must be separated from the last integer by a
  1285   1285   single space.  ^The "sz=NNN" token means that the average row size over all 
  1286   1286   records of the table or
  1287   1287   index is NNN bytes per row.  ^The SQLite query planner might use the
  1288   1288   estimated row size information provided by the "sz=NNN" token
  1289         -to help it choose smaller tables and indices that require less disk I/O.
         1289  +to help it choose smaller tables and indexes that require less disk I/O.
  1290   1290   
  1291   1291   <p>If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
  1292   1292   column contains a single integer which is the approximate number of
  1293   1293   rows in the table identified by sqlite_stat1.tbl.
  1294   1294   
  1295   1295   <tcl>hd_fragment stat2tab {sqlite_stat2}</tcl>
  1296   1296   <h4>2.6.4 The sqlite_stat2 table</h4>
................................................................................
  1371   1371   <h4>2.6.6 The sqlite_stat4 table</h4>
  1372   1372   
  1373   1373   <p>The sqlite_stat4 is only created and is only used if SQLite is compiled
  1374   1374   with [SQLITE_ENABLE_STAT4] and if the SQLite version number is
  1375   1375   3.8.1 or greater.  The sqlite_stat4 table is neither read nor written by any
  1376   1376   version of SQLite before 3.8.1.
  1377   1377   The sqlite_stat4 table contains additional information
  1378         -about the distribution of keys within an index, information that the
         1378  +about the distribution of keys within an index or the distribution of
         1379  +keys in the primary key of a [WITHOUT ROWID] table, information that the
  1379   1380   query planner can use to devise better and faster query algorithms.
  1380   1381   The schema of the sqlite_stat4 table is as follows:
  1381   1382   
  1382   1383   <blockquote><pre>
  1383   1384   CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);
  1384   1385   </pre></blockquote>
  1385   1386   
  1386         -<p>There are usually multiple entries in the sqlite_stat4 table for each index.
  1387         -The sqlite_stat4.sample column holds the content of an index entry in
  1388         -the [record format].  The index entry stored is taken from the
  1389         -index identified by sqlite_stat4.idx and sqlite_stat4.tbl.
  1390         -The sqlite_stat4.nEq column holds a list of integers where the K-th integer
  1391         -is the approximate number of entries in the index whose left-most K columns
  1392         -exactly match the K left-most columns of the sample.
  1393         -The sqlite_stat4.nLt holds a list of integers where the K-th integer is
  1394         -the approximate number of entries in the
  1395         -index whose K left-most columns are collectively less than the 
  1396         -K left-most columns of the sample.
  1397         -The sqlite_stat4.nDLt column holds a list of integers where the K-th 
  1398         -integers is the approximate
  1399         -number of entries in the index that are distinct in the first K columns and
  1400         -that are whose left-most K columns are collectively less than the left-most
  1401         -K columns of the sample.
         1387  +<p>There are typically between 10 to 40 entries in the sqlite_stat4 table for
         1388  +each index for which statistics are available, however size limits are
         1389  +not hard bounds.
         1390  +The meanings of the columns in the sqlite_stat4 table are as follows:
         1391  +
         1392  +<center>
         1393  +<table border="0" width="100%" cellpadding="10">
         1394  +<tr><td valign="top" align="right">tbl:</td>
         1395  +    <td>The sqlite_stat4.tbl column holds name of the table that owns
         1396  +    the index that the row describes
         1397  +
         1398  +<tr><td valign="top" align="right">idx:</td>
         1399  +    <td>The sqlite_stat4.idx column holds name of the index that the
         1400  +    row describes, or in the case of
         1401  +    an sqlite_stat4 entry for a [WITHOUT ROWID] table, the
         1402  +    name of the table itself.
         1403  +
         1404  +<tr><td valign="top" align="right">sample:</td>
         1405  +    <td>The sqlite_stat4.sample column holds a BLOB
         1406  +    in the [record format] that encodes the indexed columns followed by
         1407  +    the rowid for a rowid table or by the columns of the primary key 
         1408  +    for a WITHOUT ROWID table.
         1409  +    The sqlite_stat4.sample BLOB for the WITHOUT ROWID table itself 
         1410  +    contains just the columns of the primary key.
         1411  +    Let the number of columns encoded by the sqlite_stat4.sample blob be N.
         1412  +    For indexes on an ordinary rowid table, N will be one more than the number 
         1413  +    of columns indexed.
         1414  +    For indexes on WITHOUT ROWID tables, N will be the number of columns
         1415  +    indexed plus the number of columns in the primary key.
         1416  +    For a WITHOUT ROWID table, N will be the number of columns in the
         1417  +    primary key.
         1418  +
         1419  +<tr><td valign="top" align="right">nEq:</td>
         1420  +    <td>The sqlite_stat4.nEq column holds a list of N integers where 
         1421  +    the K-th integer is the approximate number of entries in the index
         1422  +    whose left-most K columns exactly match the K left-most columns
         1423  +    of the sample.
         1424  +
         1425  +<tr><td valign="top" align="right">nLt:</td>
         1426  +    <td>The sqlite_stat4.nLt column holds a list of N integers where
         1427  +    the K-th integer is the approximate number of entries in the
         1428  +    index whose K left-most columns are collectively less than the 
         1429  +    K left-most columns of the sample.
         1430  +
         1431  +<tr><td valign="top" align="right">nDLt:</td>
         1432  +    <td>The sqlite_stat4.nDLt column holds a list of N integers where the K-th 
         1433  +    integers is the approximate
         1434  +    number of entries in the index that are distinct in the first K columns and
         1435  +    that are whose left-most K columns are collectively less than the left-most
         1436  +    K columns of the sample.
         1437  +</table>
         1438  +</center>
  1402   1439   
  1403   1440   <p>The sqlite_stat4 is a generalization of the sqlite_stat3 table.  The
  1404   1441   sqlite_stat3 table provides information about the left-most column of an
  1405   1442   index whereas the sqlite_stat4 table provides information about all columns
  1406   1443   of the index.
  1407   1444   
  1408   1445   <p>There can be an arbitrary number of sqlite_stat4 entries per index.