> ## 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.

# Result Set Caching

export const OcientDataIntelligencePlatform = "OcientAIQ™ Unified Data Platform";

The {OcientDataIntelligencePlatform} 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](/system-catalog#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.

<Info>
  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.
</Info>

## Configuring Query Caching

You can configure caching behavior for the service class (see [SERVICE CLASS](/users-groups-and-service-classes#service-class)) or for an individual query (see [USING](/general-sql-syntax#using)).

You can specify configurations for result set caching with these attributes:

| `cache_max_bytes` | Controls whether the database stores the results of any specified query in the cache. <br />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.<br />The database caches results in memory on the SQL Nodes. These results are not cached if there is insufficient memory available.                                                     |
| ----------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `cache_max_time`  | Affects 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.<br />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 SQL theme={null}
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#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 SQL theme={null}
ALTER TABLE table_name INVALIDATE CACHE [SPREAD_OVER_TIME seconds]
```

| **Parameter** | **Data Type** | **Description**                                                                                         |
| ------------- | ------------- | ------------------------------------------------------------------------------------------------------- |
| `table_name`  | string        | Name of the database table.                                                                             |
| `seconds`     | numeric       | Number 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 SQL theme={null}
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 SQL theme={null}
ALTER TABLE employees INVALIDATE CACHE SPREAD_OVER_TIME 10;
```

**Syntax**

Remove the cached result sets in the specified database.

```sql SQL theme={null}
ALTER DATABASE database_name INVALIDATE CACHE [SPREAD_OVER_TIME seconds]
```

| **Parameter**   | **Data Type** | **Description**                                                                                         |
| --------------- | ------------- | ------------------------------------------------------------------------------------------------------- |
| `database_name` | string        | Name of the database.                                                                                   |
| `seconds`       | numeric       | Number 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 SQL theme={null}
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 SQL theme={null}
ALTER DATABASE test_database INVALIDATE CACHE SPREAD_OVER_TIME 10;
```

## Related Links

[Data Definition Language (DDL) Statement Reference](/data-definition-language-ddl-statement-reference)

[Query Ocient](/query-ocient)