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: |
d7f83670d77b664af5f9a56effadd86e |
| 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
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.