3

Is there any product which can be queries using JDBC (normal SQL), it sees whether all the tables in the query are in CACHED tables, and use the cache, otherwise fallback to the back-end database.

I am aware of two products: Oracle In Memory Database (IMDB) Cache, and VMware SQLFire.

I'm not familiar with none of them, so I want to know is it possible to query IMDB cache on non-cached tables, so it falls-back to underlying database?

Is there any other products which support this feature?

Avinash
  • 12,851
  • 32
  • 116
  • 186
Amir Pashazadeh
  • 7,170
  • 3
  • 39
  • 69

2 Answers2

0

Oracle In Memory Database (IMDB) Cache does support the feature that you ask about.

If the SQL statement that you use refers to IMDB cache tables, then the cache will be used. If the SQL statements that you use refers to non cache tables, then the Oracle database will be accessed.

IMDB Cache uses SQL or PLSQL to do read and/or write caching to Oracle databases. You can use JDBC [or ODBC, OCI, ODP.Net, Node.js, Python, Go, Ruby etc] to talk to either an Oracle database or the IMDB Cache.

IMDB Cache also works with object relational mapping technologies such as Hibernate [eg JPA] for data access.

IMDB Cache uses the Oracle TimesTen In-Memory Database and is now called 'Oracle Application Tier Database Cache'.

I am a product manager for Oracle TimesTen.

ScalableDBDoug
  • 31
  • 6
0

With 11g you can use the JDBC OCI Client Result Cache:

Client result cache feature enables client-side caching of SQL query result sets in client memory. In this way, OCI applications can use client memory to take advantage of the client result cache to improve response times of repetitive queries.

Note that the CACHE clause doesn't mean what the name implies:

For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • I am using JPA (Hibernate) in my project, and I want a truely transparent cache. It is not easy to add query hints in a JPAQL or HQL statement. – Amir Pashazadeh Dec 29 '11 at 05:11
  • You may want to try changing the tags to get some other people to look at this question. (Maybe Hibernate, or JPA? I'm not familiar with those technologies.) – Jon Heller Jan 08 '12 at 01:44
  • I am a Hibernate expert, and I believe it does nothing to Hibernate, except that using Hibernate it is not so easy to map an entity to different datasources. – Amir Pashazadeh Jan 21 '12 at 20:00
  • As far as I know Oracle TimesTen almost does what I want, but have anyone any experience with it? Is it possible to map some tables in TimesTen with zero rows to be cached? It is much easier in a Hibernate application to have just one data source, so it would be much better to use TimesTen just as a proxy for the real database. – Amir Pashazadeh Jan 21 '12 at 20:02
  • I don't think I can help you with those questions. Based on the low number of views, I doubt anyone else will contribute. You should probably edit this question to clarify, which will also bump it and get more attention. – Jon Heller Jan 22 '12 at 03:52