Loading and Refreshing a Cache Group Using a WITH ID Clause

The WITH ID clause of the LOAD CACHE GROUP or REFRESH CACHE GROUP statement enables you to load or refresh a cache group based on values of the primary key columns without having to use a WHERE clause.

The WITH ID clause is more convenient than the equivalent WHERE clause if the primary key contains more than one column. Using the WITH ID clause allows you to load one cache instance at a time. It also enables you to roll back the transaction containing the load or refresh operation, if necessary, unlike the equivalent statement that uses a WHERE clause because using a WHERE clause also requires specifying a COMMIT EVERY n ROWS clause.

The following example loads a cache group using a WITH ID clause. A cache group recent_orders contains a single cache table sales.orderdetails with a primary key of (orderid, itemid). If a customer calls about an item within a particular order, the information can be obtained by loading the cache instance for the specified order number and item number.

Load the sales.orderdetails cache table in the recent_orders cache group with the row whose value in the orderid column of the sales.orderdetails cached Oracle Database table is 1756 and its value in the itemid column is 573:

LOAD CACHE GROUP recent_orders WITH ID (1756,573);

The following is an equivalent LOAD CACHE GROUP statement that uses a WHERE clause:

LOAD CACHE GROUP recent_orders WHERE orderid = 1756 and itemid = 573
  COMMIT EVERY 256 ROWS;

A LOAD CACHE GROUP or REFRESH CACHE GROUP statement issued on a cache group with autorefresh cannot contain a WITH ID clause unless the cache group is dynamic.

You cannot use the COMMIT EVERY n ROWS clause with the WITH ID clause.