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