- SQLite - DISTINCT Keyword
- SQLite - HAVING Clause
- SQLite - GROUP By Clause
- SQLite - ORDER By Clause
- SQLite - LIMIT Clause
- SQLite - GLOB Clause
- SQLite - LIKE Clause
- SQLite - DELETE Query
- SQLite - UPDATE Query
- SQLite - AND & OR Clauses
- SQLite - WHERE Clause
- SQLite - Expressions
- SQLite - Operators
- SQLite - SELECT Query
- SQLite - INSERT Query
- SQLite - DROP Table
- SQLite - CREATE Table
- SQLite - DETACH Database
- SQLite - ATTACH Database
- SQLite - CREATE Database
- SQLite - Data Type
- SQLite - Syntax
- SQLite - Commands
- SQLite - Installation
- SQLite - Overview
- SQLite - Home
Advanced SQLite
- SQLite - Useful Functions
- SQLite - Date & Time
- SQLite - VACUUM
- SQLite - EXPLAIN
- SQLite - Injection
- SQLite - AUTOINCREMENT
- SQLite - Subqueries
- SQLite - Transactions
- SQLite - Views
- SQLite - TRUNCATE Command
- SQLite - ALTER Command
- SQLite - INDEXED By Clause
- SQLite - Indexes
- SQLite - Triggers
- SQLite - ALIAS Syntax
- SQLite - NULL Values
- SQLite - UNIONS Clause
- SQLite - JOINS
- SQLite - Constraints
- SQLite - PRAGMA
SQLite Interfaces
SQLite Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
SQLite - VACUUM
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 epminates free pages, apgns table data to be contiguous, and otherwise cleans up the database file structure.
VACUUM command may change the ROWID of entries in tables that do not have an exppcit INTEGER PRIMARY KEY. The VACUUM command only works on the main database. It is not possible to VACUUM an attached database file.
VACUUM command will fail if there is an active transaction. VACUUM command is a no-op for in-memory databases. As the VACUUM command rebuilds the database file from scratch, VACUUM can also be used to modify many database-specific configuration parameters.
Manual VACUUM
Following is a simple syntax to issue a VACUUM command for the whole database from command prompt −
$sqpte3 database_name "VACUUM;"
You can run VACUUM from SQLite prompt as well as follows −
sqpte> VACUUM;
You can also run VACUUM on a particular table as follows −
sqpte> VACUUM table_name;
Auto-VACCUM
SQLite Auto-VACUUM does not do the same as VACUUM rather it only moves free pages to the end of the database thereby reducing the database size. By doing so it can significantly fragment the database while VACUUM ensures defragmentation. Hence, Auto-VACUUM just keeps the database small.
You can enable/disable SQLite auto-vacuuming by the following pragmas running at SQLite prompt −
sqpte> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum sqpte> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum sqpte> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum
You can run the following command from the command prompt to check the auto-vacuum setting −
$sqpte3 database_name "PRAGMA auto_vacuum;"Advertisements