Query Cache

This article describes the query cache feature in Exasol.

To enhance performance when you frequently have many identical queries (low write/high read scenarios), you can use the query cache in Exasol. 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 can read the result directly out of the cache instead of executing the query.

To prevent that stale data is returned, cached queries/results are flushed if the corresponding table objects are changed.

Conditions

The cached result will be used if all the following conditions are fulfilled:

  • Query cache is enabled for the session or system-wide (see Enable query cache)

  • The query is semantically identical to a cached query (disregarding case and spaces)

  • The corresponding schema objects have not been changed since the initial query

  • Using the cached result would not be more expensive (for example, a very simple query)

The query cache will not be used in the following cases:

  • The SELECT statement contains UDF scripts, virtual schemas, scripts, or PL/SQL functions

  • The SELECT statement contains an IMPORT clause:

    SELECT x FROM (
        IMPORT INTO (x int) ....

  • The query contains VALUES BETWEEN on the top level:

    SELECT * FROM VALUES BETWEEN 1 AND 10000000 X(a);

  • The query is a prepared statement:

    SELECT x FROM T WHERE x > ?;

  • The query contains any of the following functions or literals, except in the ORDER BY clause of the top-level SELECT:

    • CURRENT_CLUSTER

    • CURRENT_DATE

    • CURRENT_SCHEMA

    • CURRENT_SESSION

    • CURRENT_STATEMENT

    • CURRENT_TIMESTAMP

    • CURRENT_USER

    • IPROC

    • LOCALTIMESTAMP

    • NPROC

    • POSIX_TIME

    • RANDOM

    • SCOPE_USER

    • SESSION_PARAMETER

    • SYS_GUID

    • SYSDATE

    • SYSTIMESTAMP

Enable query cache

To enable/disable query cache, set the QUERY_CACHE parameter using ALTER SESSION (for the current session) or ALTER SYSTEM (system-wide). The following values can be set:

  • QUERY_CACHE = 'ON': The query cache is enabled. Each query result that is expensive enough (defined internally by resource usage) is read and written into the cache.

  • QUERY_CACHE = 'OFF': The query cache is disabled.

  • QUERY_CACHE = 'READONLY': Results are read from the cache, but additional new queries will not be cached.

Exasol will in most cases be able to determine if using a cached result will be more expensive than executing the query on the database. In some rare cases, using the query cache may however result in reduced performance. If you experience performance issues, try disabling the query cache.

Usage notes

To determine if a query was returned from the cache, inspect the EXECUTION_MODE column in the system tables EXA_SQL_LAST_DAY or EXA_DBA_AUDIT_SQL. The value CACHED indicates that a cached version of the query was used.

When using Profiling, the PART_NAME column contains the value QUERY CACHE RESULT.