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