Oracle Cache Dependency
While working with databases in a multiuser environment, updates are very frequent in the databse. When cache is involved, you need to keep cached items synchronized with the database. One possible solution is that, on every update database itself notifies the cache and cache expires the out of synch items. Oracle Dependency is provided by NCache for this purpose. Item expires if result of the command (based on command text) changes. Oracle Dependency is only available for Oracle database 10g release 2 or later.
Following is the step-by-step guide for using OracleCacheDependency.
Enabling Database Notification:
Grant CHANGE NOTIFICATION privilege to the user to create a notification registration. Following statement grants the CHANGE NOTIFICATION privilege to the user.
[SQL]
"grant change notification to scott"
Using Dependency:
-
Create a Java project in any Java environment.
-
Add reference of NCClient from installationFolder../NCache/java/lib/
-
Add the following import statement in your project:
import com.alachisoft.ncache.web.caching.*;
import com.alachisoft.ncache.runtime.dependencies.*;
-
Declare a variable "_cache" in the code and initialize it with "oracleDependencyCache", and clear it if this cache was used previously
Cache _cache = NCache.initializeCache("oracleDependencyCache");
_cache.clear();
-
Create a String "connectionString" containing information to connect with Oracle database e.g.
String connectionString = "User Id=scott;Password=tiger;Data Source=oracle" ;
-
Create a dependency and add it to the cache.
CacheDependency orclSync = new OracleCacheDependency(connectionString, "SELECT CustomerID FROM Customers WHERE CustomerID = 006");
_cache.insert("Customer:David:006", "my Item" , orclSync, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);
-
Now update the key "6" of the Database table using the following Oracle command.--[SQL]
UPDATE Customers SET Customer Name = 'Modified Customer' WHERE CustomerID =6
-
This invalidates cacheKey " Dependant-CustomerID6 " and is removed by NCache.
Note: In Oracle 10g, database change notifications are only object based. This means that change notifications will be fired if any row is modified in object. Therefore we have to check rowID to confirm if the row changed is the one for which the event is registered. RowID's cannot be retrieved unless explicitly included in query. Therefore, customer has to include rowID in query that he/she is registering with OracleDependecy, otherwise change notification will be fired if any row is modified in table.
When rowID is included in query like: select rowID, productID, productname, unitprice from Products where ProductID = 220; NCache will save rowID of rows for which change notification is registered. When it receives any change notification, NCache will compare the rowIDs to determine whether the row changed is the one for which the rowID is registered. Otherwise NCache will have no way to check this and items for which change notification is registered will be removed if any row in table changes.
In Oracle 11g both object based and query based (default) notifications are provided. In query based notifications; change of the modified row will be notified only if the change notification is registered for it.
See Also