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:

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.

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