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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/tempfiles.in.

   116    116   not deleted until exclusive access mode is exited.</p>
   117    117   
   118    118   <p>
   119    119   Rollback journal creation and deletion is also changed by the
   120    120   <a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>.
   121    121   The default journaling mode is DELETE, which is the default behavior
   122    122   of deleting the rollback journal file at the end of each transaction,
   123         -as described above.  The PERSIST journal mode omits the deletion of
          123  +as described above.  The PERSIST journal mode foregoes the deletion of
   124    124   the journal file and instead overwrites the rollback journal header
   125    125   with zeros, which prevents other processes from rolling back the
   126         -journal and has the same effect as deleting the journal file, though
   127         -without the expense of actually removing the file from disk.  The
   128         -OFF journal mode causes SQLite to omit creating a rollback journal
          126  +journal and thus has the same effect as deleting the journal file, though
          127  +without the expense of actually removing the file from disk.  In other
          128  +words, journal mode PERSIST exhibits the same behavior as is seen
          129  +in EXCLUSIVE locking mode. The
          130  +OFF journal mode causes SQLite to forego creating a rollback journal
   129    131   in the first place.  The OFF journal mode disables the atomic
   130    132   commit and rollback capabilities of SQLite.  The ROLLBACK command
   131    133   is not available when OFF journal mode is set.  And if a crash or power
   132         -loss occurs in the middle of a tranaction that uses the OFF journal
          134  +loss occurs in the middle of a transaction that uses the OFF journal
   133    135   mode, no recovery is possible and the database file will likely
   134    136   go corrupt.
   135    137   </p>
   136    138   
   137    139   
   138    140   <h3>2.2 Master Journal Files</h3>
   139    141   
................................................................................
   153    155   See the documentation titled
   154    156   <a href="atomiccommit.html">Atomic Commit In SQLite</a> for
   155    157   additional detail.
   156    158   </p>
   157    159   
   158    160   <p>
   159    161   The master journal file is only created in cases where a single
   160         -[database connection] is talking two or more databases files
          162  +[database connection] is talking with two or more databases files
   161    163   as a result of using [ATTACH] to connection to auxiliary databases,
   162    164   and where a single transaction modifies more than one of those
   163    165   database files.
   164    166   Without the master journal, the transaction commit on a multi-database
   165    167   transaction would be atomic for each database individually, but it
   166    168   would not be atomic across all databases.  In other words, if the
   167    169   commit were interrupted in the middle by a crash or power loss, then
................................................................................
   172    174   
   173    175   <h3>2.3 Statement Journal Files</h3>
   174    176   
   175    177   <p>
   176    178   A statement journal file is used to rollback partial results of
   177    179   a single statement within a larger transaction.  For example, suppose
   178    180   an UPDATE statement will attempt to modify 100 rows in the database.
   179         -Futher suppose that after modifying the first 50 rows, the UPDATE hits
          181  +But after modifying the first 50 rows, the UPDATE hits
   180    182   a constraint violation which should block the entire statement.
   181    183   The statement journal is used to undo the first 50 row changes
   182    184   so that the database is restored to the state it was in at the start
   183    185   of the statement.
   184    186   </p>
   185    187   
   186    188   <p>
................................................................................
   227    229   rollback journal.
   228    230   The temporary database file used to store TEMP tables is deleted
   229    231   automatically when the [database connection] is closed
   230    232   using [sqlite3_close()].
   231    233   </p>
   232    234   
   233    235   <p>
   234         -The TEMP database file is very similar to auxilary database
          236  +The TEMP database file is very similar to auxiliary database
   235    237   files added using the [ATTACH] statement, though with a few
   236    238   special properties.
   237    239   The TEMP database is always automatically deleted when the
   238    240   [database connection] is closed.
   239    241   The TEMP database always uses the
   240    242   <a href="pragma.html#pragma_synchronous">synchronous=OFF</a> and
   241    243   <a href="pragma.html#pragma_journal_mode">journal_mode=PERSIST</a>
................................................................................
   246    248   
   247    249   <p>
   248    250   The temporary files associated with the TEMP database and its
   249    251   rollback journal are only created if the application makes use
   250    252   of the "CREATE TEMP TABLE" statement.
   251    253   </p>
   252    254   
   253         -<h3>2.5 Manifestations Of Views And Subqueries</h3>
          255  +<h3>2.5 Materializations Of Views And Subqueries</h3>
   254    256   
   255         -<p>Queries that contain subqueries must sometime evalute
          257  +<p>Queries that contain subqueries must sometime evaluate
   256    258   the subqueries separately and store the results in a temporary
   257    259   table, then use the content of the temporary table to evaluate
   258    260   the outer query.
   259         -We call this "manifesting" the subquery.
   260         -The query optimizer in SQLite attempts to avoid manifesting,
   261         -but sometimes it is not easily unavoidable.
   262         -The temporary tables created in the process are each stored
          261  +We call this "materializing" the subquery.
          262  +The query optimizer in SQLite attempts to avoid materializing,
          263  +but sometimes it is not easily avoidable.
          264  +The temporary tables created by materialization are each stored
   263    265   in their own separate temporary file, which is automatically
   264    266   deleted at the conclusion of the query.
   265    267   The size of these temporary tables depends on the amount of
   266         -data in the manifestation of the subquery, of course.
          268  +data in the materialization of the subquery, of course.
   267    269   </p>
   268    270   
   269    271   <p>
   270    272   A subquery on the right-hand side of IN operator must often
   271         -be manifested.  For example:
          273  +be materialized.  For example:
   272    274   </p>
   273    275   
   274    276   <blockquote><pre>
   275    277   SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);
   276    278   </pre></blockquote>
   277    279   
   278    280   <p>
................................................................................
   297    299   <p>
   298    300   Recent versions of SQLite (version 3.5.4 and later)
   299    301   will do this rewrite automatically
   300    302   if an index exists on the column ex2.b.
   301    303   </p>
   302    304   
   303    305   <p>
   304         -Subqueries might also need to be manifested when they appear
          306  +Subqueries might also need to be materialized when they appear
   305    307   in the FROM clause of a SELECT statement.  For example:
   306    308   </p>
   307    309   
   308    310   <blockquote><pre>
   309    311   SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
   310    312   </pre></blockquote>
   311    313   
   312    314   <p>
   313         -Depending on the query, SQLite might need to manifest the 
          315  +Depending on the query, SQLite might need to materialize the 
   314    316   "(SELECT b FROM ex2)" subquery into a temporary table, then
   315    317   perform the join between ex1 and the temporary table.  The
   316         -query optimizer tries to avoid this need by "flattening" the
          318  +query optimizer tries to avoid this by "flattening" the
   317    319   query.  In the previous example the query can be flattened,
   318    320   and SQLite will automatically transform the query into
   319    321   </p>
   320    322   
   321    323   <blockquote><pre>
   322    324   SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;
   323    325   </blockquote></pre>
................................................................................
   331    333   The rules for when a query and an cannot be flattened are
   332    334   very complex and are beyond the scope of this document.
   333    335   </p>
   334    336   
   335    337   <h3>2.6 Transient Indices</h3>
   336    338   
   337    339   <p>
   338         -SQLite much occasionally make use of transient indices to
          340  +SQLite may make use of transient indices to
   339    341   implement SQL language features such as:
   340    342   </p>
   341    343   
   342    344   <ul>
   343    345   <li>An ORDER BY or GROUP BY clause</li>
   344    346   <li>The DISTINCT keyword in an aggregate query</li>
   345    347   <li>Compound SELECT statements joined by UNION, EXCEPT, or INTERSECT</li>
................................................................................
   351    353   at the end of the statement that uses it.
   352    354   </p>
   353    355   
   354    356   <p>
   355    357   SQLite strives to implement ORDER BY clauses using a preexisting
   356    358   index.  If an appropriate index already exists, SQLite will walk
   357    359   the index, rather than the underlying table, to extract the 
   358         -requestion information, and thus cause the rows to come out in
          360  +requested information, and thus cause the rows to come out in
   359    361   the desired order.  But if SQLite cannot find an appropriate index
   360    362   it will evaluate the query and store each row in a transient index
   361    363   whose data is the row data and whose key is the ORDER BY terms.
   362    364   After the query is evaluated, SQLite goes back and walks the
   363    365   transient index from beginning to end in order to output the
   364    366   rows in the desired order.
   365    367   </p>
................................................................................
   411    413   </p>
   412    414   
   413    415   <p>
   414    416   The INTERSECT operator for compound queries is implemented by
   415    417   creating two separate transient indices, each in a separate
   416    418   temporary file.  The left and right subqueries are evaluated
   417    419   each into a separate transient index.  Then the two indices
   418         -are walked together and entires that appear in both indices
          420  +are walked together and entries that appear in both indices
   419    421   are output.
   420    422   </p>
   421    423   
   422    424   <p>
   423    425   Note that the UNION ALL operator for compound queries does not
   424    426   use transient indices by itself (though of course the right
   425    427   and left subqueries of the UNION ALL might use transient indices
   426    428   depending on how they are composed.)
   427         -t
   428    429   
   429    430   <h3>2.7 Transient Database Used By [VACUUM]</h3>
   430    431   
   431    432   <p>
   432    433   The [VACUUM] command works by creating a temporary file
   433    434   and then rebuilding the entire database into that temporary
   434    435   file.  Then the content of the temporary file is copied back
................................................................................
   442    443   file will be no larger than the original database.
   443    444   </p>
   444    445   
   445    446   <h2>3.0 The TEMP_STORE Compile-Time Parameter and Pragma</h2>
   446    447   
   447    448   <p>
   448    449   The rollback journal and master journal files are always written
   449         -to disk if they exist at all.
          450  +to disk.
   450    451   But the other kinds of temporary files might be stored in memory
   451         -only.  The other temporary files might never be created and written
   452         -to disk.  Whether or not temporary files other than the rollback
          452  +only and never written to disk.
          453  +Whether or not temporary files other than the rollback
   453    454   and master journals are written to disk or stored only in memory
   454    455   depends on the TEMP_STORE compile-time parameter, the
   455    456   <a href="pragma.html#pragma_temp_store">temp_store</a> [PRAGMA],
   456    457   and on the size of the temporary file.
   457    458   </p>
   458    459   
   459    460   <p>
................................................................................
   480    481   of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
   481    482   </li>
   482    483   </ol>
   483    484   
   484    485   <p>
   485    486   The default value of the TEMP_STORE compile-time parameter is 1,
   486    487   which means to store temporary files on disk but provide the option
   487         -of overriding the bahavior using the
          488  +of overriding the behavior using the
   488    489   <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
   489    490   </p>
   490    491   
   491    492   <p>
   492    493   The <a href="pragma.html#pragma_temp_store">temp_store pragma</a> has
   493    494   an integer value which also influences the decision of where to store
   494    495   temporary files.  The values of the the temp_store pragma have the
................................................................................
   498    499   <ol type="1">
   499    500   <li value="0">
   500    501   Use either disk or memory storage for temporary files as determined
   501    502   by the TEMP_STORE compile-time parameter.
   502    503   </li>
   503    504   <li value="1">
   504    505   If the TEMP_STORE compile-time parameter specifies memory storage for
   505         -temporary files, then that decision and use disk storage instead.
          506  +temporary files, then override that decision and use disk storage instead.
   506    507   Otherwise follow the recommendation of the TEMP_STORE compile-time
   507    508   parameter.
   508    509   </li>
   509    510   <li value="2">
   510    511   If the TEMP_STORE compile-time parameter specifies disk storage for
   511         -temporary files, then that decision and use memory storage instead.
          512  +temporary files, then override that decision and use memory storage instead.
   512    513   Otherwise follow the recommendation of the TEMP_STORE compile-time
   513    514   parameter.
   514    515   </li>
   515    516   </ol>
   516    517   
   517    518   <p>
   518    519   The default setting for the 
................................................................................
   532    533   </p>
   533    534   
   534    535   <h2>4.0 Other Temporary File Optimizations</h2>
   535    536   
   536    537   <p>
   537    538   SQLite uses a page cache of recently read and written database
   538    539   pages.  This page cache is used not just for the main database
   539         -file but also for transient indices and table stored in temporary
          540  +file but also for transient indices and tables stored in temporary
   540    541   files.  If SQLite needs to use a temporary index or table and
   541    542   the TEMP_STORE compile-time parameter and the
   542    543   <a href="pragma.html#pragma_temp_store">temp_store pragma</a> are
   543    544   set to store temporary tables and index on disk, the information
   544    545   is still initially stored in memory in the page cache.  The 
   545    546   temporary file is not opened and the information is not truly
   546    547   written to disk until the page cache is full.
................................................................................
   558    559   Each temporary table and index is given its own page cache
   559    560   which can store a maximum number of database pages determined
   560    561   by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter.
   561    562   (The default value is 500 pages.)
   562    563   The maximum number of database pages in the page cache is the
   563    564   same for every temporary table and index.  The value cannot
   564    565   be changed at run-time or on a per-table or per-index basis.
          566  +Each temporary file gets its own private page cache with its
          567  +own SQLITE_DEFAULT_TEMP_CACHE_SIZE page limit.
   565    568   </p>