| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>Temporary Files Used By SQLite</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
Temporary Files Used By SQLite |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div> |
| 126 |
<div class="fancy-toc1"><a href="#nine_kinds_of_temporary_files">2. Nine Kinds Of Temporary Files</a></div> |
| 127 |
<div class="fancy-toc2"><a href="#rollback_journals">2.1. Rollback Journals</a></div> |
| 128 |
<div class="fancy-toc2"><a href="#write_ahead_log_wal_files">2.2. Write-Ahead Log (WAL) Files</a></div> |
| 129 |
<div class="fancy-toc2"><a href="#shared_memory_files">2.3. Shared-Memory Files</a></div> |
| 130 |
<div class="fancy-toc2"><a href="#super_journal_files">2.4. Super-Journal Files</a></div> |
| 131 |
<div class="fancy-toc2"><a href="#statement_journal_files">2.5. Statement Journal Files</a></div> |
| 132 |
<div class="fancy-toc2"><a href="#temp_databases">2.6. TEMP Databases</a></div> |
| 133 |
<div class="fancy-toc2"><a href="#materializations_of_views_and_subqueries">2.7. Materializations Of Views And Subqueries</a></div> |
| 134 |
<div class="fancy-toc2"><a href="#transient_indices">2.8. Transient Indices</a></div> |
| 135 |
<div class="fancy-toc2"><a href="#transient_database_used_by_vacuum_">2.9. Transient Database Used By <a href="lang_vacuum.html">VACUUM</a></a></div> |
| 136 |
<div class="fancy-toc1"><a href="#the_sqlite_temp_store_compile_time_parameter_and_pragma">3. The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</a></div> |
| 137 |
<div class="fancy-toc1"><a href="#other_temporary_file_optimizations">4. Other Temporary File Optimizations</a></div> |
| 138 |
<div class="fancy-toc1"><a href="#temporary_file_storage_locations">5. Temporary File Storage Locations</a></div> |
| 139 |
</div> |
| 140 |
</details> |
| 141 |
</div> |
| 142 |
|
| 143 |
|
| 144 |
|
| 145 |
|
| 146 |
|
| 147 |
<h1 id="introduction"><span>1. </span>Introduction</h1> |
| 148 |
|
| 149 |
<p> |
| 150 |
One of the <a href="different.html">distinctive features</a> of |
| 151 |
SQLite is that a database consists of a single disk file. |
| 152 |
This simplifies the use of SQLite since moving or backing up a |
| 153 |
database is as simple as copying a single file. It also makes |
| 154 |
SQLite appropriate for use as an |
| 155 |
<a href="whentouse.html#appfileformat">application file format</a>. |
| 156 |
But while a complete database is held in a single disk file, |
| 157 |
SQLite does make use of many temporary files during the |
| 158 |
course of processing a database. |
| 159 |
</p> |
| 160 |
|
| 161 |
<p> |
| 162 |
This article describes the various temporary files that SQLite |
| 163 |
creates and uses. It describes when the files are created, when |
| 164 |
they are deleted, what they are used for, why they are important, |
| 165 |
and how to avoid them on systems where creating temporary files is |
| 166 |
expensive. |
| 167 |
</p> |
| 168 |
|
| 169 |
<p> |
| 170 |
The manner in which SQLite uses temporary files is not considered |
| 171 |
part of the contract that SQLite makes with applications. The |
| 172 |
information in this document is a correct description of how |
| 173 |
SQLite operates at the time that this document was written or last |
| 174 |
updated. But there is no guarantee that future versions of SQLite |
| 175 |
will use temporary files in the same way. New kinds of temporary |
| 176 |
files might be employed and some of |
| 177 |
the current temporary file uses might be discontinued |
| 178 |
in future releases of SQLite. |
| 179 |
</p> |
| 180 |
|
| 181 |
<a name="types"></a> |
| 182 |
|
| 183 |
<h1 id="nine_kinds_of_temporary_files"><span>2. </span>Nine Kinds Of Temporary Files</h1> |
| 184 |
|
| 185 |
<p> |
| 186 |
SQLite currently uses nine distinct types of temporary files: |
| 187 |
</p> |
| 188 |
|
| 189 |
<ol> |
| 190 |
<li>Rollback journals</li> |
| 191 |
<li>Super-journals</li> |
| 192 |
<li>Write-ahead Log (WAL) files</li> |
| 193 |
<li>Shared-memory files</li> |
| 194 |
<li>Statement journals</li> |
| 195 |
<li>TEMP databases</li> |
| 196 |
<li>Materializations of views and subqueries</li> |
| 197 |
<li>Transient indices</li> |
| 198 |
<li>Transient databases used by VACUUM</li> |
| 199 |
</ol> |
| 200 |
|
| 201 |
<p> |
| 202 |
Additional information about each of these temporary file types |
| 203 |
is in the sequel. |
| 204 |
</p> |
| 205 |
|
| 206 |
<a name="rollbackjrnl"></a> |
| 207 |
|
| 208 |
<h2 id="rollback_journals"><span>2.1. </span>Rollback Journals</h2> |
| 209 |
|
| 210 |
<p> |
| 211 |
A rollback journal is a temporary file used to implement |
| 212 |
atomic commit and rollback capabilities in SQLite. |
| 213 |
(For a detailed discussion of how this works, see |
| 214 |
the separate document titled |
| 215 |
<a href="atomiccommit.html">Atomic Commit In SQLite</a>.) |
| 216 |
The rollback journal is always located in the same directory |
| 217 |
as the database file and has the same name as the database |
| 218 |
file except with the 8 characters "<b>-journal</b>" appended. |
| 219 |
The rollback journal is usually created when a transaction |
| 220 |
is first started and is usually deleted when a transaction |
| 221 |
commits or rolls back. |
| 222 |
The rollback journal file is essential for implementing the |
| 223 |
atomic commit and rollback capabilities of SQLite. Without |
| 224 |
a rollback journal, SQLite would be unable to rollback an |
| 225 |
incomplete transaction, and if a crash or power loss occurred |
| 226 |
in the middle of a transaction the entire database would likely |
| 227 |
go corrupt without a rollback journal. |
| 228 |
</p> |
| 229 |
|
| 230 |
<p> |
| 231 |
The rollback journal is <i>usually</i> created and destroyed at the |
| 232 |
start and end of a transaction, respectively. But there are exceptions |
| 233 |
to this rule. |
| 234 |
</p> |
| 235 |
|
| 236 |
<p> |
| 237 |
If a crash or power loss occurs in the middle of a transaction, |
| 238 |
then the rollback journal file is left on disk. The next time |
| 239 |
another application attempts to open the database file, it notices |
| 240 |
the presence of the abandoned rollback journal (we call it a "hot |
| 241 |
journal" in this circumstance) and uses the information in the |
| 242 |
journal to restore the database to its state prior to the start |
| 243 |
of the incomplete transaction. This is how SQLite implements |
| 244 |
atomic commit. |
| 245 |
</p> |
| 246 |
|
| 247 |
<p> |
| 248 |
If an application puts SQLite in |
| 249 |
<a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> using |
| 250 |
the pragma: |
| 251 |
</p> |
| 252 |
|
| 253 |
<blockquote><pre> |
| 254 |
PRAGMA locking_mode=EXCLUSIVE; |
| 255 |
</pre></blockquote> |
| 256 |
|
| 257 |
<p> |
| 258 |
SQLite creates a new rollback journal at the start of the first |
| 259 |
transaction within an exclusive locking mode session. But at the |
| 260 |
conclusion of the transaction, it does not delete the rollback |
| 261 |
journal. The rollback journal might be truncated, or its header |
| 262 |
might be zeroed (depending on what version of SQLite you are using) |
| 263 |
but the rollback journal is not deleted. The rollback journal is |
| 264 |
not deleted until exclusive access mode is exited.</p> |
| 265 |
|
| 266 |
<p> |
| 267 |
Rollback journal creation and deletion is also changed by the |
| 268 |
<a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>. |
| 269 |
The default journaling mode is DELETE, which is the default behavior |
| 270 |
of deleting the rollback journal file at the end of each transaction, |
| 271 |
as described above. The PERSIST journal mode foregoes the deletion of |
| 272 |
the journal file and instead overwrites the rollback journal header |
| 273 |
with zeros, which prevents other processes from rolling back the |
| 274 |
journal and thus has the same effect as deleting the journal file, though |
| 275 |
without the expense of actually removing the file from disk. In other |
| 276 |
words, journal mode PERSIST exhibits the same behavior as is seen |
| 277 |
in EXCLUSIVE locking mode. The |
| 278 |
OFF journal mode causes SQLite to omit the rollback journal, completely. |
| 279 |
In other words, no rollback journal is ever written if journal mode is |
| 280 |
set to OFF. |
| 281 |
The OFF journal mode disables the atomic |
| 282 |
commit and rollback capabilities of SQLite. The ROLLBACK command |
| 283 |
is not available when OFF journal mode is set. And if a crash or power |
| 284 |
loss occurs in the middle of a transaction that uses the OFF journal |
| 285 |
mode, no recovery is possible and the database file will likely |
| 286 |
go corrupt. |
| 287 |
The MEMORY journal mode causes the rollback journal to be stored in |
| 288 |
memory rather than on disk. The ROLLBACK command still works when |
| 289 |
the journal mode is MEMORY, but because no file exists on disks for |
| 290 |
recovery, a crash or power loss in the middle of a transaction that uses |
| 291 |
the MEMORY journal mode will likely result in a corrupt database. |
| 292 |
</p> |
| 293 |
|
| 294 |
<a name="walfile"></a> |
| 295 |
|
| 296 |
<h2 id="write_ahead_log_wal_files"><span>2.2. </span>Write-Ahead Log (WAL) Files</h2> |
| 297 |
|
| 298 |
<p> |
| 299 |
A write-ahead log or WAL file is used in place of a rollback journal |
| 300 |
when SQLite is operating in <a href="wal.html">WAL mode</a>. As with the rollback journal, |
| 301 |
the purpose of the WAL file is to implement atomic commit and rollback. |
| 302 |
The WAL file is always located in the same directory |
| 303 |
as the database file and has the same name as the database |
| 304 |
file except with the 4 characters "<b>-wal</b>" appended. |
| 305 |
The WAL file is created when the first connection to the |
| 306 |
database is opened and is normally removed when the last |
| 307 |
connection to the database closes. However, if the last connection |
| 308 |
does not shutdown cleanly, the WAL file will remain in the filesystem |
| 309 |
and will be automatically cleaned up the next time the database is |
| 310 |
opened. |
| 311 |
</p> |
| 312 |
|
| 313 |
<a name="shmfile"></a> |
| 314 |
|
| 315 |
<h2 id="shared_memory_files"><span>2.3. </span>Shared-Memory Files</h2> |
| 316 |
|
| 317 |
<p> |
| 318 |
When operating in <a href="wal.html">WAL mode</a>, all SQLite database connections associated |
| 319 |
with the same database file need to share some memory that is used as an |
| 320 |
index for the WAL file. In most implementations, this shared memory is |
| 321 |
implemented by calling mmap() on a file created for this sole purpose: |
| 322 |
the shared-memory file. The shared-memory file, if it exists, is located |
| 323 |
in the same directory as the database file and has the same name as the |
| 324 |
database file except with the 4 characters "<b>-shm</b>" appended. |
| 325 |
Shared memory files only exist while running in WAL mode. |
| 326 |
</p> |
| 327 |
|
| 328 |
<p> |
| 329 |
The shared-memory file contains no persistent content. The only purpose |
| 330 |
of the shared-memory file is to provide a block of shared memory for use |
| 331 |
by multiple processes all accessing the same database in WAL mode. |
| 332 |
If the <a href="vfs.html">VFS</a> is able to provide an alternative method for accessing shared |
| 333 |
memory, then that alternative method might be used rather than the |
| 334 |
shared-memory file. For example, if <a href="pragma.html#pragma_locking_mode">PRAGMA locking_mode</a> is set to |
| 335 |
EXCLUSIVE (meaning that only one process is able to access the database |
| 336 |
file) then the shared memory will be allocated from heap rather than out |
| 337 |
of the shared-memory file, and the shared-memory file will never be |
| 338 |
created. |
| 339 |
</p> |
| 340 |
|
| 341 |
<p> |
| 342 |
The shared-memory file has the same lifetime as its associated WAL file. |
| 343 |
The shared-memory file is created when the WAL file is created and is |
| 344 |
deleted when the WAL file is deleted. During WAL file recovery, the |
| 345 |
shared memory file is recreated from scratch based on the contents of |
| 346 |
the WAL file being recovered. |
| 347 |
</p> |
| 348 |
|
| 349 |
<a name="superjrnl"></a> |
| 350 |
|
| 351 |
<h2 id="super_journal_files"><span>2.4. </span>Super-Journal Files</h2> |
| 352 |
|
| 353 |
<p> |
| 354 |
The super-journal file is used as part of the atomic commit |
| 355 |
process when a single transaction makes changes to multiple |
| 356 |
databases that have been added to a single <a href="c3ref/sqlite3.html">database connection</a> |
| 357 |
using the <a href="lang_attach.html">ATTACH</a> statement. The super-journal file is always |
| 358 |
located in the same directory as the main database file |
| 359 |
(the main database file is the database that is identified |
| 360 |
in the original <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or |
| 361 |
<a href="c3ref/open.html">sqlite3_open_v2()</a> call that created the <a href="c3ref/sqlite3.html">database connection</a>) |
| 362 |
with a randomized suffix. The super-journal file contains |
| 363 |
the names of all of the various attached auxiliary databases |
| 364 |
that were changed during the transaction. The multi-database |
| 365 |
transaction commits when the super-journal file is deleted. |
| 366 |
See the documentation titled |
| 367 |
<a href="atomiccommit.html">Atomic Commit In SQLite</a> for |
| 368 |
additional detail. |
| 369 |
</p> |
| 370 |
|
| 371 |
<p> |
| 372 |
Without the super-journal, the transaction commit on a multi-database |
| 373 |
transaction would be atomic for each database individually, but it |
| 374 |
would not be atomic across all databases. In other words, if the |
| 375 |
commit were interrupted in the middle by a crash or power loss, then |
| 376 |
the changes to one of the databases might complete while the changes |
| 377 |
to another database might roll back. The super-journal causes all |
| 378 |
changes in all databases to either rollback or commit together. |
| 379 |
</p> |
| 380 |
|
| 381 |
<p> |
| 382 |
The super-journal file is only created for <a href="lang_transaction.html">COMMIT</a> operations that |
| 383 |
involve multiple database files where at least two of the databases |
| 384 |
meet all of the following requirements: |
| 385 |
|
| 386 |
</p><ol> |
| 387 |
<li>The database is modified by the transaction |
| 388 |
</li><li>The <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> setting is not OFF |
| 389 |
</li><li>The <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode</a> is not OFF, MEMORY, or WAL |
| 390 |
</li></ol> |
| 391 |
|
| 392 |
<p> |
| 393 |
This means that SQLite transactions are not atomic |
| 394 |
across multiple database files on a power-loss when the database |
| 395 |
files have synchronous turned off or when they are using journal |
| 396 |
modes of OFF, MEMORY, or WAL. For synchronous OFF and for |
| 397 |
journal_modes OFF and MEMORY, database will usually corrupt if |
| 398 |
a transaction commit is interrupted by a power loss. For |
| 399 |
<a href="wal.html">WAL mode</a>, individual database files are updated atomically |
| 400 |
across a power-loss, but in the case of a multi-file transactions, |
| 401 |
some files might rollback while others roll forward after |
| 402 |
power is restored. |
| 403 |
</p> |
| 404 |
|
| 405 |
|
| 406 |
<a name="stmtjrnl"></a> |
| 407 |
|
| 408 |
<h2 id="statement_journal_files"><span>2.5. </span>Statement Journal Files</h2> |
| 409 |
|
| 410 |
<p> |
| 411 |
A statement journal file is used to rollback partial results of |
| 412 |
a single statement within a larger transaction. For example, suppose |
| 413 |
an UPDATE statement will attempt to modify 100 rows in the database. |
| 414 |
But after modifying the first 50 rows, the UPDATE hits |
| 415 |
a constraint violation which should block the entire statement. |
| 416 |
The statement journal is used to undo the first 50 row changes |
| 417 |
so that the database is restored to the state it was in at the start |
| 418 |
of the statement. |
| 419 |
</p> |
| 420 |
|
| 421 |
<p> |
| 422 |
A statement journal is only created for an UPDATE or INSERT statement |
| 423 |
that might change multiple rows of a database and which might hit a |
| 424 |
constraint or a RAISE exception within a trigger and thus need to |
| 425 |
undo partial results. |
| 426 |
If the UPDATE or INSERT is not contained within BEGIN...COMMIT and if |
| 427 |
there are no other active statements on the same database connection then |
| 428 |
no statement journal is created since the ordinary |
| 429 |
rollback journal can be used instead. |
| 430 |
The statement journal is also omitted if an alternative |
| 431 |
<a href="lang_conflict.html">conflict resolution algorithm</a> is |
| 432 |
used. For example: |
| 433 |
</p> |
| 434 |
|
| 435 |
<blockquote><pre> |
| 436 |
UPDATE OR FAIL ... |
| 437 |
UPDATE OR IGNORE ... |
| 438 |
UPDATE OR REPLACE ... |
| 439 |
UPDATE OR ROLLBACK ... |
| 440 |
INSERT OR FAIL ... |
| 441 |
INSERT OR IGNORE ... |
| 442 |
INSERT OR REPLACE ... |
| 443 |
INSERT OR ROLLBACK ... |
| 444 |
REPLACE INTO .... |
| 445 |
</pre></blockquote> |
| 446 |
|
| 447 |
<p> |
| 448 |
The statement journal is given a randomized name, not necessarily |
| 449 |
in the same directory as the main database, and is automatically |
| 450 |
deleted at the conclusion of the transaction. The size of the |
| 451 |
statement journal is proportional to the size of the change implemented |
| 452 |
by the UPDATE or INSERT statement that caused the statement journal |
| 453 |
to be created. |
| 454 |
</p> |
| 455 |
|
| 456 |
<a name="tempdb"></a> |
| 457 |
|
| 458 |
<h2 id="temp_databases"><span>2.6. </span>TEMP Databases</h2> |
| 459 |
|
| 460 |
<p>Tables created using the "CREATE TEMP TABLE" syntax are only |
| 461 |
visible to the <a href="c3ref/sqlite3.html">database connection</a> in which the "CREATE TEMP TABLE" |
| 462 |
statement is originally evaluated. These TEMP tables, together |
| 463 |
with any associated indices, triggers, and views, are collectively |
| 464 |
stored in a separate temporary database file that is created as |
| 465 |
soon as the first "CREATE TEMP TABLE" statement is seen. |
| 466 |
This separate temporary database file also has an associated |
| 467 |
rollback journal. |
| 468 |
The temporary database file used to store TEMP tables is deleted |
| 469 |
automatically when the <a href="c3ref/sqlite3.html">database connection</a> is closed |
| 470 |
using <a href="c3ref/close.html">sqlite3_close()</a>. |
| 471 |
</p> |
| 472 |
|
| 473 |
<p> |
| 474 |
The TEMP database file is very similar to auxiliary database |
| 475 |
files added using the <a href="lang_attach.html">ATTACH</a> statement, though with a few |
| 476 |
special properties. |
| 477 |
The TEMP database is always automatically deleted when the |
| 478 |
<a href="c3ref/sqlite3.html">database connection</a> is closed. |
| 479 |
The TEMP database always uses the |
| 480 |
<a href="pragma.html#pragma_synchronous">synchronous=OFF</a> and <a href="pragma.html#pragma_journal_mode">journal_mode=PERSIST</a> |
| 481 |
PRAGMA settings. |
| 482 |
And, the TEMP database cannot be used with <a href="lang_detach.html">DETACH</a> nor can |
| 483 |
another process <a href="lang_attach.html">ATTACH</a> the TEMP database. |
| 484 |
</p> |
| 485 |
|
| 486 |
<p> |
| 487 |
The temporary files associated with the TEMP database and its |
| 488 |
rollback journal are only created if the application makes use |
| 489 |
of the "CREATE TEMP TABLE" statement. |
| 490 |
</p> |
| 491 |
|
| 492 |
<a name="views"></a> |
| 493 |
|
| 494 |
<h2 id="materializations_of_views_and_subqueries"><span>2.7. </span>Materializations Of Views And Subqueries</h2> |
| 495 |
|
| 496 |
<p>Queries that contain subqueries must sometime evaluate |
| 497 |
the subqueries separately and store the results in a temporary |
| 498 |
table, then use the content of the temporary table to evaluate |
| 499 |
the outer query. |
| 500 |
We call this "materializing" the subquery. |
| 501 |
The query optimizer in SQLite attempts to avoid materializing, |
| 502 |
but sometimes it is not easily avoidable. |
| 503 |
The temporary tables created by materialization are each stored |
| 504 |
in their own separate temporary file, which is automatically |
| 505 |
deleted at the conclusion of the query. |
| 506 |
The size of these temporary tables depends on the amount of |
| 507 |
data in the materialization of the subquery, of course. |
| 508 |
</p> |
| 509 |
|
| 510 |
<p> |
| 511 |
A subquery on the right-hand side of IN operator must often |
| 512 |
be materialized. For example: |
| 513 |
</p> |
| 514 |
|
| 515 |
<blockquote><pre> |
| 516 |
SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2); |
| 517 |
</pre></blockquote> |
| 518 |
|
| 519 |
<p> |
| 520 |
In the query above, the subquery "SELECT b FROM ex2" is evaluated |
| 521 |
and its results are stored in a temporary table (actually a temporary |
| 522 |
index) that allows one to determine whether or not a value ex2.b |
| 523 |
exists using a simple binary search. Once this table is constructed, |
| 524 |
the outer query is run and for each prospective result row a check |
| 525 |
is made to see if ex1.a is contained within the temporary table. |
| 526 |
The row is output only if the check is true. |
| 527 |
</p> |
| 528 |
|
| 529 |
<p> |
| 530 |
To avoid creating the temporary table, the query might be rewritten |
| 531 |
as follows: |
| 532 |
</p> |
| 533 |
|
| 534 |
<blockquote><pre> |
| 535 |
SELECT * FROM ex1 WHERE EXISTS(SELECT 1 FROM ex2 WHERE ex2.b=ex1.a); |
| 536 |
</pre></blockquote> |
| 537 |
|
| 538 |
<p> |
| 539 |
Recent versions of SQLite (<a href="releaselog/3_5_4.html">version 3.5.4</a> 2007-12-14) and later) |
| 540 |
will do this rewrite automatically |
| 541 |
if an index exists on the column ex2.b. |
| 542 |
</p> |
| 543 |
|
| 544 |
<p> |
| 545 |
If the right-hand side of an IN operator is a list of values |
| 546 |
as in the following: |
| 547 |
</p> |
| 548 |
<blockquote><pre> |
| 549 |
SELECT * FROM ex1 WHERE a IN (1,2,3); |
| 550 |
</pre></blockquote> |
| 551 |
<p> |
| 552 |
then they are treated as a subquery that must be materialized. In other |
| 553 |
words, the previous statement acts as if it were: |
| 554 |
</p> |
| 555 |
<blockquote><pre> |
| 556 |
SELECT * FROM ex1 WHERE a IN (SELECT 1 UNION ALL |
| 557 |
SELECT 2 UNION ALL |
| 558 |
SELECT 3); |
| 559 |
</pre></blockquote> |
| 560 |
<p> |
| 561 |
A temporary index is always used to hold the values of the |
| 562 |
right-hand side of an IN operator when that right-hand side |
| 563 |
is a list of values. |
| 564 |
</p> |
| 565 |
|
| 566 |
<p> |
| 567 |
Subqueries might also need to be materialized when they appear |
| 568 |
in the FROM clause of a SELECT statement. For example: |
| 569 |
</p> |
| 570 |
|
| 571 |
<blockquote><pre> |
| 572 |
SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a; |
| 573 |
</pre></blockquote> |
| 574 |
|
| 575 |
<p> |
| 576 |
Depending on the query, SQLite might need to materialize the |
| 577 |
"(SELECT b FROM ex2)" subquery into a temporary table, then |
| 578 |
perform the join between ex1 and the temporary table. The |
| 579 |
query optimizer tries to avoid this by "flattening" the |
| 580 |
query. In the previous example the query can be flattened, |
| 581 |
and SQLite will automatically transform the query into |
| 582 |
</p> |
| 583 |
|
| 584 |
<blockquote><pre> |
| 585 |
SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a; |
| 586 |
</pre></blockquote> |
| 587 |
|
| 588 |
<p> |
| 589 |
More complex queries may or may not be able to employ query |
| 590 |
flattening to avoid the temporary table. Whether or not |
| 591 |
the query can be flattened depends on such factors as whether |
| 592 |
or not the subquery or outer query contain aggregate functions, |
| 593 |
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth. |
| 594 |
The rules for when a query can and cannot be flattened are |
| 595 |
very complex and are beyond the scope of this document. |
| 596 |
</p> |
| 597 |
|
| 598 |
<a name="transidx"></a> |
| 599 |
|
| 600 |
<h2 id="transient_indices"><span>2.8. </span>Transient Indices</h2> |
| 601 |
|
| 602 |
<p> |
| 603 |
SQLite may make use of transient indices to |
| 604 |
implement SQL language features such as: |
| 605 |
</p> |
| 606 |
|
| 607 |
<ul> |
| 608 |
<li>An ORDER BY or GROUP BY clause</li> |
| 609 |
<li>The DISTINCT keyword in an aggregate query</li> |
| 610 |
<li>Compound SELECT statements joined by UNION, EXCEPT, or INTERSECT</li> |
| 611 |
</ul> |
| 612 |
|
| 613 |
<p> |
| 614 |
Each transient index is stored in its own temporary file. |
| 615 |
The temporary file for a transient index is automatically deleted |
| 616 |
at the end of the statement that uses it. |
| 617 |
</p> |
| 618 |
|
| 619 |
<p> |
| 620 |
SQLite strives to implement ORDER BY clauses using a preexisting |
| 621 |
index. If an appropriate index already exists, SQLite will walk |
| 622 |
the index, rather than the underlying table, to extract the |
| 623 |
requested information, and thus cause the rows to come out in |
| 624 |
the desired order. But if SQLite cannot find an appropriate index |
| 625 |
it will evaluate the query and store each row in a transient index |
| 626 |
whose data is the row data and whose key is the ORDER BY terms. |
| 627 |
After the query is evaluated, SQLite goes back and walks the |
| 628 |
transient index from beginning to end in order to output the |
| 629 |
rows in the desired order. |
| 630 |
</p> |
| 631 |
|
| 632 |
<p> |
| 633 |
SQLite implements GROUP BY by ordering the output rows in the |
| 634 |
order suggested by the GROUP BY terms. Each output row is |
| 635 |
compared to the previous to see if it starts a new "group". |
| 636 |
The ordering by GROUP BY terms is done in exactly the same way |
| 637 |
as the ordering by ORDER BY terms. A preexisting index is used |
| 638 |
if possible, but if no suitable index is available, a transient |
| 639 |
index is created. |
| 640 |
</p> |
| 641 |
|
| 642 |
<p> |
| 643 |
The DISTINCT keyword on an aggregate query is implemented by |
| 644 |
creating a transient index in a temporary file and storing |
| 645 |
each result row in that index. As new result rows are computed |
| 646 |
a check is made to see if they already exist in the transient |
| 647 |
index and if they do the new result row is discarded. |
| 648 |
</p> |
| 649 |
|
| 650 |
<p> |
| 651 |
The UNION operator for compound queries is implemented by creating |
| 652 |
a transient index in a temporary file and storing the results |
| 653 |
of the left and right subquery in the transient index, discarding |
| 654 |
duplicates. After both subqueries have been evaluated, the |
| 655 |
transient index is walked from beginning to end to generate the final output. |
| 656 |
</p> |
| 657 |
|
| 658 |
<p> |
| 659 |
The EXCEPT operator for compound queries is implemented by creating |
| 660 |
a transient index in a temporary file, storing the results of the |
| 661 |
left subquery in this transient index, then removing the result |
| 662 |
from right subquery from the transient index, and finally walking |
| 663 |
the index from beginning to end to obtain the final output. |
| 664 |
</p> |
| 665 |
|
| 666 |
<p> |
| 667 |
The INTERSECT operator for compound queries is implemented by |
| 668 |
creating two separate transient indices, each in a separate |
| 669 |
temporary file. The left and right subqueries are evaluated |
| 670 |
each into a separate transient index. Then the two indices |
| 671 |
are walked together and entries that appear in both indices |
| 672 |
are output. |
| 673 |
</p> |
| 674 |
|
| 675 |
<p> |
| 676 |
Note that the UNION ALL operator for compound queries does not |
| 677 |
use transient indices by itself (though of course the right |
| 678 |
and left subqueries of the UNION ALL might use transient indices |
| 679 |
depending on how they are composed.) |
| 680 |
|
| 681 |
<a name="vacuumdb"></a> |
| 682 |
|
| 683 |
</p><h2 id="transient_database_used_by_vacuum_"><span>2.9. </span>Transient Database Used By <a href="lang_vacuum.html">VACUUM</a></h2> |
| 684 |
|
| 685 |
<p> |
| 686 |
The <a href="lang_vacuum.html">VACUUM</a> command works by creating a temporary file |
| 687 |
and then rebuilding the entire database into that temporary |
| 688 |
file. Then the content of the temporary file is copied back |
| 689 |
into the original database file and the temporary file is |
| 690 |
deleted. |
| 691 |
</p> |
| 692 |
|
| 693 |
<p> |
| 694 |
The temporary file created by the <a href="lang_vacuum.html">VACUUM</a> command exists only |
| 695 |
for the duration of the command itself. The size of the temporary |
| 696 |
file will be no larger than the original database. |
| 697 |
</p> |
| 698 |
|
| 699 |
<a name="tempstore"></a> |
| 700 |
|
| 701 |
<h1 id="the_sqlite_temp_store_compile_time_parameter_and_pragma"><span>3. </span>The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h1> |
| 702 |
|
| 703 |
<p> |
| 704 |
The temporary files associated with transaction control, namely |
| 705 |
the rollback journal, super-journal, write-ahead log (WAL) files, |
| 706 |
and shared-memory files, are always written to disk. |
| 707 |
But the other kinds of temporary files might be stored in memory |
| 708 |
only and never written to disk. |
| 709 |
Whether or not temporary files other than the rollback, |
| 710 |
super, and statement journals are written to disk or stored only in memory |
| 711 |
depends on the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter, the |
| 712 |
<a href="pragma.html#pragma_temp_store">temp_store pragma</a>, |
| 713 |
and on the size of the temporary file. |
| 714 |
</p> |
| 715 |
|
| 716 |
<p> |
| 717 |
The <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is a #define whose value is |
| 718 |
an integer between 0 and 3, inclusive. The meaning of the |
| 719 |
<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is as follows: |
| 720 |
</p> |
| 721 |
|
| 722 |
<ol type="1"> |
| 723 |
<li value="0"> |
| 724 |
Temporary files are always stored on disk regardless of the setting |
| 725 |
of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>. |
| 726 |
</li> |
| 727 |
<li value="1"> |
| 728 |
Temporary files are stored on disk by default but this can be |
| 729 |
overridden by the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>. |
| 730 |
</li> |
| 731 |
<li value="2"> |
| 732 |
Temporary files are stored in memory by default but this can be |
| 733 |
overridden by the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>. |
| 734 |
</li> |
| 735 |
<li value="3"> |
| 736 |
Temporary files are always stored in memory regardless of the setting |
| 737 |
of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>. |
| 738 |
</li> |
| 739 |
</ol> |
| 740 |
|
| 741 |
<p> |
| 742 |
The default value of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is 1, |
| 743 |
which means to store temporary files on disk but provide the option |
| 744 |
of overriding the behavior using the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>. |
| 745 |
</p> |
| 746 |
|
| 747 |
<p> |
| 748 |
The <a href="pragma.html#pragma_temp_store">temp_store pragma</a> has |
| 749 |
an integer value which also influences the decision of where to store |
| 750 |
temporary files. The values of the temp_store pragma have the |
| 751 |
following meanings: |
| 752 |
</p> |
| 753 |
|
| 754 |
<ol type="1"> |
| 755 |
<li value="0"> |
| 756 |
Use either disk or memory storage for temporary files as determined |
| 757 |
by the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter. |
| 758 |
</li> |
| 759 |
<li value="1"> |
| 760 |
If the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter specifies memory storage for |
| 761 |
temporary files, then override that decision and use disk storage instead. |
| 762 |
Otherwise follow the recommendation of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time |
| 763 |
parameter. |
| 764 |
</li> |
| 765 |
<li value="2"> |
| 766 |
If the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter specifies disk storage for |
| 767 |
temporary files, then override that decision and use memory storage instead. |
| 768 |
Otherwise follow the recommendation of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time |
| 769 |
parameter. |
| 770 |
</li> |
| 771 |
</ol> |
| 772 |
|
| 773 |
<p> |
| 774 |
The default setting for the <a href="pragma.html#pragma_temp_store">temp_store pragma</a> is 0, |
| 775 |
which means to following the recommendation of <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time |
| 776 |
parameter. |
| 777 |
</p> |
| 778 |
|
| 779 |
<p> |
| 780 |
To reiterate, the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the |
| 781 |
<a href="pragma.html#pragma_temp_store">temp_store pragma</a> only |
| 782 |
influence the temporary files other than the rollback journal |
| 783 |
and the super-journal. The rollback journal and the |
| 784 |
super-journal are always written to disk regardless of the settings of |
| 785 |
the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the |
| 786 |
<a href="pragma.html#pragma_temp_store">temp_store pragma</a>. |
| 787 |
</p> |
| 788 |
|
| 789 |
<a name="otheropt"></a> |
| 790 |
|
| 791 |
<h1 id="other_temporary_file_optimizations"><span>4. </span>Other Temporary File Optimizations</h1> |
| 792 |
|
| 793 |
<p> |
| 794 |
SQLite uses a page cache of recently read and written database |
| 795 |
pages. This page cache is used not just for the main database |
| 796 |
file but also for transient indices and tables stored in temporary |
| 797 |
files. If SQLite needs to use a temporary index or table and |
| 798 |
the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the |
| 799 |
<a href="pragma.html#pragma_temp_store">temp_store pragma</a> are |
| 800 |
set to store temporary tables and index on disk, the information |
| 801 |
is still initially stored in memory in the page cache. The |
| 802 |
temporary file is not opened and the information is not truly |
| 803 |
written to disk until the page cache is full. |
| 804 |
</p> |
| 805 |
|
| 806 |
<p> |
| 807 |
This means that for many common cases where the temporary tables |
| 808 |
and indices are small (small enough to fit into the page cache) |
| 809 |
no temporary files are created and no disk I/O occurs. Only |
| 810 |
when the temporary data becomes too large to fit in RAM does |
| 811 |
the information spill to disk. |
| 812 |
</p> |
| 813 |
|
| 814 |
<p> |
| 815 |
Each temporary table and index is given its own page cache |
| 816 |
which can store a maximum number of database pages determined |
| 817 |
by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter. |
| 818 |
(The default value is 500 pages.) |
| 819 |
The maximum number of database pages in the page cache is the |
| 820 |
same for every temporary table and index. The value cannot |
| 821 |
be changed at run-time or on a per-table or per-index basis. |
| 822 |
Each temporary file gets its own private page cache with its |
| 823 |
own SQLITE_DEFAULT_TEMP_CACHE_SIZE page limit. |
| 824 |
</p> |
| 825 |
|
| 826 |
<a name="tempdir"></a> |
| 827 |
|
| 828 |
<h1 id="temporary_file_storage_locations"><span>5. </span>Temporary File Storage Locations</h1> |
| 829 |
|
| 830 |
<p> |
| 831 |
The directory or folder in which temporary files are created is |
| 832 |
determined by the OS-specific <a href="vfs.html">VFS</a>. |
| 833 |
|
| 834 |
</p><p> |
| 835 |
On unix-like systems, directories are searched in the following order: |
| 836 |
</p><ol> |
| 837 |
<li>The directory set by <a href="pragma.html#pragma_temp_store_directory">PRAGMA temp_store_directory</a> or by the |
| 838 |
<a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global variable |
| 839 |
</li><li>The SQLITE_TMPDIR environment variable |
| 840 |
</li><li>The TMPDIR environment variable |
| 841 |
</li><li>/var/tmp |
| 842 |
</li><li>/usr/tmp |
| 843 |
</li><li>/tmp |
| 844 |
</li><li>The current working directory (".") |
| 845 |
</li></ol> |
| 846 |
The first of the above that is found to exist and have the write and |
| 847 |
execute bits set is used. The final "." fallback is important for some |
| 848 |
applications that use SQLite inside of chroot jails that do not have |
| 849 |
the standard temporary file locations available. |
| 850 |
|
| 851 |
<p> |
| 852 |
On Windows systems, folders are searched in the following order: |
| 853 |
</p><ol> |
| 854 |
<li>The folder set by <a href="pragma.html#pragma_temp_store_directory">PRAGMA temp_store_directory</a> or by the |
| 855 |
<a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global variable |
| 856 |
</li><li>The folder returned by the GetTempPath() system interface. |
| 857 |
</li></ol> |
| 858 |
SQLite itself does not pay any attention to environment variables |
| 859 |
in this case, though presumably the GetTempPath() system call does. |
| 860 |
The search algorithm is different for CYGWIN builds. Check the |
| 861 |
source code for details. |
| 862 |
<p align="center"><small><i>This page was last updated on 2025-06-12 09:10:23Z </small></i></p> |
| 863 |
|