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));
```