JBoss.orgCommunity Documentation
A cache hint can be used to:
Indicate that a user query is eligible for result set caching.
Set the result set query cache entry memory preference or time to live.
Set the materialized view memory preference, time to live, or updatablity.
/*+ cache[([pref_mem] [ttl:n] [updatable])] */
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.
The most basic form of the cache hint, /*+ cache */, is sufficient to inform the engine that the results of the non-update command should be cached.
Example 5.1. PreparedStatement ResultSet Caching
...
PreparedStatement ps = connection.prepareStatement("/*+ cache */ select col from t where col2 = ?");
ps.setInt(1, 5);
ps.execute();
...While no options are specified with the cache hint, it still informs the engine to use ResultSet caching.
The pref_mem and ttl options may also be used for ResultSet cache queries, however updatable only has an effect on materialized view tables.
Example 5.2. Advanced ResultSet Caching
/*+ cache(pref_mem ttl:60000 */ select col from t
In this example the memory preference has been enabled and the time to live is set to 60000 milliseconds or 1 minute. The ttl for an entry is actually treated as it's maximum age and the entry may be purged sooner if the maximum number of cache entries has been reached.
See the ResultSet Caching Chapter for more.
The cache hint, when used in the context of an internal materialized view transformation query, provides the ability to fine tune the materializated table. The hint is not used for materialization targeted at an external source. See the Materialized View Chapter for more on materialized views.
The pref_mem option also applies to internal materialized views. Internal table index pages already have a memory preference, so the perf_mem option indicates that the data pages should prefer memory as well.
When the ttl is specified in the cache hint, a full refresh of the materialized view will be triggered automatically after the specified time interval.
The refresh is equivalent to CALL SYS.refreshMatView('view name', false), but performed asynchronously so that user queries do not block on the load.
The automatic ttl refresh is not intended for complex loading scenarios, as nested materialized views will be used by the refresh query.
The automatic ttl refresh is performed lazily, that is it is only trigger by using the table after the ttl has expired. For infrequently used tables with long load times, this means that data may be used well past the intended ttl.
When the updatable option is specified, the materialized view may be targeted by the system function refreshMatViewRow.
The refreshMatViewRow function updates a single row of an internal materialized with the supplied key value.
The refresh query does use nested caches, so this refresh method should be used with caution.
When the updatable option is not specified, accessing the materialized view table is more efficient because modifications do not need to be considered. Therefore, only specify the updatable option if row based incremental updates are needed. Even when performing row updates, full snapshot refreshes may be needed to ensure consistency.
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.