Documentation Source Text

Check-in [91efaa0fc0]
Login

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

Overview
Comment:Fixes and testability improvements to lang_vacuum.html.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 91efaa0fc0168383d50e311b810aec154a8d4057
User & Date: dan 2010-09-24 17:58:36
Context
2010-09-24
18:59
Document the effect of the "REINDEX;" statement. check-in: 6405a81c7b user: dan tags: trunk
17:58
Fixes and testability improvements to lang_vacuum.html. check-in: 91efaa0fc0 user: dan tags: trunk
07:59
Add a definition for interpreting the result of an expression as a boolean value (e.g. for WHERE or HAVING clauses) to lang_expr.html. Link to this from lang_update.html, lang_delete.html and lang_select.html instead of repeating the definition on each page. check-in: 36c1765653 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

  3114   3114   <tcl>
  3115   3115   ##############################################################################
  3116   3116   Section VACUUM vacuum VACUUM
  3117   3117   
  3118   3118   BubbleDiagram vacuum-stmt 1
  3119   3119   </tcl>
  3120   3120   
  3121         -<p>^When an object (table, index, trigger, or view) is dropped from the 
  3122         -database, it leaves behind empty space. 
  3123         -^This empty space will be reused the next time new information is
  3124         -added to the database.  But in the meantime, the database file might
  3125         -be larger than strictly necessary.  Also, frequent inserts, updates,
  3126         -and deletes can cause the information in the database to become
  3127         -fragmented - scattered out all across the database file rather
  3128         -than clustered together in one place.</p>
  3129         -
  3130         -<p>^The VACUUM command cleans
  3131         -the main database by copying its contents to a temporary database file and 
  3132         -reloading the original database file from the copy.  This eliminates 
  3133         -free pages,  aligns table data to be contiguous, and otherwise cleans 
  3134         -up the database file structure.</p>
  3135         -
  3136         -<p>^The VACUUM command may change the 
  3137         -[ROWID | ROWIDs] of entries in tables that do
  3138         -not have an explicit [INTEGER PRIMARY KEY].</p>
  3139         -
  3140         -<p>^VACUUM only works on the main database.
  3141         -It is not possible to VACUUM an attached database file.</p>
  3142         -
  3143         -<p>^The VACUUM command will fail if there is an active transaction.
  3144         -^The VACUUM command is a no-op for in-memory databases.</p>
  3145         -
  3146         -<p>^(As of SQLite version 3.1, an alternative to using the VACUUM command
  3147         -is auto-vacuum mode, enabled using the 
  3148         -[auto_vacuum] pragma.)^  ^When [auto_vacuum] is enabled for a database, 
  3149         -large deletes cause
  3150         -the size of the database file to shrink.  However, [auto_vacuum]
  3151         -also causes excess fragmentation of the database file.  ^And [auto_vacuum]
  3152         -does not compact partially filled pages of the database as VACUUM
  3153         -does.</p>
  3154         -
  3155         -<p>^The [page_size] and/or [auto_vacuum] mode of a database can be changed
  3156         -by invoking the [page_size pragma] and/or [auto_vacuum pragma] and then
  3157         -immediately VACUUMing the database. ^Except, the page size cannot be
  3158         -changed when [write-ahead log] mode is in use.</p>
         3121  +<p>
         3122  +  The VACUUM command rebuilds the entire database. There are several 
         3123  +  reasons an application might do this:
         3124  +
         3125  +<ul>
         3126  +  <li> <p> ^Unless SQLite is running in "auto_vacuum=FULL" mode, when a large
         3127  +     amount of data is deleted from the database file it leaves behind empty
         3128  +     space, or "free" database pages. This means the database file might
         3129  +     be larger than strictly necessary. ^Running VACUUM to rebuild the 
         3130  +     database reclaims this space and reduces the size of the database file.
         3131  +
         3132  +  <li> <p> ^Frequent inserts, updates, and deletes can cause the database file
         3133  +     to become fragmented - where data for a single table or index is scattered 
         3134  +     around the database file. ^Running VACUUM ensures that each table and
         3135  +     index is largely stored contiguously within the database file. ^In some
         3136  +     cases, VACUUM may also reduce the number of partially filled pages in
         3137  +     the database, reducing the size of the database file further.
         3138  +
         3139  +  <li> <p> ^Normally, the database [page_size] and whether or not the database
         3140  +     supports [auto_vacuum] must be configured before the database file is
         3141  +     actually created. ^However, when not in [write-ahead log] mode, the 
         3142  +     [page_size] and/or [auto_vacuum] properties of an existing database may be
         3143  +     changed by using the [page_size pragma|page_size]  and/or 
         3144  +     [auto_vacuum|pragma auto_vacuum] pragmas and then immediately VACUUMing
         3145  +     the database. ^When in [write-ahead log] mode, only the [auto_vacuum]
         3146  +     support property can be changed using VACUUM.
         3147  +</ul>
         3148  +
         3149  +<p>^(VACUUM only works on the main database. It is not possible to VACUUM an
         3150  +attached database file.)^
         3151  +
         3152  +<p>^The VACUUM command works by copying the contents of the database into
         3153  +a temporary database file and then overwriting the original with the 
         3154  +contents of the temporary file. ^When overwriting the original, a rollback
         3155  +journal or [write-ahead log] WAL file is used just as it would be for any
         3156  +other database transaction. ^This means that when VACUUMing a database, 
         3157  +as much as twice the size of the original database file is required in free
         3158  +disk space.
         3159  +
         3160  +<p>^The VACUUM command may change the [ROWID | ROWIDs] of entries in any
         3161  +tables that do not have an explicit [INTEGER PRIMARY KEY].
         3162  +</p>
         3163  +
         3164  +<p>^A VACUUM will fail if there is an open transaction, or if there are one or
         3165  +more active SQL statements when it is run.
         3166  +
         3167  +<p>^(As of SQLite version 3.1, an alternative to using the VACUUM command to
         3168  +reclaim space after data has been deleted is auto-vacuum mode, enabled using
         3169  +the [auto_vacuum] pragma.)^ ^When [auto_vacuum] is enabled for a database
         3170  +free pages may be reclaimed after deleting data, causing the file to shrink,
         3171  +without rebuilding the entire database using VACUUM.  However, using
         3172  +[auto_vacuum] can lead to extra database file fragmentation.  And [auto_vacuum]
         3173  +does not compact partially filled pages of the database as VACUUM does.
         3174  +
         3175  +</p>
         3176  +
  3159   3177   
  3160   3178   <tcl>
  3161   3179   ##############################################################################
  3162   3180   Section {INDEXED BY} indexedby {{INDEXED BY} {NOT INDEXED}}
  3163   3181   
  3164   3182   </tcl>
  3165   3183   <p>^The INDEXED BY phrase is a SQL extension found only in SQLite which can