Result Set Caching

the {{ocienthyperscaledatawarehouse}} 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 system catalog docid 2zcc9xuscejvt5v ihgy6 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 users, groups, and service classes docid\ hcnnsmzcvpai1kqlhtzud ) or for an individual query (see general sql syntax docid\ qcf0x9ao4a56x id39pkr ) you can specify configurations for r esult set caching with these attributes 174,305 false unhandled content type false unhandled content type unhandled content type false unhandled content type 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 y ou 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 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 s pecify cache max bytes as 0 for service classes used by ad hoc queries, where results should never be cached and consume memory s pecify 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 e xplicitly override cache max bytes on repeated production queries whose results should be cached e xplicitly 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 system catalog docid 2zcc9xuscejvt5v ihgy6 system catalog table y ou 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 alter table table name invalidate cache \[spread over time seconds] false true 126false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type example this example immediately invalidates the cache and removes the cached result sets for all queries that reference the employees table 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 alter table employees invalidate cache spread over time 10; syntax remove the cached result sets in the specified database alter database database name invalidate cache spread over time seconds] false true 148false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type example this example immediately invalidates the cache and removes the cached result sets in the test database database 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 alter database test database invalidate cache spread over time 10; related links data definition language (ddl) statement reference docid 2wcd8nmbi5m9fctufiqtm query ocient docid 4ycq1d8tkfmlsacorynf6