Oracle Coherence: Query Limitations

You have probably noticed by now that all the filters we have used as examples are evaluated against a single cache. This is one of the limitations of the Coherence query mechanism--it is not possible to perform an equivalent of a table join and execute the query against it.

However, while the ability to execute queries across multiple caches would come in handy occasionally, this is not too big a problem in practice. In most cases, you can perform any necessary table joins before loading the data into the cache, so you end up with all the information you need to query on in a single cache. Remember, the purpose of Coherence is to bring data closer to the application in a format that is easily consumable by the application, so transforming data from multiple tables into instances of a single aggregate is definitely something that you will be doing often.

That said, there will still be cases when you don't have all the data you need in a single cache, and you really, really need that join. In those cases the solution is to execute the query directly against the backend database and obtain a list of identifiers that you can use to retrieve objects from a cache.

Another important limitation you should be aware of is that Coherence queries only take into account objects that are already in the cache-- they will not load any data from the database into the cache automatically. Because partial results are typically not what you want, this implies that you need to preload all the data into the cache before you start executing queries against it.

Alternatively, you can choose not to use Coherence queries at all and adopt the same approach as in the previous case by querying the database directly in order to obtain identifiers for the objects in the result, and using those identifiers to look up objects in the cache. Of course, this assumes that your cache is configured to automatically load the missing objects from the database on gets, which is something you will learn how to do in Chapter 8, Implementing the Persistence Layer.