Table Maintenance
Open in CalíopeAnalyze, check, optimize or repair the selected tables, with the operations the server has.
Where it is: Workspace › Tools › Table Maintenance
Open Table Maintenance from the workspace sidebar. Select tables in the tree on the left (a database's checkbox selects all its tables, or tick them one by one), choose the operation at the top — ANALYZE, CHECK, CHECKSUM, OPTIMIZE or REPAIR — and click Run. Next to the button you see how many tables are selected.
The results appear below, one row for each message the server returns for each table: warnings are highlighted in orange and errors in red. On InnoDB, OPTIMIZE answers with a note: it rebuilds the table and analyzes it instead. CHECKSUM gives one number per table; the same number on two servers means the same rows. REPAIR only runs on tables whose storage engine can repair itself — MyISAM, Aria, ARCHIVE and CSV. Before you run it, a line under the modifiers says how many of the selected tables will be skipped, and each one gets a row saying why; on InnoDB the row suggests OPTIMIZE, which rebuilds the table. On the Mac, click a column header to sort the results; Clear results empties the list. While an operation runs, a progress bar and Cancel appear next to Run; tables are processed one at a time, so cancelling stops before the next one.
The tree has a filter by name in its header, just like the SQL Editor: type part of a database or table name to narrow the list. Each database loads its tables the first time you expand it, so if you filter with databases still unexpanded, the footer tells you how many are missing and offers to load them. Select all sweeps every database on the server except the server's own system databases, which you can still tick by hand, and may take a few seconds. If the server rejects the operation on a table, that table gets an error row with the server's message.
SQL Server
There are three operations: ANALYZE (UPDATE STATISTICS), CHECK (DBCC CHECKTABLE) and OPTIMIZE (ALTER INDEX ALL … REBUILD). There is no REPAIR —it needs the database in single-user mode and can lose data— and no CHECKSUM. OPTIMIZE locks the table while it rebuilds: rebuilding online exists only in the Enterprise edition, and not with spatial indexes.
PostgreSQL
There are two operations: ANALYZE and OPTIMIZE, which runs VACUUM and then REINDEX TABLE CONCURRENTLY, so it gives two rows per table; neither stops the table. There is no CHECK —the core has nothing like it—, no REPAIR, no CHECKSUM and no modifiers. VACUUM FULL, the only one that gives disk space back to the system, is left out on purpose: it locks the whole table while it rewrites it. On a server older than 12, OPTIMIZE stops at the VACUUM and the index row says why.
ClickHouse
With ClickHouse there are two operations: CHECK, which returns one row for each part of the table, and OPTIMIZE, which merges its parts. ANALYZE, REPAIR and CHECKSUM don't exist in the engine and don't appear. With a single table ticked, Partition appears, to optimize one partition instead of the whole table.
Oracle
With Oracle there is a single operation: ANALYZE, which gathers the statistics of the table and its indexes with DBMS_STATS.GATHER_TABLE_STATS. The result row says how many rows the statistics count and when they were gathered. There is no CHECK, REPAIR, CHECKSUM or OPTIMIZE: Oracle has no such per-table operations, and they are not offered. Without the privilege on a table in another schema, the row says the statistics could not be gathered, with the server's message below. Cancel also interrupts the current table: the server cuts its DBMS_STATS short (ORA-01013) and that table gets no row in the list.
With a read-only environment tag, this tool isn’t offered. With one that confirms writes, ANALYZE, OPTIMIZE and REPAIR ask once per batch; CHECK and CHECKSUM, which read, don’t ask.
Keywords: ANALYZE, CHECK, OPTIMIZE, REPAIR, maintenance, CHECKSUM