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: