Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add documentation on the journal_mode pragma and the new tempfiles.html document. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
85d1df25127c44b8435d459ac1425d86 |
User & Date: | drh 2008-04-21 19:14:18.000 |
Context
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) | |
2008-04-16
| ||
18:13 | Fix typo on download.html page. (check-in: 794e2cf389 user: drh tags: trunk) | |
Changes
Changes to pages/pragma.in.
︙ | ︙ | |||
252 253 254 255 256 257 258 259 260 261 262 263 264 265 | defragmentation and node repacking just as the full-blown [VACUUM] command does. And incremental vacuum may be promoted from a pragma to a separate SQL command, or perhaps some variation on the [VACUUM] command. Programmers are cautioned to not become enamored with the current syntax or functionality as it is likely to change.</p> </li> <tcl>Subsection legacy_file_format</tcl> <li><p><b>PRAGMA legacy_file_format; <br>PRAGMA legacy_file_format = <i>ON | OFF</i></b></p> <p>This pragma sets or queries the value of the legacy_file_format flag. When this flag is on, new SQLite databases are created in | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | defragmentation and node repacking just as the full-blown [VACUUM] command does. And incremental vacuum may be promoted from a pragma to a separate SQL command, or perhaps some variation on the [VACUUM] command. Programmers are cautioned to not become enamored with the current syntax or functionality as it is likely to change.</p> </li> <tcl>Subsection journal_mode</tcl> <li><p><b>PRAGMA jounal_mode; <br>PRAGMA <i>database</i>.journal_mode; <br>PRAGMA journal_mode = <i>DELETE | PERSIST | OFF</i> <br>PRAGMA <i>database</i>.journal_mode = <i>DELETE | PERSIST | OFF</i></b></p> <p>This pragma queries or sets the journal mode for databases associated with the current [database connection].</p> <p>The first two forms of this pragma query the current journaling mode. In the first form, the default journal_mode is returned. The default journaling mode is the mode used by databases added to the connection by subsequent [ATTACH] statements. The second form returns the current journaling mode for a specific database.</p> <p>The last two forms change the journaling mode. The 4th form changes the journaling mode for a specific database connection. Use "main" for the main database (the database that was opened by the original [sqlite3_open()], [sqlite3_open16()], or [sqlite3_open_v2()] interface call) and use "temp" for database that holds TEMP tables. The 3rd form changes the journaling mode on all databases and it changes the default journaling mode that will be used for new databases added by subsequent [ATTACH] commands.</p> <p>The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit. (See the documented titled <a href="atomiccommit.html"> Atomic Commit In SQLite</a> for additional detail.)</p> <p>The PERSIST journaling mode prevents the rollback journal from being deleted at the end of each transaction. Instead, the header of the journal is overwritten with zeros. This will prevent other database connections from rolling the journal back. The PERSIST journaling mode is useful as an optimization on platforms where deleting a file is much more expensive than overwriting the first block of a file with zeros.</p> <p>The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. If a crash or power failure occurs in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt.</p> </li> <tcl>Subsection legacy_file_format</tcl> <li><p><b>PRAGMA legacy_file_format; <br>PRAGMA legacy_file_format = <i>ON | OFF</i></b></p> <p>This pragma sets or queries the value of the legacy_file_format flag. When this flag is on, new SQLite databases are created in |
︙ | ︙ |
Added pages/tempfiles.in.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | <title>Temporary Files Used By SQLite</title> <h1 align="center">SQLite's Use Of Temporary Disk Files</h1> <h2>1.0 Introduction</h2> <p> On 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> <h2>2.0 Seven Kinds Of Temporary Files</h2> <p> SQLite currently uses seven distinct types of temporary files: </p> <ol> <li>Rollback journals</li> <li>Master journals</li> <li>Statement journals</li> <li>TEMP databases</li> <li>Manifestations 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> <h3>2.1 Rollback Journals</h3> <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 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> <p> The master 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 [database connection] using the [ATTACH] statement. The master 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 [sqlite3_open()], [sqlite3_open16()], or [sqlite3_open_v2()] call that created the [database connection]) with a randomized suffix. The master database 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 master journal file is deleted. 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 the changes to one of the databases might complete while the changes to another database might roll back. The master journal causes all changes in all databases to either rollback or commit together. </p> <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> A statement journal is only created for an UPDATE or INSERT statement that might change muliple 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 the first or only statement within a transaction, 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 ... INSERT OR FAIL ... INSERT OR IGNORE ... INSERT OR REPLACE ... 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> <h3>2.4 TEMP Databases</h3> <p>Tables created using the "CREATE TEMP TABLE" syntax are only visible to the [database connection] 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 [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> [PRAGMA] settings. And, the TEMP database cannot be used with [DETACH] nor can another process [ATTACH] 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> <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> 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 (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> <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 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> </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 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> 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 previous two paragraphs describe the implementation of SQLite as of version 3.5.8. There are known problems with this approach for very large results sets - result sets that are larger than the available disk cache. Future versions of SQLite will likely address this deficiency by completely reworking the sort algorithm for cases when no suitable preexisting sort index is available. The new sort algorithm will also use temporary files, but not in the same way as the current implementation, the the temporary files for the new implementation will probably not be index files. </p> <p> The DISTINCT keyword on an aggregate query is implemented by creating an 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 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 into the original database file and the temporary file is deleted. </p> <p> The temporary file created by the [VACUUM] 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> <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> The TEMP_STORE compile-time parameter is a #define whose value is an integer between 0 and 3, inclusive. The meaning of the TEMP_STORE 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 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 following meanings: </p> <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 <a href="pragma.html#pragma_temp_store">temp_store pragma</a> is 0, which means to following the recommendation of TEMP_STORE compile-time parameter. </p> <p> To reiterate, the TEMP_STORE compile-time parameter an the <a href="pragma.html#pragma_temp_store">temp_store pragma</a> only influence the temporary files other than the rollback journal and the master journal. The rollback journal and the master journal are always written to disk regardless of the settings of the TEMP_STORE compile-time parameter and the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>. </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. </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. </p> |