"Caching the results of database queries can dramatically improve script execution time and minimize the load on the database server", Yuli Vasiliev writes. According to Yuli, this technique is effective if the data you are dealing with is static. This is because many data requests to a remote database may be eventually satisfied from the local cache, thus avoiding the need to make a connection to the database, execute the query, and fetch the results.

"Determining the best caching strategy for may be a challenge" Yuli states. For example, a time triggered caching approach, which is commonly used in caching systems and assumes that the cache is regenerated each time an expiry timestamp is reached, may not be a satisfactory solution for the application where using up-to-date database result sets is crucial. In this case, you need to employ a mechanism that will notify your application each time a change occurs to the database data, so the application can reconcile cached, out-of-date data with the database. "This is where Database Change Notification – a new Oracle Database 10g Release 2 feature – can come in handy", Yuli explains.

Here’s a run-down of the procedure:

• Getting started with 'Database Change Notification'
• Using UTL_HTTP to send notifications to the client
• Creating the notification handler
• Creating a registration for change notification
• Adding a table to an existing registration
• Adding a table to an existing registration

"If your PHP application interacts with Oracle Database 10g Release 2, you can use the Database Change Notification feature that allows your application to receive notifications in response to DML changes on the objects associated with the queries issued," Yuli writes. Given this feature, you don’t necessarily need to update the cache in your application in a specific period of time. Instead, it can be done if the result set of a registered query has changed.

More Info