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 Unified Diffs Show Whitespace Changes Patch

Changes to pages/lang.in.

3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158


















3159
3160
3161
3162
3163
3164
3165
<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>^When an object (table, index, trigger, or view) is dropped from the 
database, it leaves behind empty space. 
^This empty space will be reused the next time new information is
added to the database.  But in the meantime, the database file might
be larger than strictly necessary.  Also, frequent inserts, updates,
and deletes can cause the information in the database to become
fragmented - scattered out all across the database file rather
than clustered together in one place.</p>

<p>^The VACUUM command cleans
the main database by copying its contents to a temporary database file and 
reloading the original database file from the copy.  This eliminates 
free pages,  aligns table data to be contiguous, and otherwise cleans 
up the database file structure.</p>

<p>^The VACUUM command may change the 
[ROWID | ROWIDs] of entries in tables that do
not have an explicit [INTEGER PRIMARY KEY].</p>

<p>^VACUUM only works on the main database.
It is not possible to VACUUM an attached database file.</p>

<p>^The VACUUM command will fail if there is an active transaction.
^The VACUUM command is a no-op for in-memory databases.</p>

<p>^(As of SQLite version 3.1, an alternative to using the VACUUM command
is auto-vacuum mode, enabled using the 
[auto_vacuum] pragma.)^  ^When [auto_vacuum] is enabled for a database, 
large deletes cause
the size of the database file to shrink.  However, [auto_vacuum]
also causes excess fragmentation of the database file.  ^And [auto_vacuum]
does not compact partially filled pages of the database as VACUUM
does.</p>

<p>^The [page_size] and/or [auto_vacuum] mode of a database can be changed
by invoking the [page_size pragma] and/or [auto_vacuum pragma] and then
immediately VACUUMing the database. ^Except, the page size cannot be
changed when [write-ahead log] mode is in use.</p>



















<tcl>
##############################################################################
Section {INDEXED BY} indexedby {{INDEXED BY} {NOT INDEXED}}

</tcl>
<p>^The INDEXED BY phrase is a SQL extension found only in SQLite which can







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
3180
3181
3182
3183
<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>
  The VACUUM command rebuilds the entire database. There are several 
  reasons an application might do this:

<ul>
  <li> <p> ^Unless SQLite is running in "auto_vacuum=FULL" mode, when a large
     amount of data is deleted from the database file it leaves behind empty
     space, or "free" database pages. This means the database file might
     be larger than strictly necessary. ^Running VACUUM to rebuild the 
     database reclaims this space and reduces the size of the database file.

  <li> <p> ^Frequent inserts, updates, and deletes can cause the database file
     to become fragmented - where data for a single table or index is scattered 
     around the database file. ^Running VACUUM ensures that each table and
     index is largely stored contiguously within the database file. ^In some
     cases, VACUUM may also reduce the number of partially filled pages in
     the database, reducing the size of the database file further.

  <li> <p> ^Normally, the database [page_size] and whether or not the database
     supports [auto_vacuum] must be configured before the database file is
     actually created. ^However, when not in [write-ahead log] mode, the 
     [page_size] and/or [auto_vacuum] properties of an existing database may be
     changed by using the [page_size pragma|page_size]  and/or 
     [auto_vacuum|pragma auto_vacuum] pragmas and then immediately VACUUMing
     the database. ^When in [write-ahead log] mode, only the [auto_vacuum]
     support property can be changed using VACUUM.
</ul>

<p>^(VACUUM only works on the main database. It is not possible to VACUUM an
attached database file.)^

<p>^The VACUUM command works by copying the contents of the database into
a temporary database file and then overwriting the original with the 
contents of the temporary file. ^When overwriting the original, a rollback
journal or [write-ahead log] WAL file is used just as it would be for any
other database transaction. ^This means that when VACUUMing a database, 
as much as twice the size of the original database file is required in free
disk space.

<p>^The VACUUM command may change the [ROWID | ROWIDs] of entries in any
tables that do not have an explicit [INTEGER PRIMARY KEY].
</p>

<p>^A VACUUM will fail if there is an open transaction, or if there are one or
more active SQL statements when it is run.

<p>^(As of SQLite version 3.1, an alternative to using the VACUUM command to
reclaim space after data has been deleted is auto-vacuum mode, enabled using
the [auto_vacuum] pragma.)^ ^When [auto_vacuum] is enabled for a database
free pages may be reclaimed after deleting data, causing the file to shrink,
without rebuilding the entire database using VACUUM.  However, using
[auto_vacuum] can lead to extra database file fragmentation.  And [auto_vacuum]
does not compact partially filled pages of the database as VACUUM does.

</p>


<tcl>
##############################################################################
Section {INDEXED BY} indexedby {{INDEXED BY} {NOT INDEXED}}

</tcl>
<p>^The INDEXED BY phrase is a SQL extension found only in SQLite which can