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:

Enabling or Disabling Dynamic Load

You can enable or disable dynamic load with the DynamicLoadEnable connection attribute.

0 - Disables dynamic load of Oracle Database data to a single dynamic cache group for the current connection.
1 (default) - Enables dynamic load of Oracle Database data to a single dynamic cache group per statement for the current connection.

You can set the DynamicLoadEnable optimizer hint to temporarily enable or disable dynamic loading of a single cache instance for a particular transaction. You can set the DynamicLoadEnable optimizer hint with one of the following methods:

Use the ttIsql utility set dynamicloadenable command.
Call the ttOptSetFlag built-in procedure with the DynamicLoadEnable flag set to the desired value. The following example sets dynamic loading to 1.
```
call ttOptSetFlag('DynamicLoadEnable', 1)
```

Note:

See DynamicLoadEnable, ttIsql or ttOptSetFlag in the Oracle TimesTen In-Memory Database Reference.

You can also set connection attributes with the SQLSetConnectOption ODBC function (ODBC 2.5) or the SQLSetConnectAttr function (ODBC 3.5). See the Option Support for ODBC 2.5 SQLSetConnectOption and SQLGetConnectOption and Attribute Support for ODBC 3.5 SQLSetConnectAttr and SQLGetConnectAttr sections in the Oracle TimesTen In-Memory Database C Developer's Guide.

Guidelines for Dynamic Load

This section details the guidelines for a dynamic load to occur of cache instances for each cache group referenced in the main query.

Note:

Examples for these guidelines are provided in Examples of Dynamic Load of a Single Cache Instance.

Dynamic load of a cache instance is available only for the following types of statements issued on a cache table in a dynamic cache group:

When an INSERT statement inserts values into any of the child tables of a cache instance that does not currently exist in the TimesTen tables, the cache instance to which the new row belongs dynamically loads. The insert operation for the new child row is propagated to the cached Oracle Database table.
SELECT, UPDATE, or DELETE statements require that the WHERE clause have the conditions as stated in Dynamic Cache Groups.

The SELECT, UPDATE, or DELETE statements for which dynamic load is available must satisfy the following conditions:

If the statement contains a subquery, only the cache group with tables referenced in the main query are considered for a dynamic load.
If the statement references multiple tables of the cache group, the statement must include an equality join condition between the primary keys and foreign keys for all parent and child relationships.
The statement cannot contain the UNION, INTERSECT, or MINUS set operators.
The statement can reference non-cache tables.
The statement can reference cache tables from only one dynamic cache group.

Dynamic load of a cache instance occurs when you set DynamicLoadEnable=1 and the request passes the following rules:

Dynamic load of a cache instance does not occur for a cache group if any table of the cache group is specified more than once in any FROM clause.
Only the conditions specified in the query are considered for dynamic load, which excludes any derived conditions.
If any cache group is referenced only in a subquery, it is not considered for a dynamic load.
When using an active standby pair replication scheme, dynamic load cannot occur in any subscriber.

The following considerations can affect dynamic load:

If tables within multiple cache groups or non-cache group tables are specified in the main query, the join order influences if the cache instance is loaded. If during the processing of the query, a dynamic load is possible and necessary to produce the query results, the dynamic load occurs. However, if no rows are returned, then some or all of the cache instances are not dynamically loaded.
If a statement specifies more than the dynamic load condition on tables of a cache group, the cache instance may be dynamically loaded even though the additional conditions are not qualified for the statement.

You can use aging with a dynamic cache group. TimesTen supports two aging types, least recently used (LRU) aging and time-based aging. By default, the data in a dynamic cache group is subject to LRU aging. Time-based aging on a dynamic cache group overrides LRU aging. If the cache group has a time-based aging policy defined, the timestamp in the root table's row must be within the aging policy's lifetime in order for the cache instance to be loaded.

Rows in a dynamic AWT cache group must be propagated to the Oracle database before they become candidates for aging.

You can use the ttAgingLRUConfig built-in procedure to override the default or current LRU aging attribute settings for the aging cycle and TimesTen database space usage thresholds. See Implementing Aging in a Cache Group for TimesTen Classic.

Examples of Dynamic Load of a Single Cache Instance

Provides an example that defines Oracle database tables, which are then cached into a dynamic AWT cache group.

The following is the definition of the Oracle Database tables that are to be cached in a dynamic AWT cache group. 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));

CREATE TABLE orders
(ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
 cust_num     NUMBER(6) NOT NULL,
 when_placed  DATE NOT NULL,
 when_shipped DATE NOT NULL);

CREATE TABLE orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY (orderid, itemid));

For example, the following data is 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

The following statement creates a dynamic AWT cache group new_customers that caches the sales.customer, sales.orders, and sales.orderdetails tables:

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)),
sales.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES sales.customer(cust_num)),
sales.orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY(orderid, itemid),
  FOREIGN KEY(orderid) REFERENCES sales.orders(order_num));

The following examples show the default behavior as DynamicLoadEnable defaults to 1:

The sales.customer TimesTen cache table is initially empty:

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

The following SELECT statement with an equality condition on the primary key for the sales.customer table results in a dynamic load of a single cache instance:

Command> SELECT * FROM sales.customer WHERE cust_num = 1;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >

If you do not use an equality condition on the primary key and you do not configure for dynamic load of multiple cache instances, then no dynamic load occurs for this example, since it would result in multiple cache instances. See Dynamically Loading Multiple Cache Instances for details on how to configure for this scenario.

Command> SELECT * FROM sales.customer WHERE cust_num IN (1,2);

The following example contains equality expressions on all of the primary key columns for a primary key composite. The orderdetails table has a composite primary key of orderid and itemid.

UPDATE sales.orderdetails SET quantity = 5 WHERE orderid=2280 AND itemid=663;

The following example shows an INSERT into the orders child table, which initiates a dynamic load. However, if you tried to insert into the customer table, which is the parent, no dynamic load occurs.

INSERT INTO orders VALUES(1,1, DATE '2012-01-25', DATE '2012-01-30');

The following UPDATE statement dynamically loads one cache instance from the cached Oracle Database table into the TimesTen cache table, updates the instance in the cache table, and then automatically propagates the update to the cached Oracle Database table:

Command> UPDATE sales.customer SET name = 'Angela Peterson' WHERE cust_num = 2;
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >
< 2, East, Angela Peterson, 356 Olive St., Boston MA >

The following is the updated 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 Peterson   356 Olive St., Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr., Chicago IL

The following DELETE statement dynamically loads one cache instance from the cached Oracle Database table into the TimesTen cache table, deletes the instance from the cache table, and then automatically propagates the delete to the cached Oracle Database table:

Command> DELETE FROM sales.customer WHERE cust_num = 3;
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >
< 2, East, Angela Peterson, 356 Olive St., Boston MA >

The following is the updated 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 Peterson   356 Olive St., Boston MA

The following is an example of a dynamic load performed using all columns of a unique index on the root table. The departments table is defined in a dynamic AWT cache group. A unique index is created on this cache group consisting of the manager_id and location_id.

The following creates the departments table on the Oracle database.

Command> CREATE TABLE departments(
        department_id INT NOT NULL PRIMARY KEY,
        department_name VARCHAR(10) NOT NULL,
        technical_lead INT NOT NULL,
        manager_id INT,
        location_id INT NOT NULL);

The following creates the dynamic AWT cache group and a unique index on the dept_cg root table:

Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP dept_cg
        FROM departments
        (department_id INT NOT NULL PRIMARY KEY, 
         department_name VARCHAR(10) NOT NULL, 
         technical_lead INT NOT NULL, 
         manager_id INT, location_id INT NOT NULL);

Command> CREATE UNIQUE INDEX dept_idx 
         ON departments
         (manager_id, 
          location_id);

The following inserts three records into the departments table on the Oracle database:

Command> INSERT INTO departments 
              VALUES (1, 'acct', 1, 1, 100);
1 row inserted.
Command> INSERT INTO departments 
          VALUES (2, 'hr', 2, 2, 200);
1 row inserted.
Command> INSERT INTO departments 
          VALUES (3, 'owner', 3, NULL, 300);
1 row inserted.
Command> commit;

On TimesTen, dynamically load a cache instance based on the unique index:

Command> SELECT * FROM departments;
0 rows found.
Command> SELECT * FROM departments 
           WHERE manager_id IS NULL AND location_id=300;
< 3, owner, 3, <NULL>, 300 >
1 row found.
Command> SELECT * FROM departments;
< 3, owner, 3, <NULL>, 300 >
1 row found.
Command> SELECT * FROM departments 
          WHERE manager_id=2 AND location_id=200;
< 2, legal, 2, 2, 200 >
1 row found.
Command> SELECT * FROM departments;
< 2, legal, 2, 2, 200 >
< 3, owner, 3, <NULL>, 300 >
2 rows found.

Dynamically Loading Multiple Cache Instances

If configured, TimesTen can dynamically load multiple cache instances for dynamic cache groups that contain only a single table.

TimesTen Classic dynamically loads cache instances associated with a primary key that do not already exist in the cache group. Any cache instances associated with a primary key that already exist in the cache group are not reloaded. As a result, your query may return partial results. If a cache instance already exists on TimesTen, these cache instances can only be updated with either an autorefresh operation or a REFRESH CACHE GROUP statement.

The following sections describe methods for dynamically loading multiple cache instances:

Dynamically Loading Multiple Cache Instances With Multiple Primary Keys

TimesTen Classic can dynamically load multiple cache instances for a SELECT statement that includes more than one primary key referenced in the WHERE clause on a single table cache group.

You can dynamically load multiple cache instances by specifying multiple primary key values in the WHERE clause.

Only supported with SELECT statements.
Only supported on a single table cache group.
For a multiple column primary key, all columns of the primary key must be specified in the WHERE clause.
Each primary key in the WHERE clause must use conditions with either an IN operator and/or a single value from an equality condition.

By default, the DynamicLoadMultiplePKs or TT_DynamicLoadMultiplePKs statement, transaction or connection level hint is set to 1. This must be enabled for dynamic load for multiple cache instances using more than one primary key.

Statement level hint:
```
/*+TT_DynamicLoadMultiplePKs(1)*/
```

Transaction level hint:

Call ttOptSetFlag(DynamicLoadMultiplePKs, 1)

Connection level hint:

OptimizerHint = TT_DynamicLoadMultiplePKs(1)

Note:

See Use Optimizer Hints to Modify the Execution Plan in the Oracle TimesTen In-Memory Database Operations Guide, ttOptSetFlag in the Oracle TimesTen In-Memory Database Reference and Optimizer Hints in the Oracle TimesTen In-Memory Database SQL Reference.

The following examples use a cache group of products_cg that caches the Oracle database products table.

CREATE DYNAMIC READONLY CACHE GROUP products_cg FROM
  products(prod_type INT NOT NULL, prod_id BIGINT NOT NULL, prod_name VARCHAR2(100),
                 prod_weight NUMBER, PRIMARY KEY(prod_type, prod_id));

The following examples demonstrate SELECT statements with a WHERE clause with multiple primary keys that use conditions with either an IN operator and/or a single value from an equality condition to return the name and weight of multiple products.

If the primary key of a root table is composed of two columns, x and y, the following SELECT queries do result in a dynamic load:

Both columns of the primary key use a condition with an IN operator.

(x,y) IN ((1,2),(3,4))

SELECT p.prod_name, p.prod_weight
 FROM products p
  WHERE (((prod_type, p.prod_id) IN ((1,2), (10,20) , (100, 200))));

Both columns of the primary key use conditions with an IN operator.

(x IN (1,3)) AND (y IN (2,4))

SELECT p.prod_name, p.prod_weight
 FROM products p WHERE ((p.prod_type IN (1, 10, 100)) AND (p.prod_id IN (2, 20, 200)));

Both columns of the primary key use conditions with an equality condition resulting in a single value.

(x=1 OR x=3) AND (y=2 OR y=4)
```
SELECT p.prod_name, p.prod_weight
 FROM products p WHERE ((p.prod_type = 10 OR p.prod_type=100) AND 
    (p.prod_id = 20 OR p.prod_id = 200));
```

Dynamically Loading Multiple Cache Instances Without Multiple Primary Keys

TimesTen Classic can dynamically load multiple cache instances without using multiple primary keys referenced in a WHERE clause on a single table cache group.

If the query tries to load cache instances that both exist and do not exist in the database, then the entire dynamic load operation does not execute. The dynamic load only executes if none of the cache instances requested already exist in the TimesTen database.

By default, TimesTen Classic does not dynamically load multiple cache instances for a single table cache group when the SELECT statement has an arbitrary WHERE clause, unless you set one of the following statement, transaction or connection level hints to 1.

Statement level hint:
```
/*+TT_DynamicLoadRootTbl (1)*/
```

Transaction level hint:

Call ttOptSetFlag(DynamicLoadRootTbl , 1)

Connection level hint:

OptimizerHint = TT_DynamicLoadRootTbl (1)

Note:

Restrictions for dynamic load of multiple cache instances with arbitrary WHERE clause

In order for a dynamic load of multiple cache instances for a single table cache group, the SELECT statement query must comply with the following:

The results of the WHERE clause do not include any cache instances that currently exist in the TimesTen database.
The WHERE clause must be supported by the Oracle Database SQL syntax.
Does not qualify for any other dynamic load condition.
Does not use aggregation.
No other table is referenced within the query. That is, the SELECT statement does not specify any JOIN clauses or any subqueries embedded within the WHERE clause.
Does not use the SELECT...FOR UPDATE clause or the INSERT … FOR SELECT clause.

Examples

These examples use the following cache group definition on the TimesTen database:

CREATE DYNAMIC READONLY CACHE GROUP cust_orders FROM
    customers(cust_id BIGINT NOT NULL PRIMARY KEY, cust_name VARCHAR2(100), 
    cust_street VARCHAR2(200), cust_state VARCHAR2(2), cust_zip VARCHAR2(10))
    WHERE (customers.cust_state = 'CA');

Data is inserted into the Oracle database.

INSERT INTO customers(cust_id, cust_name, cust_street, cust_state, cust_zip)
  VALUES (100, 'Tom Hanks', '100 Rodeo Dr', 'CA', '90210');
INSERT INTO customers(cust_id, cust_name, cust_street, cust_state, cust_zip)
  VALUES (200, 'Fred Rogers', '1 Make-Believe Ave', 'CA', '90210');

None of the requested customers are in the cache group on the TimesTen database; thus, all of the requested customers (and their orders) are dynamically loaded and their names are returned by the query.

SELECT c.cust_name
 FROM customers c
  WHERE (c.cust_zip like '90210%');
<'Tom Hanks'>
<'Fred Rogers'>

Another customer and full data is inserted into the Oracle database:

INSERT INTO customers(cust_id, cust_name, cust_street, cust_state, cust_zip)
  VALUES (300, 'Matthew Rhys', '2 Moscow Cir', 'CA', '90210');

On the TimesTen database, the following query is executed. Since the cache group already has at least 1 row that satisfies the query, the dynamic load is not triggered. Thus, only data that currently exists in the cache group are returned for the query.

SELECT c.cust_name
 FROM customers c
  WHERE (c.cust_zip like '90210%');
<'Tom Hanks'>
<'Fred Rogers'>

Returning Errors for Dynamic Load

You can configure TimesTen to return an error if a SELECT, UPDATE or DELETE statement does not meet the requirements.

See Guidelines for Dynamic Load for requirements of a dynamic load.

The DynamicLoadErrorMode connection attribute controls what happens when an application runs a SQL operation against a dynamic cache group and the SQL operation cannot use dynamic load in a particular connection.

When DynamicLoadErrorMode is set to a value of 0, dynamic load happens to any cache group referenced in the query that is qualified for dynamic load. Cache groups that do not qualify are not dynamically loaded and no errors are returned. When DynamicLoadEnable=1, no dynamic load occurs if the query references more than one cache group.
When DynamicLoadErrorMode is set to a value of 1, a query fails with an error if any dynamic cache group referenced in the query is not qualified for dynamic load. The error indicates the reason why the dynamic load cannot occur.

To set the connection attribute solely for a particular transaction, use one of the following:

Use the ttIsql utility set dynamicloaderrormode 1 command.
Call the ttOptSetFlag built-in procedure with the DynamicLoadErrorMode flag and the optimizer value set to 1.
```
call ttOptSetFlag('DynamicLoadErrorMode', 1)
```
Call the ttOptSetFlag built-in procedure with the DynamicLoadErrorMode flag and the optimizer value set to 0 to suppress error reporting when a statement does not comply with dynamic load requirements.