This article describes some of the mechanisms that work in the background to optimize performance in Exasol.
Exasol is optimized for high performance with several mechanisms that work automatically and do not require user intervention. These are some of the more important optimization features that happen “under the hood”:
Optimizer statistics gathering
The Exasol optimizer works cost based, using statistics about object attributes such as the size and number of table rows. These statistics are automatically gathered after each DML statement without any administrative effort.
Index creation, maintenance, and deletion
Indexes that are required for JOIN operations are automatically created and subsequently maintained upon DML that modifies indexed tables. If indexes are not used for more than five weeks, they are automatically dropped.
For more information, see Indexes.
Table reorganization
A DELETE operation will initially only mark rows as deleted within a table. When more than 25% of the rows are marked as deleted, the table is automatically reorganized and the indexes on that table are rebuilt.
Query cache
The query cache stores a SELECT query together with its result if certain conditions are fulfilled. If the same query is sent again, the database reads the result directly out of the cache instead of executing the query. This enhances performance when you frequently have many identical queries (low write/high read scenarios).
You can disable/enable the query cache using ALTER SESSION (for the current session) or ALTER SYSTEM (system-wide).
