Chapter 3. Result Set Caching
Chapter 3. Result Set Caching 3.1. User Query Cache User query result set caching will cache result sets based on an exact match of the incoming SQL string and PreparedStatement parameter values if present. Caching only applies to SELECT, set query, and stored procedure execution statements; it does not apply to SELECT INTO statements, or INSERT, UPDATE, or DELETE statements. End users or client applications explicitly state whether to use result set caching. Do this by setting the JDBC ResultSetCacheMode execution property to true (by default it is set to false). Alternatively, add a Cache Hint to the query. Note Note that if either of these mechanisms are used, DV must also have result set caching enabled (it is so by default). The most basic form of the cache hint, /*+ cache */, is sufficient to inform the engine that the results of the non-update command must be cached. The results will be cached with the default ttl and use the SQL string and the parameter value as part of the cache key. The pref_mem and ttl options of the cache hint may also be used for result set cache queries. If a cache hint is not specified, then the default time to live of the result set caching configuration will be used. Here is a more advanced example: In this example the memory preference has been enabled and the time to live is set to 60000 milliseconds (1 minute). The time-to-live for an entry is actually treated as its maximum age and the entry may be purged sooner if the maximum number of cache entries has been reached. Important Each query is re-checked for authorization using the current user's permissions, regardless of whether or not the results have been cached.
Properties info = new Properties(); info.setProperty("ResultSetCacheMode", "true"); Connection conn = DriverManager.getConnection(url, info); PreparedStatement ps = connection.prepareStatement("/*+ cache */ select col from t where col2 = ?"); ps.setInt(1, 5); ps.execute(); /*+ cache(pref_mem ttl:60000) */ select col from t