1. Cache Guide
  2. Methods for Transmitting Changes Between TimesTen and Oracle Databases
  3. Manually Loading and Refreshing a Cache Group

Manually Loading and Refreshing a Cache Group

You can manually insert or update cache instances into the TimesTen cache tables from the cached Oracle Database tables using either a LOAD CACHE GROUP or REFRESH CACHE GROUP statement.

The differences between loading and refreshing a cache group are:

  • The LOAD CACHE GROUP statement only loads committed inserts on the cached Oracle Database tables into the TimesTen cache tables. New cache instances are loaded into the cache tables, but cache instances that already exist in the cache tables are not updated or deleted even if the corresponding rows in the cached Oracle Database tables have been updated or deleted. A load operation is primarily used to initially populate a cache group.

  • The REFRESH CACHE GROUP statement replaces cache instances in the TimesTen cache tables with the most current data from the cached Oracle Database tables including cache instances that are already exist in the cache tables. A refresh operation is primarily used to update the contents of a cache group with committed changes on the cached Oracle Database tables after the cache group has been initially populated.

    For a static cache group, a refresh operation is equivalent to issuing an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement on the cache group. In effect, all committed inserts, updates and deletes on the cached Oracle Database tables are refreshed into the cache tables. New cache instances may be loaded into the cache tables. Cache instances that already exist in the cache tables are updated or deleted if the corresponding rows in the cached Oracle Database tables have been updated or deleted. See Unloading a Cache Group for more information about the UNLOAD CACHE GROUP statement.

    For a dynamic cache group, a refresh operation only refreshes committed updates and deletes on the cached Oracle Database tables into the cache tables because only existing cache instances in the cache tables are refreshed. New cache instances are not loaded into the cache tables so after the refresh operation completes, the cache tables contain either the same or fewer number of cache instances. To load new cache instances into the cache tables of a dynamic cache group, use a LOAD CACHE GROUP statement or perform a dynamic load operation. See Dynamic Cache Groups for more information about a dynamic load operation.

For most cache group types, you can use a WHERE clause in a LOAD CACHE GROUP or REFRESH CACHE GROUP statement to restrict the rows to be loaded or refreshed into the cache tables.

If the cache table definitions use a WHERE clause, only rows that satisfy the WHERE clause are loaded or refreshed into the cache tables even if the LOAD CACHE GROUP or REFRESH CACHE GROUP statement does not use a WHERE clause.

If the cache group has a time-based aging policy defined, only cache instances where the timestamp in the root table's row is within the aging policy's lifetime are loaded or refreshed into the cache tables.

To prevent a load or refresh operation from processing a large number of cache instances within a single transaction, which can greatly reduce concurrency and throughput, use the COMMIT EVERY n ROWS clause to specify a commit frequency unless you are using the WITH ID clause. If you specify COMMIT EVERY 0 ROWS, the load or refresh operation is processed in a single transaction.

In addition, if the load operation is for a large amount of data, use parallelism to increase throughput by specifying the number of threads with the PARALLEL clause.

A LOAD CACHE GROUP or REFRESH CACHE GROUP statement that uses the COMMIT EVERY n ROWS clause must be performed in its own transaction without any other operations within the same transaction.

The following example loads new cache instances into the TimesTen cache tables in the customer_orders cache group from the cached Oracle Database tables: 

LOAD CACHE GROUP customer_orders COMMIT EVERY 256 ROWS PARALLEL 2;

The following example loads into the TimesTen cache tables using a WHERE clause in the new_customers cache group from the cached Oracle Database tables. The WHERE clause specifies new cache instances for customers whose customer number is greater than or equal to 5000: 

LOAD CACHE GROUP new_customers WHERE (sales.customer.cust_num >= 5000)
  COMMIT EVERY 256 ROWS;

The following example refreshes cache instances in the TimesTen cache tables within the top_products cache group from the cached Oracle Database tables: 

REFRESH CACHE GROUP top_products COMMIT EVERY 256 ROWS;

The following example refreshes in the TimesTen cache tables within the update_anywhere_customers cache group from the cached Oracle Database tables. The WHERE clause specifies cache instances of customers located in zip code 60610: 

REFRESH CACHE GROUP update_anywhere_customers
  WHERE (sales.customer.zip = '60610') COMMIT EVERY 256 ROWS;

See LOAD CACHE GROUP and REFRESH CACHE GROUP in Oracle TimesTen In-Memory Database SQL Reference.

The rest of this section includes these topics:

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.

Loading and Refreshing a Multiple-Table Cache Group

If you are loading or refreshing a multiple-table cache group while the cached Oracle Database tables are concurrently being updated, set the isolation level in the TimesTen database to serializable before issuing the LOAD CACHE GROUP or REFRESH CACHE GROUP statement.

This causes TimesTen to query the cached Oracle Database tables in a serializable fashion during the load or refresh operation so that the loaded or refreshed cache instances in the cache tables are guaranteed to be transactionally consistent with the corresponding rows in the cached Oracle Database tables. After you have loaded or refreshed the cache group, set the isolation level back to read committed for better concurrency when accessing elements in the TimesTen database.

Improving the Performance of Loading or Refreshing a Large Number of Cache Instances

You can improve the performance of loading or refreshing a large number of cache instances into a cache group by specifying the operation to be multithreaded with the PARALLEL clause of the LOAD CACHE GROUP or REFRESH CACHE GROUP statement.

If you do not specify the PARALLEL clause, the load or refresh operation will be single-threaded. Specifying the PARALLEL clause to create multithreaded processes for a load or refresh provides a performance benefit if you have large data sets to be loaded or if the round trip time to Oracle is large. For example, if the data in the Oracle database is large, then an initial full load of the cache group can prove to be time consuming. Specifying multiple threads for the initial load can improve performance for that operation. However, note that multithreaded processes require more time to initiate than a single-threaded process and multithreaded processes use more system resources.

Specify the number of threads to use when processing the load or refresh operation. You can specify 2 to 10 threads. Do not specify more threads than the number of CPUs available on your system or you may encounter decreased performance than if you had not used the PARALLEL clause.

Note:

There is no default for the PARALLEL clause.

You cannot use the WITH ID clause with the PARALLEL clause. You can use the COMMIT EVERY n ROWS clause with the PARALLEL clause as long as n is greater than 0. In addition, you cannot use the PARALLEL clause for dynamic read-only cache groups or when database level locking is enabled. See REFRESH CACHE GROUP in the Oracle TimesTen In-Memory Database SQL Reference.

The following example refreshes cache instances in the TimesTen cache tables using a PARALLEL clause. This example refreshes the western_customers cache group from the cached Oracle Database tables using one thread to fetch rows from the cached Oracle Database tables and one thread to insert the rows into the cache tables: 

LOAD CACHE GROUP western_customers COMMIT EVERY 256 ROWS PARALLEL 2;

The number of threads that you specify with the PARALLEL clause are assigned to readers and inserters. TimesTen recommends to have the number of readers >= number of inserters, because readers are slower than inserters. By default, only one thread is assigned to a reader to fetch rows from the cached Oracle Database tables. Since there is only one reader by default, then TimesTen assigns only one of the other threads as an inserter to insert the rows into the TimesTen cache tables. Using more inserters for a single reader offers no benefit.

If you want to specify more than 2 threads, use both the PARALLEL clause with the READERS clause to specify the number of readers to assign. The number of inserters assigned is the number of parallel threads minus the number of readers. 

LOAD CACHE GROUP western_customers COMMIT EVERY 256 ROWS 
                          PARALLEL 6 READERS 4;

This specifies 6 threads with 4 of the threads assigned to readers and 2 threads assigned to inserters.

Example of Manually Loading and Refreshing a Static Cache Group

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

On the Oracle database:

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 static AWT cache group new_customers that caches the sales.customer table: 

CREATE 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 >

On the Oracle database, 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

Back on the TimesTen database as the TimesTen cache administration, run a REFRESH CACHE GROUP statement on a static cache group, which is processed by unloading and then reloading the cache group. As a result, the cache instances in the cache table matches the rows in the cached Oracle Database table. 

Command> REFRESH CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 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 >

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 >