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 press 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 four operations: ANALYZE (UPDATE STATISTICS), CHECK (DBCC CHECKTABLE), OPTIMIZE (ALTER INDEX ALL … REBUILD) and CHECKSUM (CHECKSUM_AGG(BINARY_CHECKSUM(*)), over the whole table). The checksum ignores text, ntext, image and xml columns and those of a type such as geometry, and the result names them next to it: two tables that differ only there give the same checksum. A table on a partition scheme offers to rebuild a single partition. There is no REPAIR: it needs the database in single-user mode and can lose data. OPTIMIZE locks the table while it rebuilds it: online rebuilds exist only in Enterprise edition, and not with spatial indexes.
PostgreSQL
There are three operations: ANALYZE, OPTIMIZE, which runs VACUUM and then REINDEX TABLE CONCURRENTLY, so it gives two rows per table, and CHECK, with the amcheck extension: bt_index_check on each B-tree index and, from 14, verify_heapam on the table. Without the extension, CHECK gives a note with CREATE EXTENSION amcheck. None of them stops the table. There is no REPAIR or CHECKSUM. VACUUM FULL, the only one that gives 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 three operations: CHECK, which returns one row for each part of the table; OPTIMIZE, which merges its parts; and ANALYZE, which calculates the column statistics the optimizer uses to order filters: ADD STATISTICS with tdigest and uniq on number and date columns, uniq on text columns, and MATERIALIZE STATISTICS, which waits until it finishes. An Array or a Map takes no statistics, and a table with no column that accepts them says so. 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 are three operations. ANALYZE gathers the statistics of the table and its indexes with DBMS_STATS.GATHER_TABLE_STATS, and the result row says how many rows the statistics count and when they were gathered; without privileges on a table in another schema, it says they could not be gathered and the server's message goes below. CHECK is ANALYZE TABLE … VALIDATE STRUCTURE CASCADE ONLINE: it checks the table and its indexes without blocking writes. On a partitioned table it runs CASCADE FAST, which says whether there is damage without detailing it, because the complete form needs an INVALID_ROWS table in your schema. OPTIMIZE is ALTER TABLE … SHRINK SPACE CASCADE: it compacts the table and its indexes online and gives back the free space. If the table had no ROW MOVEMENT, Calíope turns it on for the operation and turns it off again. If the table does not accept SHRINK (ORA-10631 or ORA-10635: a function-based index, an ON COMMIT materialized view on top, a tablespace without ASSM), Calíope rewrites it with MOVE ONLINE and the row says so. On a partitioned table you can pick a partition (MODIFY PARTITION … SHRINK SPACE CASCADE). There is no REPAIR or CHECKSUM: Oracle has no such per-table operations. Cancel also interrupts the table in progress: the server stops its statement (ORA-01013) and that table gets no row in the list.
TiDB
With TiDB there are two operations: ANALYZE and CHECK, which runs ADMIN CHECK TABLE —it compares the indexes with the rows— one table at a time. If it finds nothing, the row says the table checked clean; if it finds something, the server's message goes there, naming the index. There's no REPAIR, CHECKSUM or OPTIMIZE: TiDB doesn't have them —its storage compacts on its own— and they aren't offered.
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.
MySQLMariaDBAurora
With a single table ticked, OPTIMIZE lets you choose one of its partitions. Calíope rebuilds it with REBUILD PARTITION and analyzes it with ANALYZE PARTITION, because on InnoDB OPTIMIZE PARTITION would rebuild the whole table. That operation goes to the binlog even if you tick the modifier that avoids it: ALTER TABLE does not accept it.
Keywords: ANALYZE, CHECK, OPTIMIZE, REPAIR, maintenance, CHECKSUM