Example of Manually Loading and Refreshing a Dynamic Cache Group

This example shows the definition of an Oracle Database table that is to be cached in a dynamic AWT cache group.

On the Oracle database, connect as the schema owner, sales.

The Oracle Database table is owned by the schema user sales.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

The following is the data in the sales.customer cached Oracle Database table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Wilkins    356 Olive St. Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr. Chicago IL

On the TimesTen database, connect as the TimesTen cache administration user. The following statement creates a dynamic AWT cache group new_customers that caches the sales.customer table:

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM sales.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num));

The sales.customer TimesTen cache table is initially empty:

Command> SELECT * FROM sales.customer;
0 rows found.

The following LOAD CACHE GROUP statement loads the three cache instances from the cached Oracle Database table into the TimesTen cache table:

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instances affected.
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >

Back on the Oracle database, connect as the sales schema user and modify the cached Oracle Database table by inserting a new row, updating an existing row, and deleting an existing row:

SQL> INSERT INTO customer
  2  VALUES (4, 'East', 'Roberta Simon', '3667 Park Ave. New York NY');
SQL> UPDATE customer SET name = 'Angela Peterson' WHERE cust_num = 2;
SQL> DELETE FROM customer WHERE cust_num = 3;
SQL> COMMIT;
SQL> SELECT * FROM customer;
CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Peterson   356 Olive St. Boston MA
       4   East      Roberta Simon     3667 Park Ave. New York NY

On the TimesTen database, a REFRESH CACHE GROUP statement issued on a dynamic cache group only refreshes committed updates and deletes on the cached Oracle Database tables into the cache tables. New cache instances are not loaded into the cache tables. Therefore, only existing cache instances are refreshed. As a result, the number of cache instances in the cache tables are either fewer than or the same as the number of rows in the cached Oracle Database tables.

Command> REFRESH CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
2 cache instances affected.
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >

A subsequent LOAD CACHE GROUP statement loads one cache instance from the cached Oracle Database table into the TimesTen cache table because only committed inserts are loaded into the cache table. Therefore, only new cache instances are loaded. Cache instances that already exist in the cache tables are not changed because of a LOAD CACHE GROUP statement, even if the corresponding rows in the cached Oracle Database tables were updated or deleted.

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
1 cache instance affected.
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >