1. Cache Guide
  2. Defining Cache Groups
  3. Hybrid Cache Group
  4. Specifying the Dynamic Load for a Hybrid Cache Group

Specifying the Dynamic Load for a Hybrid Cache Group

For hybrid cache groups, you can specify a derived table within the FROM clause of the SELECT statement or include more than one table of the same hybrid cache group in the same query.

Dynamic load occurs after evaluating the rules specified in Guidelines for Dynamic Load.

Using a Derived Table

For hybrid cache groups, you can specify a derived table within the FROM clause of the SELECT statement. If the query specifies multiple tables including the derived table, then the materialized result of the derived table with the dynamic load condition is treated as a parent table (but only if the derived table specifies a single first child table of the hybrid cache group).

See DerivedTable in the Oracle TimesTen In-Memory Database SQL Reference.

Example 4-1 Using a Derived Table

The following query uses a derived table within the FROM clause of the SELECT statement. The materialized result of the derived table is treated as the parent table orders when determining if the query qualifies for a dynamic load. The following query uses a derived table within the FROM clause of the SELECT statement. The materialized result of the derived table is treated as the parent table orders when determining if the query qualifies for a dynamic load. 

SELECT * FROM 
 (SELECT customer_id FROM orders WHERE customer_id=? AND ROWNUM <= 5);

Including Multiple Tables

More than one table of the same hybrid cache group can be included in the same query.

  • One or more first level child tables of the same hybrid cache group can be included in a query (including the option of a derived table that includes a first level child table):

    • Specifies the same foreign key as the other first child tables or derived table.

    • Includes a join condition that equates its foreign key with the foreign key of other first child tables or derived table.

  • Any included grandchild table of the same hybrid cache group must:

    • Include a foreign key join condition with either the derived table or a first level child table of the same hybrid cache group.

    • Not be included in an outer table join with its parent table.

The following examples demonstrate the conditions that do and do not trigger a dynamic load for a hybrid cache group. All of these examples are based on the customer_orders hybrid cache group example defined in Creating a Hybrid Cache Group.

Example 4-2 Dynamic Load Condition Using Multiple First Level Child Tables

The following query triggers a dynamic load since two first level child tables (orders and locations) specify the same dynamic load condition. 

SELECT * FROM orders, locations
 WHERE orders.customer_id=:id and locations.customer_id=:id;

And the following query triggers a dynamic load since the locations table equates its foreign key with the orders table foreign key. 

SELECT * FROM orders, locations
 WHERE orders.customer_id=:id and locations.customer_id=:id;

Example 4-3 Dynamic Load Condition Using a First Level Child Table and a Derived Table

The following query triggers a dynamic load since two first level child tables (orders and locations) specify the same dynamic load condition. The locations table equates its foreign key with the dynamically loaded foreign key from the orders table.

The derived table is temporarily named cust as that name is provided directly after the derived table specification. 

SELECT * FROM 
  (SELECT customer_id,order_id FROM orders 
          WHERE customer_id=:id and ROWNUM <= 5) cust, invoices, locations  
 WHERE 
    invoices.order_id = cust.order_id and locations.customer_id=cust.customer_id;

Example 4-4 Dynamic Load Condition Using a First Level Child Table and Grandchild Table

The following query example triggers a dynamic load since the dynamic load condition is on a derived table that includes the orders table (a first level child table of the customer_orders hybrid cache group). It also includes the grandchild table invoices that is included in a foreign key join condition with the derived table cust.

Temporarily, the derived table name is orders and is treated as a parent table. 

SELECT * FROM 
  (SELECT customer_id,order_id FROM orders 
          WHERE customer_id=? and ROWNUM <= 5) cust, invoices 
 WHERE invoices.order_id = cust.order_id;

Example 4-5 Dynamic Load Using Grandchild Table Joined with Derived Table

The following query triggers a dynamic load because the invoices table (as a grandchild table) is joined with the derived table cust through a foreign key join: 

SELECT * FROM 
  (SELECT customer_id,order_id FROM orders 
          WHERE customer_id=? and ROWNUM <= 5) cust, invoices   
 WHERE invoices.order_id = cust.order_id;

Example 4-6 No Dynamic Load Example with First Level Child Table

The following query does not trigger a dynamic load because the first level child locations table specifies a different dynamic load condition than the derived table (cust) load condition: 

SELECT * FROM 
  (SELECT customer_id,order_id FROM orders 
          WHERE customer_id=:id and ROWNUM <= 5) cust, invoices, locations  
 WHERE invoices.order_id = cust.order_id and locations.customer_id=:id2;

Example 4-7 No Dynamic Load Example Using Grandchild Table

The following query does not trigger a dynamic load because the invoices grandchild table is not joined through a foreign key join with its parent, the orders table. 

SELECT * FROM 
  (SELECT customer_id,order_id FROM orders 
          WHERE customer_id=? and ROWNUM <= 5) cust, invoices   
 WHERE invoices.invoice_id=?;

Example 4-8 No Dynamic Load Second Example Using Grandchild Table

The following query does not trigger a dynamic load because the invoices grandchild table is included in an outer table of a join with its parent, the orders table. 

SELECT * FROM invoices LEFT JOIN
  (SELECT customer_id,order_id FROM orders 
          WHERE customer_id=? and ROWNUM <= 5) cust 
 ON invoices.order_id = cust.order_id;