OpenWorld07: JDBC Client Result Set Cache

This is the kind of thing that makes going to OpenWorld worth it. I just got out of a vary informative session about client result set caching in the 11g JDBC (OCI) driver. Now ResultSet caching might not sound that exciting (Toplink can do it, Hibernate + OSCache can do it), but this is built right into the JDBC driver. Plus, it uses the databases Database Change Notification technology to get notified when its cache is out of date.

I see all kinds of useful cases for this. Especially for tables that are mostly read-only, but they do change at times and it's not acceptable for the application to be too far out of synch with the database. This is a hard problem to solve with just Java because you're basically constantly refreshing the cache and the benefit of caching is greatly reduced. You can build you're own processes to check for changes (thus eliminating the need to be constantly refreshing) but that can take a lot of development, and could potentially result in inconsistency issues.

Here's an example (after a little configuration):

select /*+ result_cache */ * from table1 where...

This instructs the driver to check it's local cache for a result set from the same SQL and bind parameters.

The only downside is that this is currently only implemented in the OCI driver, which is less than desirable. I asked and they said there are plans to bring this to the thin driver, but he couldn't commit to when.