Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.ocient.com/llms.txt

Use this file to discover all available pages before exploring further.

The allows query result sets to be cached on the server and returned quickly on subsequent queries. The service class under which the query executes manages result set caching. As with other service class attributes, you can override this by using keywords on the query.

​
When to Use Result Set Caching

In general, result set caching is most advantageous for queries that require high processing to produce a small result. For example, a query that uses aggregate functions, such as finding the average over many rows, would be a good candidate for result caching. To help evaluate whether to use result set caching, you can examine your query metadata, such as storage cost and processing time. To view these metrics and to see which are stored in cache, query the sys.completed_queries table.

​
Query Matching Behavior

Result set caching uses the normalized text of the query (i.e., with comments removed, whitespace reduced, and case normalized) to determine whether two queries match. If they match, the system uses the cached result of a previous query.
Query matching requires strict text matching. Queries cannot use cached results if they have different values for arguments such as time ranges or other filters.

​
Configuring Query Caching

You can configure caching behavior for the service class (see SERVICE CLASS) or for an individual query (see USING). You can specify configurations for result set caching with these attributes:
cache_max_bytesControls whether the database stores the results of any specified query in the cache.
All queries executed using a service class specifying this attribute have their results stored in the cache if the size of the result is smaller than this value. You can determine the size of a result set in bytes by querying the bytes_returned field of the completed_queries virtual table. The default value is 0, and thus by default, the database does not cache any results.
The database caches results in memory on the SQL Nodes. These results are not cached if there is insufficient memory available.
cache_max_timeAffects whether a specified query uses results from the cache rather than executing the query. If there is a cached result with the same query text, executed less than cache_max_time seconds ago, the database uses that cached result. The default value is 0, and thus by default, the database does not return any cached results.
The cache considers all SQL Nodes, and if a potential cached result is only available on a different SQL Node, the database redirects the query to that node. If you specify the force attribute on the connection, which disables load balancing and redirection, the database considers only cached results on the current node.
When a specified query uses multiple service classes, the system uses the most restrictive configuration values. For example, if a query uses service_class_a with cache_max_bytes set to 512 and service_class_b with cache_max_bytes set to 0, the system does not cache the results. This is because 0 is the most restrictive value, and caching is disabled regardless of the result set size. You can override the values for cache_max_bytes and cache_max_time at query time by specifying them in the query itself. Query Override Example
SQL
SELECT COUNT(*) FROM TRADES USING CACHE_MAX_BYTES=32, CACHE_MAX_TIME=60;
This example states that the query result is cached if it is less than or equal to 32 bytes, and the cache is used if the value stored in the cache is less than 60 seconds old. It is very important to note that attributes specified on the query cannot increase the values from the service classes, only reduce them. Both cache_max_bytes and cache_max_time can be explicitly not specified in a service class by using the value -1. -1 is effectively treated as 0 if not overridden. unspecified, or -1 values can always be overriden by other service classes or on the query. A typical use case is to:
  • Specify cache_max_bytes as 0 for service classes used by ad hoc queries, where results should never be cached and consume memory.
  • Specify cache_max_bytes and cache_max_time as -1 on service classes used by repeated production queries. As mentioned, the system treats these as 0 if not overridden.
  • Explicitly override cache_max_bytes on repeated production queries whose results should be cached.
  • Explicitly override cache_max_time on repeated production queries whose results can be retrieved from the cache.
Note that because the default schema used for a query can affect the results of the query, the system treats two queries with identical text but making use of different schemas differently. Finally, the contents of the cache can be examined using the result_cache virtual table.

​
Max Elapsed Time For Caching

The system cancels queries when they exceed the max_elapsed_time service class value. An additional service class value, however, allows queries to continue beyond max_elapsed_time so that their results can be stored in the cache. The service class (or SQL) can specify max_elapsed_time_for_caching, which should be a value larger than max_elapsed_time. This value indicates the total time the query can continue executing such that its results are stored in the cache. max_elapsed_time_for_caching and max_elapsed_time are both measured in seconds. When a query with max_elapsed_time_for_caching exceeds max_elapsed_time, a warning indicating Query exceeded time limit, but will continue to run and be cached (Limited to 200 second(s) for caching from query) is returned to the JDBC driver, where max_elapsed_time_for_caching was set to 200 seconds in this example. max_elapsed_time_for_caching is only supported on queries executed using the JDBC driver.

​
Manage the Result Set Cache

You can store and retrieve query results from the cache depending on the settings of the cache_max_bytes and cache_max_time values, either using the SQL statement or the service class specified for the query execution. To view all cached queries and their service classes, query the sys.result_cache system catalog table. You can invalidate the result set cache using these DDL statements. Use these statements when you perform table updates, such as an INSERT INTO SQL statement operation or the execution of one of the loading and transformation functions. Syntax Remove the cached result sets for all queries that reference the specified table. You can invalidate the cached result sets for only tables where you have the INSERT privilege. If a cached query references multiple tables, you only need the INSERT privilege on any one of those tables.
SQL
ALTER TABLE table_name INVALIDATE CACHE [SPREAD_OVER_TIME seconds]
ParameterData TypeDescription
table_namestringName of the database table.
secondsnumericNumber of seconds that specifies the interval at which to invalidate a section of the result set cache.
Example This example immediately invalidates the cache and removes the cached result sets for all queries that reference the employees table.
SQL
ALTER TABLE employees INVALIDATE CACHE;
This example invalidates the cache entries for queries that reference the employees table for a fraction of the cache every second. Specify the fraction to be 1/10 by using the spread_over_time parameter. The database removes 1/10 of the cached result set immediately, then the next 1/10 of the cached result set the subsequent second, and so on. Invalidation causes the re-execution of queries to delay. Spreading the invalidation over time prevents spikes of expensive query executions.
SQL
ALTER TABLE employees INVALIDATE CACHE SPREAD_OVER_TIME 10;
Syntax Remove the cached result sets in the specified database.
SQL
ALTER DATABASE database_name INVALIDATE CACHE [SPREAD_OVER_TIME seconds]
ParameterData TypeDescription
database_namestringName of the database.
secondsnumericNumber of seconds that specifies the interval at which to invalidate a section of the result set cache.
Example This example immediately invalidates the cache and removes the cached result sets in the test_database database.
SQL
ALTER DATABASE test_database INVALIDATE CACHE;
This example invalidates the cache entries in the test_database database for a fraction of the cache every second. Specify the fraction to be 1/10 by using the spread_over_time parameter. The database removes 1/10 of the cached result set immediately, then the next 1/10 of the cached result set the subsequent second, and so on. Invalidation causes the re-execution of queries to be delayed. Spreading the invalidation over time prevents spikes of expensive query executions.
SQL
ALTER DATABASE test_database INVALIDATE CACHE SPREAD_OVER_TIME 10;
Data Definition Language (DDL) Statement Reference Query Ocient
Last modified on May 21, 2026