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
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Clustered Indexes and the WITHOUT ROWID Optimization</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Clustered Indexes and the WITHOUT ROWID Optimization
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
<div class="fancy-toc2"><a href="#syntax">1.1. Syntax</a></div>
<div class="fancy-toc2"><a href="#compatibility">1.2. Compatibility</a></div>
<div class="fancy-toc2"><a href="#quirks">1.3. Quirks</a></div>
<div class="fancy-toc1"><a href="#differences_from_ordinary_rowid_tables">2. Differences From Ordinary Rowid Tables</a></div>
<div class="fancy-toc1"><a href="#benefits_of_without_rowid_tables">3. Benefits Of WITHOUT ROWID Tables</a></div>
<div class="fancy-toc1"><a href="#when_to_use_without_rowid">4. When To Use WITHOUT ROWID</a></div>
<div class="fancy-toc1"><a href="#determining_if_an_existing_table_is_without_rowid">5. Determining If An Existing Table Is WITHOUT ROWID</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="introduction"><span>1. </span>Introduction</h1>
<p>By default, every row in SQLite has a special column, usually called the
"<a href="lang_createtable.html#rowid">rowid</a>", that uniquely identifies that row within the table. However
if the phrase "WITHOUT ROWID" is added to the end of a <a href="lang_createtable.html">CREATE TABLE</a> statement,
then the special "rowid" column is omitted. There are sometimes
space and performance advantages to omitting the rowid.</p>
<p>A WITHOUT ROWID table is a table that uses a
<a href="https://en.wikipedia.org/wiki/Database_index#Clustered">Clustered Index</a>
as the primary key.</p>
<h2 id="syntax"><span>1.1. </span>Syntax</h2>
<p>To create a WITHOUT ROWID table, simply add the keywords "WITHOUT ROWID"
to the end of the <a href="lang_createtable.html">CREATE TABLE</a> 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 <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a>. An error is raised
if a CREATE TABLE statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
</p><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>
<h2 id="compatibility"><span>1.2. </span>Compatibility</h2>
<p>SQLite <a href="releaselog/3_8_2.html">version 3.8.2</a> (2013-12-06) 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 id="quirks"><span>1.3. </span>Quirks</h2>
<p>WITHOUT ROWID is found only in SQLite and is not compatible
with any other SQL database engine, as far as we know.
In an elegant system, all tables would behave as WITHOUT ROWID
tables even without the WITHOUT ROWID keyword. However, when SQLite was
first designed, it used only integer <a href="lang_createtable.html#rowid">rowids</a> for row keys
to simplify the implementation.
This approach worked well for many years. But as the demands on
SQLite grew, the need for tables in which the PRIMARY KEY really did
correspond to the underlying row key grew more acute. The WITHOUT ROWID
concept was added
in order to meet that need without breaking backwards
compatibility with the billions of SQLite databases already in use at
the time (circa 2013).
</p><h1 id="differences_from_ordinary_rowid_tables"><span>2. </span>Differences From Ordinary Rowid Tables</h1>
<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.
</p></li><li><p>
<b>The special behaviors associated "<a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>" 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"
column in a WITHOUT ROWID table works
like an "INT PRIMARY KEY" column in an ordinary table: It is a PRIMARY KEY
that has integer <a href="datatype3.html#affinity">affinity</a>.
</p></li><li><p>
<b><a href="autoinc.html">AUTOINCREMENT</a> does not work on WITHOUT ROWID tables.</b>
The <a href="autoinc.html">AUTOINCREMENT</a> 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.
</p></li><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.
</p></li><li><p>
<b>The <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> function
does not work for WITHOUT ROWID tables.</b>
Inserts into a WITHOUT ROWID do not change the value returned by the
<a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> function. The <a href="lang_corefunc.html#last_insert_rowid">last_insert_rowid()</a> SQL
function is also unaffected since it is just a wrapper around
<a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a>.
</p></li><li><p>
<b>The <a href="c3ref/blob_open.html">incremental blob I/O</a> mechanism does not work
for WITHOUT ROWID tables.</b>
Incremental BLOB I/O uses the rowid to create an <a href="c3ref/blob.html">sqlite3_blob</a> 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 <a href="c3ref/blob.html">sqlite3_blob</a> object for a WITHOUT
ROWID table.
</p></li><li><p>
<b>The <a href="c3ref/update_hook.html">sqlite3_update_hook()</a> interface does not fire callbacks for changes
to a WITHOUT ROWID table.</b>
Part of the callback from <a href="c3ref/update_hook.html">sqlite3_update_hook()</a> 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></li></ol>
<a name="bene"></a>
<h1 id="benefits_of_without_rowid_tables"><span>3. </span>Benefits Of WITHOUT ROWID Tables</h1>
<p>A WITHOUT ROWID table is an optimization that can reduce storage and
processing requirements.
</p><p>In an ordinary SQLite table, the PRIMARY KEY is really just a
<a href="lang_createtable.html#uniqueconst">UNIQUE</a> index. The key used to look up records on disk
is the <a href="lang_createtable.html#rowid">rowid</a>.
The special "<a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>" 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:
</p><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 <a href="lang_createindex.html#uniqueidx">unique index</a> 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><p>Consider querying this table to find the number of occurrences of the
word "xsync".:
</p><blockquote><pre>
SELECT cnt FROM wordcount WHERE word='xsync';
</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><p>A WITHOUT ROWID table uses a different data design for the equivalent
table.
</p><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><p>Thus, in some cases, a WITHOUT ROWID table can use about half the amount
of disk space and can operate 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.
<a name="wtu"></a>
</p><h1 id="when_to_use_without_rowid"><span>4. </span>When To Use WITHOUT ROWID</h1>
<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><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 <a href="fileformat2.html#btree">B*-Trees</a> 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 causes each intermediate node entry to take up more
space on the page and thus reduces the fan-out, increasing the search cost.
</p><p>The "sqlite3_analyzer.exe" utility program, available as source code
in the SQLite source tree or as a precompiled binary on the
<a href="https://www.sqlite.org/download.html">SQLite Download page</a>, 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.
</p><h1 id="determining_if_an_existing_table_is_without_rowid"><span>5. </span>Determining If An Existing Table Is WITHOUT ROWID</h1>
<p>A WITHOUT ROWID table returns the same content for
<a href="pragma.html#pragma_table_info">PRAGMA table_info</a> and <a href="pragma.html#pragma_table_xinfo">PRAGMA table_xinfo</a> as does an ordinary
table. But unlike an ordinary table, a WITHOUT ROWID also
responds to the <a href="pragma.html#pragma_index_info">PRAGMA index_info</a> command. The <a href="pragma.html#pragma_index_info">PRAGMA index_info</a>
on a WITHOUT ROWID table returns information about the PRIMARY KEY
for the table. In this way, the <a href="pragma.html#pragma_index_info">PRAGMA index_info</a> command can be
used to unambiguously determine whether a particular table is a
WITHOUT ROWID table or an ordinary table - an ordinary table will
always return no rows but a WITHOUT ROWID table will always return
one or more rows.
</p>
|
