DOWNLOAD Oracle Database XE Zend Core for Oracle

TAGS php, opensource, xe, All

Improve PHP Performance by Caching Database Results

Learn how to use Database Change Notification to keep cached data up to date.

Published June 2006

As is well known, caching the results of database queries can dramatically improve script execution time and minimize the load on the database server. This technique can be especially effective if the data you are dealing with is quite 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.

While caching database result sets is generally a good idea when the database you are using does not reside on the same machine as the Web server, determining the best caching strategy for your situation may be a challenge. 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 the application is interested in caching, so that 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 very handy.

Getting Started With Database Change Notification

Using the database change notification feature is quite straightforward: You create a notification handler – a PL/SQL stored procedure or a client side OCI callback function – to be executed on a notification. Then, you register a query on a database object or objects for which you want to receive change notifications, so that the notification handler is invoked each time a transaction changes any of these objects and commits. Normally, a notification handler sends the name of the modified table, type of change that occurred, and optionally rowids of changed rows to the client side listener so that the client application can do the appropriate processing in response.

To see the database change notification feature in action, consider the following example. Suppose that your PHP application accesses orders stored in the OE.ORDERS table as well as orders’ line items stored in OE.ORDER_ITEMS. Taking into account the fact that the information on the placed orders is changed rarely, you might want your application to cache query result sets on both the ORDERS and ORDER_ITEMS tables. To avoid the risk of accessing out of date data, you might use database change notification that provides a convenient way of allowing your application to be notified of changes to the data stored in the above tables.

Before you can register the queries on the ORDERS and ORDER_ITEMS tables to receive notifications in response to DML or DDL changes on these tables, you must first grant the CHANGE NOTIFICATION system privilege as well as the EXECUTE ON DBMS_CHANGENOTIFICATION privilege to the OE user. This can be done by issuing the following commands from a SQL command-line tool, such as SQL*Plus.

CONNECT / AS SYSDBA;
GRANT CHANGE NOTIFICATION TO oe;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO oe;

Make sure that the init.ora parameter job_queue_processes is set to a non-zero value to receive PL/SQL notifications. Alternatively, you might use the ALTER SYSTEM command as follows:

ALTER SYSTEM SET "job_queue_processes"=2;

Then, connecting as OE/OE, you can create a notification handler. But first you have to create the database objects that will be used by the notification handler. For example, you might need to create one or more database tables in which the notification handler will record changes to registered tables. In the following example, you create the nfresults table to record the following information: the date and time the change occurred, the name of the modified table, and a message indicating whether the notification handler has successfully managed to send a notification message to the client.

CONNECT oe/oe;

CREATE TABLE nfresults (
 operdate DATE, 
 tblname VARCHAR2(60), 
 rslt_msg VARCHAR2(100)
);

In a real world situation, you might want to create more tables to record such things as notification events and rowids of changed rows, but for our purposes here, nfresults will do fine.

Using UTL_HTTP to Send Notifications to the Client

You might also want to create one or more PL/SQL stored procedures that will be invoked from within the notification handler, thus achieving a more maintainable and flexible solution. For example, you might want to create a procedure that will implement a mechanism for sending notification messages to the client. Listing 1 shows PL/SQL procedure sendNotification that uses the UTL_HTTP package to send change notifications to the client application.

Listing 1. Using UTL_HTTP to send notifications to the client

CREATE OR REPLACE PROCEDURE sendNotification(url IN VARCHAR2, 
tblname IN VARCHAR2, order_id IN VARCHAR2) IS
 req   UTL_HTTP.REQ;
 resp  UTL_HTTP.RESP;
 err_msg VARCHAR2(100);
 tbl VARCHAR(60);
BEGIN
    tbl:=SUBSTR(tblname, INSTR(tblname, '.', 1, 1)+1, 60);
    BEGIN
     req := UTL_HTTP.BEGIN_REQUEST(url||order_id||'&'||'table='||tbl);
     resp := UTL_HTTP.GET_RESPONSE(req);
     INSERT INTO nfresults VALUES(SYSDATE, tblname, resp.reason_phrase);
     UTL_HTTP.END_RESPONSE(resp);
    EXCEPTION WHEN OTHERS THEN
     err_msg := SUBSTR(SQLERRM, 1, 100);
     INSERT INTO nfresults VALUES(SYSDATE, tblname, err_msg);
    END;
 COMMIT;
END;
/

As Listing 1 shows, sendNotification sends a notification message to the client in the form of a HTTP request issued by the UTL_HTTP.BEGIN_REQUEST function. The URL contains the order_id of a changed row in the ORDERS table. Then, it uses UTL_HTTP.GET_RESPONSE to obtain response information from the client. Actually, sendNotification is not interested in processing the whole response returned from the client. Instead, it obtains only a short message stored in the reason_phrase field of the RESP record, describing the status code.

Creating the Notification Handler

Now you can create a notification handler that will send change notifications to the client with the help of the sendNotification procedure discussed above. Consider PL/SQL procedure orders_nf_callback shown in Listing 2.

Listing 2. Notification handler to process notifications about changes to the OE.ORDERS table

CREATE OR REPLACE PROCEDURE orders_nf_callback (ntfnds IN SYS.CHNF$_DESC) IS
 tblname VARCHAR2(60);
 numtables NUMBER;
 event_type NUMBER;
 row_id VARCHAR2(20);
 numrows NUMBER;
 ord_id VARCHAR2(12);
 url VARCHAR2(256) := 'http://webserverhost/phpcache/dropResults.php?order_no=';
BEGIN
 event_type := ntfnds.event_type;
 numtables := ntfnds.numtables;
 IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN
  FOR i IN 1..numtables LOOP
   tblname := ntfnds.table_desc_array(i).table_name;
   IF (bitand(ntfnds.table_desc_array(i).opflags, 
DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
    numrows := ntfnds.table_desc_array(i).numrows;
   ELSE
    numrows :=0;
   END IF;
   IF (tblname = 'OE.ORDERS') THEN
    FOR j IN 1..numrows LOOP
     row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
     SELECT order_id INTO ord_id FROM orders WHERE rowid = row_id;
     sendNotification(url, tblname, ord_id); 
    END LOOP;
   END IF;
  END LOOP;
 END IF;
 COMMIT;
END;
/

As Listing 2 shows, the notification handler takes the SYS.CHNF$_DESC object as the parameter and then uses its attributes to obtain the details of the change. In this example, the notification handler will process only those notifications that are published by the database in response to DML or DDL changes on the registered objects – that is, only if the notification type is EVENT_OBJCHANGE – and ignore notifications about the other database events, such as instance startup or instance shutdown. As of the above version, the handler can process change notifications issued for each of the affected rows in the OE.ORDERS table. In the Adding a Table To an Existing Registration section later in this article, you’ll add several lines of code to the handler so that it can process notifications issued for modified rows in the OE.ORDER_ITEMS table.

Creating a Registration For Change Notification

Once you have created a notification handler, you then have to create a query registration for it. For this example, during registration you have to execute any query on the OE.ORDER table and specify orders_nf_callback as the notification handler. You also need to specify the QOS_ROWIDS option in the DBMS_CHANGE_NOTIFICATION package in order to enable the ROWID level granularity in the notification messages. Listing 3 shows a PL/SQL block creating a query registration for the orders_nf_callback notification handler.

Listing 3. Creating a query registration for the notification handler

DECLARE
 REGDS SYS.CHNF$_REG_INFO;
 regid NUMBER;
 ord_id NUMBER;
 qosflags NUMBER;
BEGIN
 qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + 
DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
 REGDS := SYS.CHNF$_REG_INFO ('orders_nf_callback', qosflags, 0,0,0);
 regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (REGDS);
 SELECT order_id INTO ord_id FROM orders WHERE ROWNUM<2;
 DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/

In this example, a registration is created on the ORDERS table with orders_nf_callback as the notification handler. Now, if you modify the ORDERS table with DML or DDL statements and commit the transaction, the orders_nf_callback function will be automatically invoked. For example, you might issue the following UPDATE statements against the ORDERS table and commit the transaction:

UPDATE ORDERS SET order_mode = 'direct' WHERE order_id=2421;
UPDATE ORDERS SET order_mode = 'direct' WHERE order_id=2422;
COMMIT;

To make sure that a notification has been published by the database in response to the above transaction, you might check the nfresults table:

SELECT TO_CHAR(operdate, 'dd-mon-yy hh:mi:ss') operdate, 
tblname, rslt_msg FROM nfresults;

The result should look like this:

OPERDATE              TBLNAME     RSLT_MSG
--------------------- ----------- ---------
02-mar-06 04:31:28    OE.ORDERS   Not Found
02-mar-06 04:31:29    OE.ORDERS   Not Found

From the above result it should be obvious that the orders_nf_callback has worked but the client side script was not found. This is expected behavior in this case since you haven’t created a dropResults.php script specified in the URL yet. For a discussion of the dropResults.php script, see section Building the Client later in this article.

Adding a Table to an Existing Registration

The preceding section shows how you can use the change notification service to make the database issue a notification when a change occurs to the registered object – the ORDERS table as of the above example. From a performance standpoint, however, your client application probably will be more interested in caching query result sets on the ORDER_ITEMS table rather than on the ORDERS table itself, because each time it accesses an order it has to retrieve only one row from the ORDERS table but several rows from the ORDER_ITEMS table. And in a real-world situation, a purchase order may contain tens or even hundreds line items.

Since you already have a registration for a query on the ORDERS table, you don’t have to create another registration to register a query on, say, the ORDER_ITEMS table. Instead, you might make use of the existing registration. To do this, you first need to retrieve the ID of the existing registration. This can be done by issuing the following query:

SELECT regid, table_name FROM user_change_notification_regs;

The result might look like the following:

REGID TABLE_NAME
----- --------------
  241 OE.ORDERS

Once you have obtained the registration ID, you can add a new object to the registration using the DBMS_CHANGE_NOTIFICATION.ENABLE_REG function as follows:

DECLARE
  ord_id NUMBER;
BEGIN
  DBMS_CHANGE_NOTIFICATION.ENABLE_REG(241);
  SELECT order_id INTO ord_id FROM order_items WHERE ROWNUM < 2;
  DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/

That is it. From now on, the database will generate a notification in response to any change not only to ORDERS but also to ORDER_ITEMS, invoking the orders_nf_callback procedure to process notifications. Hence, the next step is to edit orders_nf_callback so that it can process notifications generated as a result of DML operations on the ORDER_ITEMS table. Before proceeding to recreate the orders_nf_callback procedure, however, you need to create the following table type, which will be referred to in the updated procedure:

CREATE TYPE rdesc_tab AS TABLE OF SYS.CHNF$_RDESC;

Then, turning back to Listing 2, after the following lines:

   IF (tblname = 'OE.ORDERS') THEN
    FOR j IN 1..numrows LOOP
     row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
     SELECT order_id INTO ord_id FROM orders WHERE rowid = row_id;
     sendNotification(url, tblname, ord_id); 
    END LOOP;
   END IF;

Insert the following code:

   IF (tblname = 'OE.ORDER_ITEMS') THEN
     FOR rec IN (SELECT DISTINCT(o.order_id) o_id FROM 
TABLE(CAST(ntfnds.table_desc_array(i).row_desc_array AS rdesc_tab)) t,
orders o, order_items d WHERE t.row_id = d.rowid AND d.order_id=o.order_id)
     LOOP
      sendNotification(url, tblname, rec.o_id); 
     END LOOP;
   END IF;

Once you have recreated the orders_nf_callback, you might want to test whether it works properly. For that, you might issue the following UPDATE statements against the ORDER_ITEMS table and commit the transaction:

UPDATE ORDER_ITEMS SET quantity = 160 WHERE order_id=2421 AND line_item_id=1;
UPDATE ORDER_ITEMS SET quantity = 160 WHERE order_id=2421 AND line_item_id=2;
COMMIT;

Then, check the nfresults table as follows:

SELECT TO_CHAR(operdate, 'dd-mon-yy hh:mi:ss') operdate, 
rslt_msg FROM nfresults WHERE tblname = 'OE.ORDER_ITEMS';

The output might look like this:

OPERDATE            RSLT_MSG
------------------- --------------
03-mar-06 12:32:27  Not Found

You might be wondering why only one row has been inserted into the nfresults table – after all, you updated two rows in the ORDER_ITEMS table. The fact is that both updated rows have the same order_id – that is, they belong to the same order. Here, we assume that the client application will select all the line items of an order with a single statement and so it won’t be interested in knowing exactly which line items of a single order have changed. Instead, the client will be interested in knowing the ID of the order in which at least one line item has been modified, deleted, or inserted.

Building the Client

Now that you have created a registration on both the ORDERS and ORDER_ITEMS tables, it’s time to look at how a client application accessing orders and their line items stored in these tables can utilize change notification. For this purpose, you might build a PHP application that will cache query result sets on the above tables, taking appropriate actions in response to notifications about changes to these tables, received from the database server. A simple approach might be to use the PEAR::Cache_Lite package allowing you to have a reliable mechanism for keeping cached data up to date. In particular, you might use the Cache_Lite_Function class – part of the PEAR::Cache_Lite package – that enables you to cache function calls.

For example, you might create a function that performs the following tasks: establishes a connection to the database, executes a select statement against it, fetches the retrieved results, and finally returns the results as an array. Then, with the help of the call method of a Cache_Lite_Function instance, you might cache result arrays returned by the function so that they can be read from the local cache, rather than from the back-end database, thus significantly improving performance of the application. Then, when receiving a notification about a change to the cached data, you delete the out of date data from the cache using the drop method of a Cache_Lite_Function instance.

Turning back to our sample, you might want to create two functions by which your application will interact with the database: The first one will query the ORDERS table and return the order with a specified ID, while the other one will query the ORDER_ITEMS table and return the line items of that order. Listing 4 shows the getOrderFields.php script containing the getOrderFields function that takes the ID of an order and returns an associative array containing some fields of the retrieved order.

Listing 4. Getting the fields of a specified order

<?php
  //File: getOrderFields.php
  require_once 'connect.php';
  function getOrderFields($order_no) {
   if (!$rsConnection = GetConnection()){
      return false;
   }
   $strSQL = "SELECT TO_CHAR(ORDER_DATE) ORDER_DATE, CUSTOMER_ID, 
ORDER_TOTAL FROM ORDERS WHERE order_id =:order_no";
   $rsStatement = oci_parse($rsConnection,$strSQL);
   oci_bind_by_name($rsStatement, ":order_no", $order_no, 12);
   if (!oci_execute($rsStatement)) {
    $err = oci_error();
    print $err['message'];
    trigger_error('Query failed: ' . $err['message']);
    return false;
   }
   $results = oci_fetch_assoc($rsStatement);
   return $results;
 }
?>

Listing 5 shows the getOrderItems.php script containing the getOrderItems function that takes the ID of an order and returns a two-dimensional array that contains rows representing the line items of the order.

Listing 5. Getting the line items of a specified order

<?php
  //File: getOrderItems.php
  require_once 'connect.php';
  function getOrderItems($order_no) {
   if (!$rsConnection = GetConnection()){
      return false;
   }
   $strSQL = "SELECT * FROM ORDER_ITEMS WHERE 
order_id =:order_no ORDER BY line_item_id";
   $rsStatement = oci_parse($rsConnection,$strSQL);
   oci_bind_by_name($rsStatement, ":order_no", $order_no, 12);
   if (!oci_execute($rsStatement)) {
    $err = oci_error();
    trigger_error('Query failed: ' . $err['message']);
    return false;
   }
   $nrows = oci_fetch_all($rsStatement, $results);
   return array ($nrows, $results);
 }
?>

Note that both of the above functions require the connect.php script that is supposed to contain the GetConnection function returning a connection to the database. Listing 6 shows what the connect.php script might look like:

Listing 6. Obtaining a connection to the database

<?php
  //File: connect.php
   function GetConnection() {
    $dbHost = "dbserverhost";
    $dbHostPort="1521";
    $dbServiceName = "orclR2";
    $usr = "oe";
    $pswd = "oe";
    $dbConnStr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$dbHost.")
(PORT=".$dbHostPort."))(CONNECT_DATA=(SERVICE_NAME=".$dbServiceName.")))";
    if(!$dbConn = oci_connect($usr,$pswd,$dbConnStr)) {
       $err = oci_error();
       trigger_error('Failed to connect ' . $err['message']);
       return false;
      }
    return $dbConn;
  }
?>

Now that you have created all the functions needed to communicate with the database, it’s time to see the Cache_Lite_Function class in action. Listing 7 shows the testCache.php script that uses the Cache_Lite_Function class to cache the results of the above functions.

Listing 7. Caching with PEAR::Cache_Lite

<?php
 //File: testCache.php
 require_once 'getOrderItems.php';
 require_once 'getOrderFields.php';
 require_once 'Cache/Lite/Function.php';

 $options = array( 
  'cacheDir' => '/tmp/', 
  'lifeTime' => 86400 
 ); 

 if (!isset($_GET['order_no'])) {
   die('The order_no parameter is required');
  } 

 $order_no=$_GET['order_no'];
 $cache = new Cache_Lite_Function($options);
 if ($orderfields = $cache->call('getOrderFields', $order_no)){
      print "<h3>ORDER #$order_no</h3>\n";
      print "<table>";
      print "<tr><td>DATE:</td><td>".$orderfields['ORDER_DATE']."</td></tr>";
      print "<tr><td>CUST_ID:</td><td>".$orderfields['CUSTOMER_ID']."</td></tr>";
      print "<tr><td>TOTAL:</td><td>".$orderfields['ORDER_TOTAL']."</td></tr>";
      print "</table>";
 } else {
   print "Some problem occurred while getting order fields!\n";
   $cache->drop('getOrderFields', $order_no);
 }

 if (list($nrows, $orderitems) = $cache->call('getOrderItems', $order_no)){
  //print "<h3>LINE ITEMS IN ORDER #$order_no</h3>";
  print "<table border=1>";
  print "<tr>\n";
  while (list($key, $value) = each($orderitems)) {
   print "<th>$key</th>\n";
  }
  print "</tr>\n";
  for ($i = 0; $i < $nrows; $i++) {
   print "<tr>";
   print "<td>".$orderitems['ORDER_ID'][$i]."</td>";
   print "<td>".$orderitems['LINE_ITEM_ID'][$i]."</td>";
   print "<td>".$orderitems['PRODUCT_ID'][$i]."</td>";
   print "<td>".$orderitems['UNIT_PRICE'][$i]."</td>";
   print "<td>".$orderitems['QUANTITY'][$i]."</td>";
   print "</tr>";
  }
  print "</table>";
 } else {
   print "Some problem occurred while getting order line items";
   $cache->drop('getOrderItems', $order_no);
 }
?>

The testCache.php script shown in Listing 7 is supposed to be called along with an order_no URL parameter representing the ID of an order stored in the OE.ORDER table. For example, to retrieve the information related to the order whose ID is 2408, you enter the URL in your browser, which might look like the following:

http://webserverhost/phpcache/testCache.php?order_no=2408

As a result, the browser should produce the following output:

ORDER #2408

Now, if you click the reload button in your browser, the testCache.php script won’t call the getOrderFields and getOrderItems functions again. Instead, it will read their results from the local cache. So, from now and during the next 24 hours (because lifeTime was set to 86400 secs), each subsequent call to getOrderFields or getOrderItems with order_no=2108 will be satisfied from the local cache. Note, however, that the Cache_Lite_Function class provides no API to test if there is an available cache for a given function with given arguments. So, it can be a little tricky to determine whether your application really reads from the cache or still executes the function each time it is called with the same parameters. In the above case, to make sure that the caching mechanism is working, you might, for example, temporarily change the connection information specified in the connect.php script so that it’ll become impossible to establish a connection to the database; say, by specifying a wrong name for the database server host, and then run the testCache.php script with order_no=2108 again. If caching is working properly, your browser should display the same output as before.

Additionally, you may check the cache directory, which was passed to the constructor of the Cache_Lite_Function class as the value of the cacheDir option (/tmp in this example). In this directory, you should find two just created cache files that might be named something like this: cache_7b181b55b55aee36ad5e7bd9d5a091ec_3ad04d3024f4cd54296f75c92a359154. Note that if you are a Windows user, you’ll probably want to use the %SystemDrive%\temp directory to hold cache files. If so, you have to set the cacheDir option to /temp/.

Once you have verified that the caching mechanism is working properly, you can move on to creating a PHP script that will handle change notifications received from the database server. Listing 8 shows the dropResult.php script that your database server will invoke in response to changes on the ORDERS and ORDER_ITEMS tables.

Listing 8. Handling change notifications received from the database server

<?php
  //File: dropResults.php
 require_once 'Cache/Lite/Function.php';

 $options = array(
    'cacheDir' => '/tmp/'
 );
 $cache = new Cache_Lite_Function($options);
 if (isset($_GET['order_no'])&& isset($_GET['table'])) {
   if($_GET['table']=='ORDER_ITEMS'){
    $cache->drop('getOrderItems', $_GET['order_no']);
   }
   if ($_GET['table']=='ORDERS'){
    $cache->drop('getOrderFields', $_GET['order_no']);
   }
  } 
?>

Once you have created the dropResult.php script, make sure that the URL specified in the notification handler shown in Listing 2 is correct. Then, connecting as OE/OE in SQL*Plus or a similar tool, issue UPDATE statements affecting the same order that you accessed by the testCache.php script a bit earlier in this section (here, it is the order whose ID is 2408):

UPDATE ORDERS SET order_mode = 'direct' WHERE order_id=2408;
UPDATE ORDER_ITEMS SET quantity = 3 WHERE order_id=2408 AND line_item_id=1;
UPDATE ORDER_ITEMS SET quantity = 1 WHERE order_id=2408 AND line_item_id=2;
COMMIT;

In response to the above updates, the notification handler discussed earlier in this article should run the dropResults.php script twice, using the following URLs one after another:

http://webserverhost/phpcache/dropResults.php?order_no=2408&table=ORDERS
http://webserverhost/phpcache/dropresults.php?order_no=2408&table=ORDER_ITEMS

From Listing 8, it is fairly obvious that the dropResult.php script doesn’t refresh the cache after receiving a change notification from the database server. All it does is just drop the cache file containing out of date data. So, if you now check the cache directory, you should see that both the cache files created when running the testCache.php script with order_no=2408 have gone. What this means in practice is that the next time the testCache.php requests the data related to the order whose ID is 2408, it will fetch it from the back-end database rather than from the local cache.

You may find this approach useful in situations where there is a good chance that a result set your application is requesting will change before the application makes use of it. In terms of our example, this means that the data related to a specific order may change several times before the testCache.php accesses that order. So, the application would do a lot of unnecessary work by refreshing its cache immediately after receiving a change notification from the database server.

However, if you want the dropResult.php script to refresh the cache immediately after receiving a change notification, you might make a call to the call method of a Cache_Lite_Function instance after calling the drop method, specifying the same parameters for both calls. In this case, also make sure to include the getOrderFields.php and getOrderItems.php scripts so that dropResults.php can call the getOrderFields and getOrderItems functions to refresh the cache. The revised dropResult.php script is shown in Listing 9.

Listing 9. Refreshing the cache immediately after receiving a change notification

<?php
  //File: dropResults.php
 require_once 'Cache/Lite/Function.php';
 require_once 'getOrderItems.php';
 require_once 'getOrderFields.php';

 $options = array(
    'cacheDir' => '/tmp/',
    'lifeTime' => 86400 
 );
 $cache = new Cache_Lite_Function($options);
 if (isset($_GET['order_no'])&& isset($_GET['table'])) {
   if($_GET['table']=='ORDER_ITEMS'){
    $cache->drop('getOrderItems', $_GET['order_no']);
    $cache->call('getOrderItems', $_GET['order_no']);
   }
   if ($_GET['table']=='ORDERS'){
    $cache->drop('getOrderFields', $_GET['order_no']);
    $cache->call('getOrderFields', $_GET['order_no']);
   }
  } 
?>

The above approach may be useful if the data stored in the ORDERS and ORDER_ITEMS tables change infrequently and your application accesses it heavily.

Summary

If your PHP application interacts with Oracle Database 10g Release 2, you might take advantage of 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. Given this feature, you don’t necessarily need to update the cache in your application in a specific period of time. Instead, you do it only if the result set of a registered query has changed.

Yuli Vasiliev (jvyul@yahoo.com) is a software developer, freelance author, and consultant. His current interests include open source development, Oracle XML technologies, and service-oriented architecture.