|
SQL As Understood By SQLite
VACUUM
vacuum-stmt:
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.
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.
The VACUUM command may change the ROWIDs of entries in tables that do not have an explicit INTEGER PRIMARY KEY.
VACUUM only works on the main database. It is not possible to VACUUM an attached database file.
The VACUUM command will fail if there is an active transaction. The VACUUM command is a no-op for in-memory databases.
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.
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.
*** DRAFT ***