Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the WITHOUT ROWID document. Enhance the file-format, CREATE TABLE, and AUTOINCREMENT documents to reflect changes due to WITHOUT ROWID. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
ffe4c464eaea14381a1f066673f2e400 |
User & Date: | drh 2013-11-09 17:14:28.989 |
Context
2013-11-09
| ||
17:17 | Fix a broken fragment specification in the fileformat page. (check-in: ea35f76f95 user: drh tags: trunk) | |
17:14 | Add the WITHOUT ROWID document. Enhance the file-format, CREATE TABLE, and AUTOINCREMENT documents to reflect changes due to WITHOUT ROWID. (check-in: ffe4c464ea user: drh tags: trunk) | |
2013-11-08
| ||
20:06 | First attempt at documenting WITHOUT ROWID. (check-in: 3819df0c0d user: drh tags: trunk) | |
Changes
Changes to pages/autoinc.in.
1 2 3 4 5 | <title>SQLite Autoincrement</title> <h1>SQLite Autoincrement</h1> <tcl>hd_keywords AUTOINCREMENT</tcl> <p> | | | > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <title>SQLite Autoincrement</title> <h1>SQLite Autoincrement</h1> <tcl>hd_keywords AUTOINCREMENT</tcl> <p> ^In SQLite, table rows normally have a 64-bit signed integer [ROWID] which is unique among all rows in the same table. ([WITHOUT ROWID] tables are the exception.) </p> <p> ^(You can access the ROWID of an SQLite table using one the special column names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not |
︙ | ︙ | |||
102 103 104 105 106 107 108 | <p>^Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. ^One is the usual increment. ^However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. ^AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.</p> | > > > > > | 103 104 105 106 107 108 109 110 111 112 113 114 | <p>^Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. ^One is the usual increment. ^However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. ^AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.</p> <p>Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on [WITHOUT ROWID] tables. Any attempt to use AUTOINCREMENT on a [WITHOUT ROWID] table results in an error.</p> |
Changes to pages/fileformat2.in.
︙ | ︙ | |||
347 348 349 350 351 352 353 | <h4>1.2.15 Application ID</h4> <p>^The 4-byte big-endian integer at offset 68 is an "Application ID" that can be set by the [PRAGMA application_id] command in order to identify the database as belonging to or associated with a particular application. The application ID is intended for database files used as an [application file-format]. The application ID can be used by utilities | | | 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 | <h4>1.2.15 Application ID</h4> <p>^The 4-byte big-endian integer at offset 68 is an "Application ID" that can be set by the [PRAGMA application_id] command in order to identify the database as belonging to or associated with a particular application. The application ID is intended for database files used as an [application file-format]. The application ID can be used by utilities such as [http://www.darwinsys.com/file/ | file(1)] to determine the specific file type rather than just reporting "SQLite3 Database". A list of assigned application IDs can be seen by consulting the [http://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt|magic.txt] file in the SQLite source repository.</p> <tcl>hd_fragment validfor {version-valid-for number}</tcl> <h4>1.2.16 Write library version number and version-valid-for number</h4> |
︙ | ︙ | |||
429 430 431 432 433 434 435 436 437 438 439 | <p>^The number of freelist pages is stored as a 4-byte big-endian integer in the database header at an offset of 36 from the beginning of the file. ^The database header also stores the page number of the first freelist trunk page as a 4-byte big-endian integer at an offset of 32 from the beginning of the file.</p> <h3>1.5 B-tree Pages</h3> <p>A b-tree page is either an interior page or a leaf page. A leaf page contains keys and in the case of a table b-tree each | > > > > > > > > > > > > > > | | < | | > | | | | | > > > > > | > | > | | | 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 | <p>^The number of freelist pages is stored as a 4-byte big-endian integer in the database header at an offset of 36 from the beginning of the file. ^The database header also stores the page number of the first freelist trunk page as a 4-byte big-endian integer at an offset of 32 from the beginning of the file.</p> <tcl>hd_fragment btree {B-Trees} {B*-Trees}</tcl> <h3>1.5 B-tree Pages</h3> <p>The b-tree algorithm provides key/data storage with unique and ordered keys on page-oriented storage devices. For background information on b-trees, see Knuth, <u>The Art Of Computer Programming</u>, Volume 3 "Sorting and Searching", pages 471-479. Two kinds of b-trees are used by SQLite. The algorithm that Knuth calls "B*-Tree" stores all data in the leaves of the tree. SQLite calls this variety of b-tree a "table b-tree". The algorithm that Knuth calls simply "B-Tree" stores both the key and the data together in both leaves and in interior pages. In the SQLite implementation, the original B-Tree algorithm stores keys only, omitting the data entirely, and is called an "index b-tree". <p>A b-tree page is either an interior page or a leaf page. A leaf page contains keys and in the case of a table b-tree each key has associated data. An interior page contains K keys together with K+1 pointers to child b-tree pages. A "pointer" in an interior b-tree page is just the 31-bit integer page number of the child page.</p> <p>Define the depth of a leaf b-tree to be 1 and the depth of any interior b-tree to be one more than the maximum depth of any of its children. ^In a well-formed database, all children of an interior b-tree have the same depth.</p> <p>In an interior b-tree page, the pointers and keys logically alternate with a pointer on both ends. (The previous sentence is to be understood conceptually - the actual layout of the keys and pointers within the page is more complicated and will be described in the sequel.) All keys within the same page are unique and are logically organized in ascending order from left to right. (Again, this ordering is logical, not physical. The actual location of keys within the page is arbitrary.) ^For any key X, pointers to the left of a X refer to b-tree pages on which all keys are less than or equal to X. ^Pointers to the right of X refer to pages where all keys are greater than X.</p> <p>Within an interior b-tree page, each key and the pointer to its immediate left are combined into a structure called a "cell". The right-most pointer is held separately. A leaf b-tree page has no pointers, but it still uses the cell structure to hold keys for index b-trees or keys and content for table b-trees. Data is also contained in the cell. </p> <p>Every b-tree page has at most one parent b-tree page. A b-tree page without a parent is called a root page. A root b-tree page together with the closure of its children form a complete b-tree. It is possible (and in fact rather common) to have a complete b-tree that consists of a single page that is both a leaf and the root. Because there are pointers from parents to children, every page of a complete b-tree can be located if only the root page is known. Hence, b-trees are identified by their root page number.</p> <p>A b-tree page is either a table b-tree page or an index b-tree page. All pages within each complete b-tree are of the same type: either table or index. There is a one table b-trees in the database file for each rowid table in the database schema, including system tables such as sqlite_master. There is one index b-trees in the database file for each index in the schema, including implied indices created by uniqueness constraints. There are no b-trees associated with [virtual tables]. Specific virtual table implementations might make use of [shadow tables] for storage, but those shadow tables will have separate entries in the database schema. [WITHOUT ROWID] tables use an index b-tree as their princpal storage rather than a table b-tree, so there is one index b-tree in the database file for each [WITHOUT ROWID] table. The b-tree corresponding to the sqlite_master table is always a table b-tree and always has a root page of 1. The sqlite_master table contains the root page number for every other table and index in the database file.</p> <p>Each entry in a table b-tree consists of a 64-bit signed integer key and up to 2147483647 bytes of arbitrary data. (The key of a table b-tree corresponds to the [rowid] of the SQL table that the b-tree implements.) Interior table b-trees hold only keys and pointers to children. All data is contained in the table b-tree leaves.</p> <p>Each entry in an index b-tree consists of an arbitrary key of up to 2147483647 bytes in length and no data.</p> <tcl>hd_fragment cell_payload {cell payload}</tcl> <p>Define the "payload" of a cell to be the arbitrary length section of the cell. For an index b-tree, the key is always arbitrary in length |
︙ | ︙ | |||
624 625 626 627 628 629 630 | order of appearance, for the various b-tree page types.</p> <blockquote><dl> <dt><p>Table B-Tree Leaf Cell:</p></dt> <dd><p><ul> <li>A varint which is the total number of bytes of payload, including any overflow | | | 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 | order of appearance, for the various b-tree page types.</p> <blockquote><dl> <dt><p>Table B-Tree Leaf Cell:</p></dt> <dd><p><ul> <li>A varint which is the total number of bytes of payload, including any overflow <li>A varint which is the integer key, a.k.a. "[rowid]" <li>The initial portion of the payload that does not spill to overflow pages. <li>A 4-byte big-endian integer page number for the first page of the overflow page list - omitted if all payload fits on the b-tree page. </ul></p></dd> <dt><p>Table B-Tree Interior Cell:</p></dt> |
︙ | ︙ | |||
837 838 839 840 841 842 843 | accessing a large data set. This section will describe how the low-level b-tree layer is used to implement higher-level SQL capabilities.</p> <tcl>hd_fragment record_format {record format}</tcl> <h3>2.1 Record Format</h3> | | | > | | | 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 | accessing a large data set. This section will describe how the low-level b-tree layer is used to implement higher-level SQL capabilities.</p> <tcl>hd_fragment record_format {record format}</tcl> <h3>2.1 Record Format</h3> <p>The data for a table b-tree leaf page and the key of an index b-tree page was characterized above as an arbitrary sequence of bytes. The prior discussion mentioned one key being less than another, but did not define what "less than" meant. The current section will address these omissions.</p> <p>Payload, either table b-tree data or index b-tree keys, is always in the "record format". The record format defines a sequence of values corresponding to columns in a table or index. The record format specifies the number of columns, the datatype of each column, and the content of each column.</p> <p>The record format makes extensive use of the [variable-length integer] or [varint] representation of 64-bit signed integers defined above.</p> |
︙ | ︙ | |||
960 961 962 963 964 965 966 | ^When a column is indexed, the same collating function specified in the [CREATE TABLE] statement is used for the column in the index, by default, though this can be overridden using a COLLATE clause in the [CREATE INDEX] statement. <h3>2.3 Representation Of SQL Tables</h3> | | | 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 | ^When a column is indexed, the same collating function specified in the [CREATE TABLE] statement is used for the column in the index, by default, though this can be overridden using a COLLATE clause in the [CREATE INDEX] statement. <h3>2.3 Representation Of SQL Tables</h3> <p>Each ordinary SQL table in the database schema is represented on-disk by a table b-tree. Each entry in the table b-tree corresponds to a row of the SQL table. The [rowid] of the SQL table is the 64-bit signed integer key for each entry in the table b-tree.</p> <p>The content of each SQL table row is stored in the database file by first combining the values in the various columns into a byte array in the record format, then storing that byte array as the payload in |
︙ | ︙ | |||
983 984 985 986 987 988 989 990 | <p>^If the [affinity] of a column is REAL and that column contains a value that can be converted to an integer without loss of information (if the value contains no fractional part and is not too large to be represented as an integer) then the column may be stored in the record as an integer. ^SQLite will convert the value back to floating point when extracting it from the record.</p> | > > > > > > > > > > > > > > > > > > > | | | | > > > | < | | | > > > | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 | <p>^If the [affinity] of a column is REAL and that column contains a value that can be converted to an integer without loss of information (if the value contains no fractional part and is not too large to be represented as an integer) then the column may be stored in the record as an integer. ^SQLite will convert the value back to floating point when extracting it from the record.</p> <h3>2.4 Representation of WITHOUT ROWID Tables</h3> <p>If an SQL table is created using the "WITHOUT ROWID" clause at the end of its CREATE TABLE statement, then that table is a [WITHOUT ROWID] table and uses a different on-disk representation. A WITHOUT ROWID table uses an index b-tree rather than a table b-tree for storage. The key for each entry in the WITHOUT ROWID b-tree is a record composed of the columns of the PRIMARY KEY followed by all remaining columns of the table. The primary key columns appear in the order they they were declared in the PRIMARY KEY clause and the remaining columns appear in the order they occur in the CREATE TABLE statement. <p>^Hence, the content encoding for a WITHOUT ROWID table is the same as the content encoding for an ordinary rowid table, except that the order of the columns is rearranged so that PRIMARY KEY columns come first, and the content is used as the key in an index b-tree rather than as the data in a table b-tree. ^The special encoding rules for columns with REAL affinity apply to WITHOUT ROWID tables the same as they do with rowid tables. <h3>2.5 Representation Of SQL Indices</h3> <p>^Each SQL index, whether explicitly declared via a [CREATE INDEX] statement or implied by a UNIQUE or PRIMARY KEY constraint, corresponds to an index b-tree in the database file. ^Each entry in the index b-tree corresponds to a single row in the associated SQL table. ^The key to an index b-tree is a record composed of the columns that are being indexed followed by the key of the corresponding table row. For ordinary tables, the row key is the [rowid], and for [WITHOUT ROWID] tables the row key is the PRIMARY KEY. Because every row in the table has a unique row key, all keys in an index are unique.</p> <p>^In a normal index, there is a one-to-one mapping between rows in a table and entries in each index associated with that table. ^However, in a [partial index], the index b-tree only contains entries corresponding to table rows for which the WHERE clause expression on the CREATE INDEX statement is true. ^Corresponding rows in the index and table b-trees share the same rowid or primary key values and contain the same value for all indexed columns.</p> <h4>2.5.1 Suppression of redundant columns in WITHOUT ROWID secondary indexed </h4> <p>In an index on a WITHOUT ROWID table, if one or more of the columns of table PRIMARY KEY are also columns of the index, then the indexed column is not repeated in the table-key suffix on the end of the index record. As an example, consider the following SQL: <blockquote><pre> CREATE TABLE ex25(a,b,c,d,e,PRIMARY KEY(d,c,a)) WITHOUT rowid; CREATE INDEX ex25ce ON ex25(c,e); CREATE INDEX ex25acde ON ex25(a,c,d,e); </pre></blockquote> <p>Each row in the ex25ce index of the example above consists of a record containing the following columns: c, e, d, a. The first two columns are the columns being indexed, c and e. The remaining columns are the primary key of the corresponding table row. Normally, the primary key would be columns d, c, and a, but because column c already appears earlier in the index, it is omitted from the key suffix.</p> <p>In the extreme case where the columns being indexed cover all columns of the PRIMARY KEY, the index will consist of only the columns being indexed. The ex25acde example above demonstrates this. Each entry in the ex25acde index consists of only the columns a, c, d, and e, in that order.</p> <p>The suppression of redundant columns in the key suffix of an index entry only occurs in WITHOUT ROWID tables. In an ordinary rowid table, the index entry always ends with the rowid even if the [INTEGER PRIMARY KEY] column is one of the columns being indexed.</p> <tcl>hd_fragment sqlite_master {sqlite_master} {sqlite_master table}</tcl> <h3>2.6 Storage Of The SQL Database Schema</h3> <p>^Page 1 of a database file is the root page of a table b-tree that holds a special table named "sqlite_master" (or "sqlite_temp_master" in the case of a TEMP database) which stores the complete database schema. ^(The structure of the sqlite_master table is as if it had been created using the following SQL:</p> |
︙ | ︙ | |||
1037 1038 1039 1040 1041 1042 1043 | for both ordinary and [virtual tables].)^</p> </p>^(The sqlite_master.name column will hold the name of the object.)^ ^([UNIQUE] and [PRIMARY KEY] constraints on tables cause SQLite to create [internal indices] with names of the form "sqlite_autoindex_TABLE_N" where TABLE is replaced by the name of the table that contains the constraint and N is an integer beginning with 1 and increasing by one | | > > > > > > > | 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 | for both ordinary and [virtual tables].)^</p> </p>^(The sqlite_master.name column will hold the name of the object.)^ ^([UNIQUE] and [PRIMARY KEY] constraints on tables cause SQLite to create [internal indices] with names of the form "sqlite_autoindex_TABLE_N" where TABLE is replaced by the name of the table that contains the constraint and N is an integer beginning with 1 and increasing by one with each constraint seen in the table definition.)^ ^(In a [WITHOUT ROWID] table, there is no sqlite_master entry for the PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside for the PRIMARY KEY as if the sqlite_master entry did exist. This will affect the numbering of subsequent UNIQUE constraints.)^ ^The "sqlite_autoindex_TABLE_N" name is never allocated for an [INTEGER PRIMARY KEY], either in rowid tables or WITHOUT ROWID tables. </p> <p>The sqlite_master.tbl_name column holds the name of a table or view that the object is associated with. ^For a table or view, the tbl_name column is a copy of the name column. ^For an index, the tbl_name is the name of the table that is indexed. ^For a trigger, the tbl_name column stores the name of the table or view that causes the trigger to fire.</p> |
︙ | ︙ | |||
1081 1082 1083 1084 1085 1086 1087 | ^(The sqlite_master.sql is NULL for the [internal indices] that are automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p> <tcl>hd_fragment intschema {internal schema objects} \ {internal schema object} {internal index} {internal indices} \ {internal table} {internal tables}</tcl> | | | 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 | ^(The sqlite_master.sql is NULL for the [internal indices] that are automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p> <tcl>hd_fragment intschema {internal schema objects} \ {internal schema object} {internal index} {internal indices} \ {internal table} {internal tables}</tcl> <h4>2.6.1 Internal Schema Objects</h4> <p>^In addition to the tables, indices, views, and triggers created by the application and/or the developer using CREATE statements SQL, the sqlite_master table may contain zero or more entries for <i>internal schema objects</i> that are created by SQLite for its own internal use. ^The names of internal schema objects always begin with "sqlite_" and any table, index, view, or trigger |
︙ | ︙ | |||
1110 1111 1112 1113 1114 1115 1116 | <li><p>Tables with names of the form "sqlite_statN" where N is an integer. Such tables store database statistics gathered by the [ANALYZE] command and used by the query planner to help determine the best algorithm to use for each query. </ul> | | | | 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 | <li><p>Tables with names of the form "sqlite_statN" where N is an integer. Such tables store database statistics gathered by the [ANALYZE] command and used by the query planner to help determine the best algorithm to use for each query. </ul> <p>New internal schema objects names, always beginning with "sqlite_", may be added to the SQLite file format in future releases. <tcl>hd_fragment seqtab {sqlite_sequence}</tcl> <h4>2.6.2 The sqlite_sequence table</h4> <p>^The sqlite_sequence table is an internal table used to help implement [AUTOINCREMENT]. ^The sqlite_sequence table is created automatically whenever any ordinary table with an AUTOINCREMENT integer primary key is created. ^Once created, the sqlite_sequence table exists in the sqlite_master table forever; it cannot be dropped. The schema for the sqlite_sequence table is: |
︙ | ︙ | |||
1155 1156 1157 1158 1159 1160 1161 | <p>^Application code is allowed to modify the sqlite_sequence table, to add new rows, to delete rows, or to modify existing rows. ^However, application code cannot create the sqlite_sequence table if it does not already exist. ^Application code can delete all entries from the sqlite_sequence table, but application code cannot drop the sqlite_sequence table. <tcl>hd_fragment stat1tab {sqlite_stat1} SQLITE_STAT1 </tcl> | | | 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 | <p>^Application code is allowed to modify the sqlite_sequence table, to add new rows, to delete rows, or to modify existing rows. ^However, application code cannot create the sqlite_sequence table if it does not already exist. ^Application code can delete all entries from the sqlite_sequence table, but application code cannot drop the sqlite_sequence table. <tcl>hd_fragment stat1tab {sqlite_stat1} SQLITE_STAT1 </tcl> <h4>2.6.3 The sqlite_stat1 table</h4> <p>^The sqlite_stat1 is an internal table created by the [ANALYZE] command and used to hold supplemental information about tables and indices that the query planner can use to help it find better ways of performing queries. ^Applications can update, delete from, insert into or drop the sqlite_stat1 table, but may not create or alter the sqlite_stat1 table. ^The schema of the sqlite_stat1 table is as follows: |
︙ | ︙ | |||
1204 1205 1206 1207 1208 1209 1210 | 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> | | | 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 | 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.6.4 The sqlite_stat2 table</h4> <p>The sqlite_stat2 is only created and is only used if SQLite is compiled with SQLITE_ENABLE_STAT2 and if the SQLite version number is between 3.6.18 and 3.7.8. The sqlite_stat2 table is neither read nor written by any version of SQLite before 3.6.18 nor after 3.7.8. The sqlite_stat2 table contains additional information about the distribution of keys within an index. |
︙ | ︙ | |||
1242 1243 1244 1245 1246 1247 1248 | 10 uniform buckets and the samples are the middle row from each bucket. <p>The format for sqlite_stat2 is recorded here for legacy reference. Recent versions of SQLite no longer support sqlite_stat2 and the sqlite_stat2 table, it is exists, is simply ignored. <tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl> | | | 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 | 10 uniform buckets and the samples are the middle row from each bucket. <p>The format for sqlite_stat2 is recorded here for legacy reference. Recent versions of SQLite no longer support sqlite_stat2 and the sqlite_stat2 table, it is exists, is simply ignored. <tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl> <h4>2.6.5 The sqlite_stat3 table</h4> <p>The sqlite_stat3 is only only used if SQLite is compiled with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4] and if the SQLite version number is 3.7.9 or greater. The sqlite_stat3 table is neither read nor written by any version of SQLite before 3.7.9. If the [SQLITE_ENABLE_STAT4] compile-time option is used and the |
︙ | ︙ | |||
1279 1280 1281 1282 1283 1284 1285 | <p>There can be an arbitrary number of sqlite_stat3 entries per index. The [ANALYZE] command will typically generate sqlite_stat3 tables that contain between 10 and 40 samples that are distributed across the key space and with large nEq values. <tcl>hd_fragment stat4tab {sqlite_stat4} SQLITE_STAT4</tcl> | | | 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 | <p>There can be an arbitrary number of sqlite_stat3 entries per index. The [ANALYZE] command will typically generate sqlite_stat3 tables that contain between 10 and 40 samples that are distributed across the key space and with large nEq values. <tcl>hd_fragment stat4tab {sqlite_stat4} SQLITE_STAT4</tcl> <h4>2.6.6 The sqlite_stat4 table</h4> <p>The sqlite_stat4 is only created and is only used if SQLite is compiled with [SQLITE_ENABLE_STAT4] and if the SQLite version number is 3.8.1 or greater. The sqlite_stat4 table is neither read nor written by any version of SQLite before 3.8.1. The sqlite_stat4 table contains additional information about the distribution of keys within an index, information that the |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
675 676 677 678 679 680 681 682 683 684 685 686 687 688 | <li><p> A default collation sequence to use with each column. <li><p> Optionally, a PRIMARY KEY for the table. Both single column and composite (multiple column) primary keys are supported. <li><p> A set of SQL constraints for each table. SQLite supports UNIQUE, NOT NULL, CHECK and FOREIGN KEY constraints. </ul> <p>Every CREATE TABLE statement must specify a name for the new table. ^(Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".)^ | > > | 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 | <li><p> A default collation sequence to use with each column. <li><p> Optionally, a PRIMARY KEY for the table. Both single column and composite (multiple column) primary keys are supported. <li><p> A set of SQL constraints for each table. SQLite supports UNIQUE, NOT NULL, CHECK and FOREIGN KEY constraints. <li><p> Whether the table is a [WITHOUT ROWID] table. </ul> <p>Every CREATE TABLE statement must specify a name for the new table. ^(Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".)^ |
︙ | ︙ | |||
803 804 805 806 807 808 809 | <tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl> <p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. ^Or, if a PRIMARY KEY clause is specified as a [table-constraint], then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. | | | > | > | | | | > | | < | | < < | | | < < < | | > > > | > > | | < | > | 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 | <tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl> <p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. ^Or, if a PRIMARY KEY clause is specified as a [table-constraint], then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. ^An error is rasied if more than one PRIMARY KEY clause appears in a CREATE TABLE statement. ^The PRIMARY KEY is optional for ordinary tables but is required for [WITHOUT ROWID] tables. <p>If a table has a single column primary key and the declared type of that column is "INTEGER" and the table is not a [WITHOUT ROWID] table, then the column is known as an [INTEGER PRIMARY KEY]. See below for a description of the special properties and behaviors associated with an [INTEGER PRIMARY KEY]. <p>^Each row in a table with a primary key must have a unique combination of values in its primary key columns. ^For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so that two or more rows feature identical primary key values, it is a constraint violation. According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. ^Unless the column is an [INTEGER PRIMARY KEY] or unless the table is a [WITHOUT ROWID] table, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns. <tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint}</tcl> <p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. ^For each UNIQUE constraint on the table, each row must feature a unique combination of values in the columns identified by the UNIQUE constraint. ^For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs. <p>^In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are [INTEGER PRIMARY KEY] and PRIMARY KEYs on [WITHOUT ROWID] tables.) Hence, the following schemas are logically equivalent: <ol> <li><p>CREATE TABLE t1(a, b UNIQUE); <li><p>CREATE TABLE t1(a, b PRIMARY KEY); <li><p>CREATE TABLE t1(a, b);<br> CREATE UNIQUE INDEX t1b ON t1(b); </ol> <tcl>hd_fragment {ckconst} {CHECK} {CHECK constraint} {CHECK constraints}</tcl> <p>^(A <b>CHECK</b> constraint may be attached to a column definition or specified as a table constraint. In practice it makes no difference.)^ ^(Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a [CAST expression]. If the |
︙ | ︙ | |||
882 883 884 885 886 887 888 | conflict resolution algorithm is ABORT. ^Different constraints within the same table may have different default conflict resolution algorithms. See the section titled [ON CONFLICT] for additional information. <tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl> <h3>ROWIDs and the INTEGER PRIMARY KEY</h3> | > | | > > > > | | | | | > | < | 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 | conflict resolution algorithm is ABORT. ^Different constraints within the same table may have different default conflict resolution algorithms. See the section titled [ON CONFLICT] for additional information. <tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl> <h3>ROWIDs and the INTEGER PRIMARY KEY</h3> <p>^Except for [WITHOUT ROWID] tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". ^The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. ^If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value. <p>The rowid (and "oid" and "_rowid_") is omitted in [WITHOUT ROWID] tables. WITHOUT ROWID tables are only available in SQLite [version 3.8.2] and later. A table that lacks the WITHOUT ROWID clause is called a "rowid table". <p>The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. <p> ^With one exception noted below, if a rowid table has a mary key that consists of a single column, and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". ^Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer [affinity] and a unique index, not as an alias for the rowid. <p> The exception mentioned above is that ^if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key. This quirk is not by design. It is due to a bug in early versions of SQLite. But fixing the bug could result in backwards incompatibilities. Hence, the original behavior has been retained (and documented) because behavior in a corner case is far better than a compatibility break. This means that ^(the following three table declarations all cause the column "x" to be an alias for the rowid (an integer primary key): <ul> <li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);</tt> <li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));</tt> <li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));</tt> |
︙ | ︙ |
Added pages/withoutrowid.in.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 | <title>The WITHOUT ROWID Optimization</title> <tcl> hd_keywords {WITHOUT rowid} {WITHOUT ROWID} </tcl> <h1 align="center">The WITHOUT ROWID Optimization</h1> <h2>1.0 Introduction</h2> <p>By default, every row in SQLite has a special column, usually called the "[rowid]", that is a uniquely identifies that row within the table. However if the phrase "WITHOUT ROWID" is added to the end of a [CREATE TABLE] statement, then the special "rowid" column is omitted. There are sometimes space and performance advantages to omitting the rowid.</p> <h3>1.1 Syntax</h3> <p>^(To create a WITHOUT ROWID table, simply add the keywords "WITHOUT ROWID" to the end of the [CREATE TABLE] statement. For example:</p> <blockquote><pre> CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY KEY, cnt INTEGER ) <b>WITHOUT ROWID</b>; </pre></blockquote>)^ <p>^(As with all SQL syntax, the case of the keywords does not matter. One can write "WITHOUT rowid" or "without rowid" or "WiThOuT rOwId" and it will mean the same thing.)^</p> <p>Every WITHOUT ROWID table must have a [PRIMARY KEY]. ^An error is raised if a CREATE TABLE statement with the WITHOUT ROWID clause lacks a PRIMARY KEY. <p>In most contexts, the special "rowid" column of normal tables can also be called "oid" or "_rowid_". ^However, only "rowid" works as the keyword in the CREATE TABLE statement.</p> <h3>1.2 Compatibility</h3> <p>SQLite [version 3.8.2] or later is necessary in order to use a WITHOUT ROWID table. An attempt to open a database that contains one or more WITHOUT ROWID tables using an earlier version of SQLite will result in a "malformed database schema" error.</p> <h2>2.0 Differences From Ordinary Rowid Tables</h2> <p>The WITHOUT ROWID syntax is an optimization. It provides no new capabilities. Anything that can be done using a WITHOUT ROWID table can also be done in exactly the same way, and exactly the same syntax, using an ordinary rowid table. The only advantage of a WITHOUT ROWID table is that it can sometimes use less disk space and/or perform a little faster than an ordinary rowid table.</p> <p>For the most part, ordinary rowid tables and WITHOUT ROWID tables are interchangeable. But there are some additional restrictions on WITHOUT ROWID tables that do not apply to ordinary rowid tables:</p> <ol> <li><p> <b>^Every WITHOUT ROWID table must have a PRIMARY KEY.</b> ^An attempt to create a WITHOUT ROWID table without a PRIMARY KEY results in an error. <li><p> <b>^The special behaviors associated "[INTEGER PRIMARY KEY]" do not apply on WITHOUT ROWID tables.</b> In an ordinary table, "INTEGER PRIMARY KEY" means that the column is an alias for the rowid. But since there is no rowid in a WITHOUT ROWID table, that special meaning no longer applies. An "INTEGER PRIMARY KEY" works like an "INT PRIMARY KEY" in an ordinary table: It is a PRIMARY KEY that has integer [affinity]. <li><p> <b>^[AUTOINCREMENT] does not work on WITHOUT ROWID tables.</b> The [AUTOINCREMENT] mechanism assumes the presence of a rowid and so it does not work on a WITHOUT ROWID table. ^An error is raised if the "AUTOINCREMENT" keyword is used in the CREATE TABLE statement for a WITHOUT ROWID table. <li><p> <b>^NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID table.</b> This is in accordance with the SQL standard. Each column of a PRIMARY KEY is supposed to be individually NOT NULL. However, NOT NULL was not enforced on PRIMARY KEY columns by early versions of SQLite due to a bug. By the time that this bug was discovered, so many SQLite databases were already in circulation that the decision was made not to fix this bug for fear of breaking compatibility. ^So, ordinary rowid tables in SQLite violate the SQL standard and allow NULL values in PRIMARY KEY fields. ^But WITHOUT ROWID tables do follow the standard and will throw an error on any attempt to insert a NULL into a PRIMARY KEY column. <li><p> <b>^The [sqlite3_last_insert_rowid()] function does not work for WITHOUT ROWID tables.</b> Inserts into a WITHOUT ROWID do not change the value returned by the [sqlite3_last_insert_rowid()] function. The [last_insert_rowid()] SQL function is also unaffected since it is just a wrapper around [sqlite3_last_insert_rowid()]. <li><p> <b>^The [sqlite3_blob_open | incremental blob I/O] mechanism does not work for WITHOUT ROWID tables.</b> Incremental BLOB I/O uses the rowid to create an [sqlite3_blob] object for doing the direct I/O. However, WITHOUT ROWID tables do not have a rowid, and so there is no way to create an [sqlite3_blob] object for a WITHOUT ROWID table. <li><p> <b>^The [sqlite3_update_hook()] interface does not fire callbacks for changes to a WITHOUT ROWID table.</b> Part of the callback from [sqlite3_update_hook()] is the rowid of the table row that has changed. However, WITHOUT ROWID tables do not have a rowid. Hence, the update hook is not invoked when a WITHOUT ROWID table changes. <p>Note that since the [session] extension uses the update hook, that means that the session extension will not work correctly on a database that includes WITHOUT ROWID tables. </ol> <h2>3.0 Benefits Of WITHOUT ROWID Tables</h2> <p>A WITHOUT ROWID table is an optimization that can reduce storage and processing requirements. <p>In an ordinary SQLite table, the PRIMARY KEY is really just a [UNIQUE] index. The key used to look up records on disk is the [rowid]. The special "[INTEGER PRIMARY KEY]" column type in ordinary SQLite tables causes the column to be an alias for the rowid, and so an INTEGER PRIMARY KEY is a true PRIMARY KEY. But any other kind of PRIMARY KEYs, including "INT PRIMARY KEY" are just unique indexes in an ordinary rowid table.</p> <p>Consider a table (shown below) intended to store a vocabulary of words together with a count of the number of occurrences of each word in some text corpus: <blockquote><pre> CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY KEY, cnt INTEGER ); </pre></blockquote> <p>As an ordinary SQLite table, "wordcount" is implemented as two separate B-Trees. The main table uses the hidden rowid value as the key and stores the "word" and "cnt" columns as data. The "TEXT PRIMARY KEY" phrase of the CREATE TABLE statement causes the creation of an [unique index] on the "word" column. This index is a separate B-Tree that uses "word" and the "rowid" as the key and stores no data at all. Note that the complete text of every "word" is stored twice: once in the main table and again in the index. <p>Consider querying this table to find the number of occurrences of the word "xyzzy".: <blockquote><pre> SELECT cnt FROM wordcount WHERE word='xyzzy'; </pre></blockquote> <p>This query first has to search the index B-Tree looking for any entry that contains the matching value for "word". When an entry is found in the index, the rowid is extracted and used to search the main table. Then the "cnt" value is read out of the main table and returned. Hence, two separate binary searches are required to fulfill the request. <p>A WITHOUT ROWID table uses a different data design for the equivalent table. <blockquote><pre> CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY KEY, cnt INTEGER ) WITHOUT ROWID; </pre></blockquote> <p>In this latter table, there is only a single B-Tree which uses the "word" column as its key and the "cnt" column as its data. (Technicality: the low-level implementation actually stores both "word" and "cnt" in the "key" area of the B-Tree. But unless you are looking at the low-level byte encoding of the database file, that fact is unimportant.) Because there is only a single B-Tree, the text of the "word" column is only stored once in the database. Furthermore, querying the "cnt" value for a specific "word" only involves a single binary search into the main B-Tree, since the "cnt" value can be retrieved directly from the record found by that first search and without the need to do a second binary search on the rowid. <p>Thus, in some cases, a WITHOUT ROWID table can use about half the amount of disk space and can operation nearly twice as fast. Of course, in a real-world schema, there will typically be secondary indices and/or UNIQUE constraints, and the situation is more complicated. But even then, there can often be space and performance advantages to using WITHOUT ROWID on tables that have non-integer or composite PRIMARY KEYs. <h2>4.0 When To Use WITHOUT ROWID</h2> <p>The WITHOUT ROWID optimization is likely to be helpful for tables that have non-integer or composite (multi-column) PRIMARY KEYs and that do not store large strings or BLOBs.</p> <p>WITHOUT ROWID tables will work correctly (that is to say, they provide the correct answer) for tables with a single INTEGER PRIMARY KEY. However, ordinary rowid tables will run faster in that case. Hence, it is good design to avoid creating WITHOUT ROWID tables with single-column PRIMARY KEYs of type INTEGER. <p>WITHOUT ROWID tables work best when individual rows are not too large. A good rule-of-thumb is that the average size of a single row in a WITHOUT ROWID table should be less than about 1/20th the size of a database page. That means that rows should not contain more than about 50 bytes each for a 1KiB page size or about 200 bytes each for 4KiB page size. WITHOUT ROWID tables will work (in the sense that they get the correct answer) for arbitrarily large rows - up to 2GB in size - but traditional rowid tables tend to work faster for large row sizes. This is because rowid tables are implemented as [B*-Trees] where all content is stored in the leaves of the tree, whereas WITHOUT ROWID tables are implemented using ordinary B-Trees with content stored on both leaves and intermediate nodes. Storing content in intermediate nodes mean that each intermediate node entry takes up more space on the page and thus reduces the fan-out, increasing the search cost. <p>Note that except for a few corner-case differences detailed above, WITHOUT ROWID tables and rowid tables work the same. They both generate the same answers given the same SQL statements. So it is a simple matter to run experiments on an application, late in the development cycle, to test whether or not the use of WITHOUT ROWID tables will be helpful. A good strategy is to simply not worry about WITHOUT ROWID until near the end of product development, then go back and run tests to see if adding WITHOUT ROWID to tables with non-integer PRIMARY KEYs helps or hurts performance, and retaining the WITHOUT ROWID only in those cases where it helps. |