Documentation Source Text

Check-in [d1f8da061d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Typos and cleanup in the tempfiles.html page.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d1f8da061d51ac9cedb17d0c8756abca29c6396e
User & Date: drh 2008-04-21 23:33:02
Context
2008-04-25
02:42
Updates to the temporary-files document. Create a link to the document from the main documentation index page. Also patch the journal_mode pragma documentation. check-in: a192161bbc user: drh tags: trunk
2008-04-21
23:33
Typos and cleanup in the tempfiles.html page. check-in: d1f8da061d user: drh tags: trunk
19:14
Add documentation on the journal_mode pragma and the new tempfiles.html document. check-in: 85d1df2512 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/tempfiles.in.

116
117
118
119
120
121
122
123
124
125
126
127


128
129
130
131
132
133
134
135
136
137
138
139
...
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
...
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
...
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
...
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
...
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
...
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
...
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
...
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
...
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
...
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
...
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
...
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
...
558
559
560
561
562
563
564


565
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 omits 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 has the same effect as deleting the journal file, though
without the expense of actually removing the file from disk.  The


OFF journal mode causes SQLite to omit creating a rollback journal
in the first place.  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 tranaction that uses the OFF journal
mode, no recovery is possible and the database file will likely
go corrupt.
</p>


<h3>2.2 Master Journal Files</h3>

................................................................................
See the documentation titled
<a href="atomiccommit.html">Atomic Commit In SQLite</a> for
additional detail.
</p>

<p>
The master journal file is only created in cases where a single
[database connection] is talking two or more databases files
as a result of using [ATTACH] to connection to auxiliary databases,
and where a single transaction modifies more than one of those
database files.
Without the master 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
................................................................................

<h3>2.3 Statement Journal Files</h3>

<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.
Futher suppose that 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>
................................................................................
rollback journal.
The temporary database file used to store TEMP tables is deleted
automatically when the [database connection] is closed
using [sqlite3_close()].
</p>

<p>
The TEMP database file is very similar to auxilary database
files added using the [ATTACH] statement, though with a few
special properties.
The TEMP database is always automatically deleted when the
[database connection] 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>
................................................................................

<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>

<h3>2.5 Manifestations Of Views And Subqueries</h3>

<p>Queries that contain subqueries must sometime evalute
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 "manifesting" the subquery.
The query optimizer in SQLite attempts to avoid manifesting,
but sometimes it is not easily unavoidable.
The temporary tables created in the process 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 manifestation of the subquery, of course.
</p>

<p>
A subquery on the right-hand side of IN operator must often
be manifested.  For example:
</p>

<blockquote><pre>
SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);
</pre></blockquote>

<p>
................................................................................
<p>
Recent versions of SQLite (version 3.5.4 and later)
will do this rewrite automatically
if an index exists on the column ex2.b.
</p>

<p>
Subqueries might also need to be manifested 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 manifest 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 need 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;
</blockquote></pre>
................................................................................
The rules for when a query and an cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<h3>2.6 Transient Indices</h3>

<p>
SQLite much occasionally 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>
................................................................................
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 
requestion 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>

<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 entires 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.)
t

<h3>2.7 Transient Database Used By [VACUUM]</h3>

<p>
The [VACUUM] 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
................................................................................
file will be no larger than the original database.
</p>

<h2>3.0 The TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>
The rollback journal and master journal files are always written
to disk if they exist at all.
But the other kinds of temporary files might be stored in memory
only.  The other temporary files might never be created and written
to disk.  Whether or not temporary files other than the rollback
and master journals are written to disk or stored only in memory
depends on the TEMP_STORE compile-time parameter, the
<a href="pragma.html#pragma_temp_store">temp_store</a> [PRAGMA],
and on the size of the temporary file.
</p>

<p>
................................................................................
of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
</li>
</ol>

<p>
The default value of the TEMP_STORE compile-time parameter is 1,
which means to store temporary files on disk but provide the option
of overriding the bahavior 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 the temp_store pragma have the
................................................................................
<ol type="1">
<li value="0">
Use either disk or memory storage for temporary files as determined
by the TEMP_STORE compile-time parameter.
</li>
<li value="1">
If the TEMP_STORE compile-time parameter specifies memory storage for
temporary files, then that decision and use disk storage instead.
Otherwise follow the recommendation of the TEMP_STORE compile-time
parameter.
</li>
<li value="2">
If the TEMP_STORE compile-time parameter specifies disk storage for
temporary files, then that decision and use memory storage instead.
Otherwise follow the recommendation of the TEMP_STORE compile-time
parameter.
</li>
</ol>

<p>
The default setting for the 
................................................................................
</p>

<h2>4.0 Other Temporary File Optimizations</h2>

<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 table stored in temporary
files.  If SQLite needs to use a temporary index or table and
the TEMP_STORE 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.
................................................................................
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.


</p>







|


|
|
>
>
|



|







 







|







 







|







 







|







 







|

|



|
|
|
|



|




|







 







|








|


|







 







|







 







|







 







|








<







 







|

|
|







 







|







 







|





|







 







|







 







>
>

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
...
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
...
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
...
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
...
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
...
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
...
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428

429
430
431
432
433
434
435
...
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
...
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
...
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
...
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
...
559
560
561
562
563
564
565
566
567
568
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 forego creating a rollback journal
in the first place.  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.
</p>


<h3>2.2 Master Journal Files</h3>

................................................................................
See the documentation titled
<a href="atomiccommit.html">Atomic Commit In SQLite</a> for
additional detail.
</p>

<p>
The master journal file is only created in cases where a single
[database connection] is talking with two or more databases files
as a result of using [ATTACH] to connection to auxiliary databases,
and where a single transaction modifies more than one of those
database files.
Without the master 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
................................................................................

<h3>2.3 Statement Journal Files</h3>

<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>
................................................................................
rollback journal.
The temporary database file used to store TEMP tables is deleted
automatically when the [database connection] is closed
using [sqlite3_close()].
</p>

<p>
The TEMP database file is very similar to auxiliary database
files added using the [ATTACH] statement, though with a few
special properties.
The TEMP database is always automatically deleted when the
[database connection] 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>
................................................................................

<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>

<h3>2.5 Materializations Of Views And Subqueries</h3>

<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>
................................................................................
<p>
Recent versions of SQLite (version 3.5.4 and later)
will do this rewrite automatically
if an index exists on the column ex2.b.
</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;
</blockquote></pre>
................................................................................
The rules for when a query and an cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<h3>2.6 Transient Indices</h3>

<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>
................................................................................
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>

<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.)


<h3>2.7 Transient Database Used By [VACUUM]</h3>

<p>
The [VACUUM] 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
................................................................................
file will be no larger than the original database.
</p>

<h2>3.0 The TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>
The rollback journal and master journal 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
and master journals are written to disk or stored only in memory
depends on the TEMP_STORE compile-time parameter, the
<a href="pragma.html#pragma_temp_store">temp_store</a> [PRAGMA],
and on the size of the temporary file.
</p>

<p>
................................................................................
of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
</li>
</ol>

<p>
The default value of the TEMP_STORE 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 the temp_store pragma have the
................................................................................
<ol type="1">
<li value="0">
Use either disk or memory storage for temporary files as determined
by the TEMP_STORE compile-time parameter.
</li>
<li value="1">
If the TEMP_STORE compile-time parameter specifies memory storage for
temporary files, then override that decision and use disk storage instead.
Otherwise follow the recommendation of the TEMP_STORE compile-time
parameter.
</li>
<li value="2">
If the TEMP_STORE compile-time parameter specifies disk storage for
temporary files, then override that decision and use memory storage instead.
Otherwise follow the recommendation of the TEMP_STORE compile-time
parameter.
</li>
</ol>

<p>
The default setting for the 
................................................................................
</p>

<h2>4.0 Other Temporary File Optimizations</h2>

<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 TEMP_STORE 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.
................................................................................
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>