Documentation Source Text
Check-in [478a586d67]
Not logged in

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

Overview
SHA1 Hash:478a586d675dc5801b640e4e3847dfe0373c5804
Date: 2013-11-09 17:27:36
User: drh
Comment:Tweaks to WITHOUT ROWID documentation.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fileformat2.in

495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
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







|
|







495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
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 index b-trees
rather than a table b-trees, 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

Changes to pages/withoutrowid.in

214
215
216
217
218
219
220






221
222
223
224
225
226
227
228
229
230
231
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.







>
>
>
>
>
>











214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
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>The "sqlite3_analyzer.exe" utility program, available as source code
in the SQLite source tree or as a precompiled binary on the
[http://www.sqlite.org/download.html | SQLite Download page], can be
used to measure the average sizes of table rows in an existing SQLite
database.</p>

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