diff options
Diffstat (limited to 'www/withoutrowid.html')
| -rw-r--r-- | www/withoutrowid.html | 424 |
1 files changed, 424 insertions, 0 deletions
diff --git a/www/withoutrowid.html b/www/withoutrowid.html new file mode 100644 index 0000000..8944a07 --- /dev/null +++ b/www/withoutrowid.html @@ -0,0 +1,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> + |