JBoss.orgCommunity Documentation
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 or time to live.
Set the materialized view memory preference, time to live, or updatablity.
Indicate that a virtual procedure should be cachable and set the cache entry memory preference or time to live.
/*+ cache[([pref_mem] [ttl:n] [updatable])] */ sql ...
The cache hint should appear at the beginning of the SQL. It will not have any affect on INSERT/UPDATE/DELETE statements or virtual update procedure definitions.
pref_mem - if present indicates that the cached results should prefer to remain in memory. They are not however required to be memory only.
ttl:n - if present n indicates the time to live value in milliseconds.
updatable - if present indicates that the cached results can be updated. This is currently only applicable to materialized views.
The form of the query hint must be matched exactly for the hint to have affect.
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 an informational log. As a workaround, the query plan may be checked though (see the Client Developers Guide) to see if the user command
in the plan has retained the proper hint.
Individual queries may override the use of cached results by
specifying OPTION NOCACHE on the query. 0 or more fully qualified view or procedure names may be specified to exclude using their cached results.
If no names are specified, cached results will not be used transitively.
Example 5.1. Full NOCACHE
SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE
No cached results will be used at all.
Example 5.2. Specific NOCACHE
SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE vg1, vg3
Only the vg1 and vg3 caches will be skipped, vg2 or any cached results nested under vg1 and vg3 will be used.
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.