Documentation Source Text

Check-in [08b1963db5]
Login

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

Overview
Comment:Draft chagnes to the VACUUM documentation to describe the ability to VACUUM attached databases.
Timelines: family | ancestors | descendants | both | vacuum-enhancement
Files: files | file ages | folders
SHA1: 08b1963db56a31264c3e58907ec4bb5b46b740fb
User & Date: drh 2016-08-30 16:37:54
Context
2016-08-30
20:14
Alternative page header suitable for loading content into Fossil for display. Leaf check-in: 42802d730e user: drh tags: xdoc
16:37
Draft chagnes to the VACUUM documentation to describe the ability to VACUUM attached databases. Leaf check-in: 08b1963db5 user: drh tags: vacuum-enhancement
2016-08-29
20:44
Fix stray characters that managed to get into a commit of the header generator. check-in: 3cb5fe9998 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to art/syntax/bubble-generator-data.tcl.

486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
        }
  }
  qualified-table-name {
     line {optx /schema-name .} /table-name
          {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }
  vacuum-stmt {
      line VACUUM
  }
  comment-syntax {
    or
      {line -- {loop nil /anything-except-newline} 
           {or /newline /end-of-input}}
      {line /* {loop nil /anything-except-*/}
           {or */ /end-of-input}}
  }
}







|









486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
        }
  }
  qualified-table-name {
     line {optx /schema-name .} /table-name
          {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }
  vacuum-stmt {
      line VACUUM {optx /schema-name}
  }
  comment-syntax {
    or
      {line -- {loop nil /anything-except-newline} 
           {or /newline /end-of-input}}
      {line /* {loop nil /anything-except-*/}
           {or */ /end-of-input}}
  }
}

Changes to art/syntax/vacuum-stmt.gif.

cannot compute difference between binary files

Changes to pages/lang.in.

4514
4515
4516
4517
4518
4519
4520
4521

4522
4523
4524
4525
4526
4527
4528
....
4541
4542
4543
4544
4545
4546
4547
4548







4549
4550
4551
4552
4553
4554
4555
4556
4557
4558
4559
4560
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575











4576
4577
4578
4579
4580
4581
4582
##############################################################################
Section VACUUM vacuum VACUUM

RecursiveBubbleDiagram vacuum-stmt
</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 
................................................................................
     [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 forces the [SQLite query planner] to use a







|
>







 







|
>
>
>
>
>
>
>
|













<
<








<


>
>
>
>
>
>
>
>
>
>
>







4514
4515
4516
4517
4518
4519
4520
4521
4522
4523
4524
4525
4526
4527
4528
4529
....
4542
4543
4544
4545
4546
4547
4548
4549
4550
4551
4552
4553
4554
4555
4556
4557
4558
4559
4560
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570


4571
4572
4573
4574
4575
4576
4577
4578

4579
4580
4581
4582
4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
##############################################################################
Section VACUUM vacuum VACUUM

RecursiveBubbleDiagram vacuum-stmt
</tcl>

<p>
  The VACUUM command rebuilds an entire database, packing the content as
  tightly as possible. 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 
................................................................................
     [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>On all versions of SQLite through [version 3.14.1], 
the VACUUM command only worked on the main database schema.  
The optional <yyterm>schema-name</yyterm> argument was
accepted by silently ignored.  Beginning with SQLite [version 3.15.0],
any database other than TEMP can be vacuumed by providing its
<yyterm>schema-name</yyterm> as an argument.  If the argument is
omitted, the default is "main".  The schema-name argument is the
name following the AS keyword in the [ATTACH] statement, not the
name of the 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>^(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>

<h3>Restrictions</h3>

<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>^The VACUUM command will fail with an obscure error if the
[database connection] contains any [ATTACH]-ed database whose
schema-name is "vacuum_db".  This is due to the fact that the
VACUUM command [ATTACH]-es a temporary table named "vacuum_db"
as part of its processing.


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

</tcl>
<p>^The INDEXED BY phrase forces the [SQLite query planner] to use a