Cache Hint
A query cache hint can be used to:
Indicate that a user query is eligible for result set caching and set the cache entry memory preference, time to live, etc;
Set the cached view memory preference, time to live, or updatability;
Indicate that a virtual procedure should be cachable and set the cache entry memory preference, time to live, etc.:
/*+ cache[([pref_mem] [ttl:n] [updatable] [scope:session|user|vdb])]*/ sql ...
The cache hint should appear at the beginning of the SQL. It can appear as any one of the leading comments, and will not have any effect on
INSERT
/UPDATE
/DELETE
statements orINSTEAD OF TRIGGERS
.
The parameters in square brackets are optional, and, if present, they mean the following:
Parameter | Description |
---|---|
| Indicates that the cached results should preferably remain in memory. The results may still be paged out based upon memory pressure. |
| Indicates the time to live value in milliseconds. The default value for result set caching is the default expiration for the corresponding |
| Indicates that the cached results can be updated. Defaults to |
| There are three different cache scopes:
For cached queries, the presence of the scope overrides the computed scope. Cached views, on the other hand, default to the vdb scope. For cached views, explicitly setting the session or user scopes will result in a non-replicated session scoped cached view |
Example
In this example, the cached result of the query will preferably be stored in memory for 1 minute:
/*+ cache(pref_mem ttl:60000) */ SELECT COUNT(*) FROM (CALL "s3_no_path1.listFiles"(
"pathAndPattern" => '/xxx/*'
)) AS a ;;
Limitations
The form of the query hint must be matched exactly for the hint to have effect. For a user query, if the hint is not specified correctly, e.g. /*+ cach(pref_mem) */
, it will not be used by the engine nor will there be any information in the log.
OPTION NOCACHE
Individual queries may override the use of cached results by specifying OPTION NOCACHE
on the query. You can specify 0 or more fully qualified view or procedure names to exclude using their cached results. If no names are specified, cached results will not be used transitively.
Full NOCACHE
In this case, no results will be used at all:
SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE
Specific NOCACHE
In this example, only the vg1
and vg3
caches will be skipped, vg2
or any cached results nested under vg1
and vg3
will be used:
SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE vg1, vg3
OPTION NOCACHE
may be specified in procedure or view definitions. In that way, transformations can specify to always use real-time data obtained directly from sources.