Debian Codegraph @

www/tempfiles.html

raw
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