Dynamic Cache Groups

You define whether your cache group is dynamically loaded by specifying the DYNAMIC keyword during cache group definition.

When a qualifying SQL statement queries rows that do not exist in the TimesTen database, then TimesTen automatically loads the relevant cache instances from the Oracle database tables into dynamic cache groups. A dynamic load of a cache instance is similar to a LOAD CACHE GROUP statement in that it retrieves and automatically loads a qualified cache instance on demand from the Oracle database to the TimesTen database. A cache instance consists of row from the root table of any cache group (that is uniquely identified by either a primary key or a unique index on the root table) and all related rows in the child tables associated by foreign key relationships. Dynamic load operations do not update or delete cache instances that already exist in the cache tables even if the corresponding rows in the cached Oracle Database tables have been updated or deleted. Dynamic load operations are used to dynamically provide data for the application. Often, dynamic load operations are combined with aging, so that data can be aged out when not needed and dynamically loaded when needed.

Note:

The REFRESH CACHE GROUP statement and autorefresh are used to update or delete cache instances that already exist in the TimesTen database. You can use autorefresh to automatically populate changes made to cache instances in the Oracle Database.

For example, a call center application may not want to preload all of its customers' information into TimesTen as it may be very large. Instead, you can define the cache group with the DYNAMIC keyword. After which, the cache group can use dynamic load on demand so that a specific customer's information is loaded only when needed such as when the customer calls or logs onto the system.

This following example creates a dynamic read-only cache group online_customers that caches the sales.customer table:

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

Any system managed cache group type (read-only, AWT, SWT or hybrid) can be defined with the DYNAMIC keyword. A user managed cache group can be defined with the DYNAMIC keyword unless it uses both the AUTOREFRESH and the PROPAGATE cache table attributes.

Note:

If you have a dynamic read-only cache group with incremental autorefresh, you can reduce contention and improve performance with either of the methods described in Options for Reducing Contention Between Autorefresh and Dynamic Load Operations.

When a cache group is enabled for dynamic load, a cache instance is uniquely identified either by a primary key, a unique index on any table, or a foreign key of a child table. If a row in the cached Oracle Database table satisfies the WHERE clause and the row is not in the TimesTen database, then the entire associated cache instance is loaded in order to maintain the defined relationships between primary keys and foreign keys of the parent and child tables. When a cache group is enabled for dynamic load, the dynamic load operation typically loads only one cache instance into the root table of any cache group, unless you specifically request to load multiple cache instances (as described in Dynamically Loading Multiple Cache Instances).

The WHERE clause must specify one of the following for a dynamic load to occur:

An equality condition with constants and/or parameters on all columns of a primary key or a foreign key of any table of the cache group. If more than one table of a cache group is referenced, each must be connected by an equality condition on the primary or foreign key relationship.
A mixture of equality or IS NULL conditions on all columns of a unique index, provided that you use at least one equality condition. That is, you can perform a dynamic load where some columns of the unique index are NULL. The unique index must be created on the root table of the cache group.

Note:

Dynamic loading based on a primary key search of the root table performs faster than primary key searches on a child table or foreign key searches on a child table.

The dynamic load runs in a different transaction than the user transaction that triggers the dynamic load. The dynamic load transaction is committed before the SQL statement that triggers the dynamic load has finished processing. Thus, if the user transaction is rolled back, the dynamically loaded data remains in the cache group.

Note:

If the Oracle database is down, the following error is returned:

5219: Temporary Oracle connection failure error in OCISessionBegin():
ORA-01034: ORACLE not available

The following sections describes dynamic load for cache groups: