Adding upstream version 3.45.1.upstream/3.45.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 14:07:11 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 14:07:11 +0000
commit: 63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree: 01c7571c7c762ceee70638549a99834fdd7c411b /www/withoutrowid.html
parent: Initial commit. (diff)
download: sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz
sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip
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">&#x25ba;</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 = "&#x25bc;";
+} else {
+sub.style.display = "none";
+mk.innerHTML = "&#x25ba;";
+}
+}
+</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>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 14:07:11 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 14:07:11 +0000
commit	63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree	01c7571c7c762ceee70638549a99834fdd7c411b /www/withoutrowid.html
parent	Initial commit. (diff)
download	sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip