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 areNULL
. 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: