Hybrid Cache Group

A hybrid cache group is a dynamic read-only cache group where the root table is created in the TimesTen database and does not exist in the Oracle database.

A cache group is a set of tables related through foreign keys that cache data from tables in an Oracle database. Each cache group includes one root table that does not reference any of the other tables. Foreign keys on all other cache tables in the cache group reference exactly one other table in the cache group. In other words, the foreign key relationships form a tree. For multiple table cache groups, you determined the relationship between the tables by defining which table is the root table, which tables are direct child tables of the root table, and which tables are the child tables of other child tables. Historically, all tables within the cache group exist in the Oracle database.

With a hybrid cache group, you can dynamically load from cache tables that do not have a root table on the Oracle database. A hybrid cache group is a dynamic read-only cache group where the root table is created in the TimesTen database and does not exist in the Oracle database.

TimesTen creates the root table on the TimesTen database from the definition of the hybrid cache group. Note that you should not create this table on the Oracle database.
The only columns allowed in the root table definition are the columns defining the primary key.
All other cache tables must exist in the Oracle database.
The root table must be referenced by at least one child table through a foreign key relationship.

The following sections describe how to use a hybrid cache group:

Creating a Hybrid Cache Group

You can use the CREATE DYNAMIC HYBRID READONLY CACHE GROUP statement to create a dynamic hybrid read-only cache group where the root table exists only on TimesTen.

The following are the definitions of the tables that are to be cached in the customer_orders dynamic hybrid read-only cache group.

The customer root table exists only on the TimesTen database and contains only a primary key. You do not create the root table in the Oracle database as it is created by TimesTen when you specify the root table in the CREATE DYNAMIC HYBRID READONLY CACHE GROUP statement.
Customers can have more than one order and each order can go to a different location. To track the order status for each customer location, the locations and orders tables are created on the Oracle database and are children of the customer table.

With the customer_id as part of the composite key for both the locations and orders tables, you can print out the status of all orders for each customer location. In addition, the invoices table (as a child of the orders table) can be queried to determine if the order has been paid.
```
CREATE TABLE locations
  (customer_id NUMBER(6),
   location_id NUMBER(6),
   name VARCHAR2(255) NOT NULL,
   street CHAR(30) NOT NULL,
   city CHAR(20) NOT NULL,
   state CHAR(2) NOT NULL,
   zipcode CHAR(10) NOT NULL, 
 PRIMARY KEY (customer_id, location_id));

CREATE TABLE orders
  (order_id NUMBER,
   location_id NUMBER(6),
   customer_id NUMBER(6),
   when_placed  DATE NOT NULL,
   status NUMBER(2) NOT NULL,
PRIMARY KEY (order_id, location_id, customer_id));

CREATE TABLE invoices
  (invoice_id NUMBER PRIMARY KEY,
   order_id NUMBER,
   total   NUMBER,
   paid    NUMBER);
```
The Oracle cache administration user must be granted the SELECT privilege on the cached tables. In this example, these tables are sales.customer, sales.locations, sales.orders and sales.invoices tables.

See Required Privileges for Cache Administration User for Cache Operations for all required privileges for different activities.

On the Oracle database as an administrator, grant the following privileges:
```
SQL> GRANT SELECT ON sales.customer TO cacheadmin;
SQL> GRANT SELECT ON sales.locations TO cacheadmin;
SQL> GRANT SELECT ON sales.orders TO cacheadmin;
SQL> GRANT SELECT ON sales.invoices TO cacheadmin;
```

On the TimesTen database, connect as the TimesTen cache administration user to create the cache group. Use the CREATE DYNAMIC HYBRID READONLY CACHE GROUP statement to create the customer root table on TimesTen and a dynamic hybrid read-only cache group called customer_orders, which caches the Oracle database tables: locations, orders, and invoices (child tables). Note that the locations and orders cache tables reference the primary key of the customer root table that exists on the TimesTen database.

Note:

See CREATE CACHE GROUP in the Oracle TimesTen In-Memory Database SQL Reference.

CREATE DYNAMIC HYBRID READONLY CACHE GROUP customer_orders
FROM customer
 (customer_id NUMBER(6) NOT NULL,
  PRIMARY KEY(customer_id)),

 locations
  (customer_id NUMBER(6),
   location_id NUMBER(6),
   name VARCHAR2(255) NOT NULL,
   street CHAR(30) NOT NULL,
   city CHAR(20) NOT NULL,
   state CHAR(2) NOT NULL,
   zipcode CHAR(10) NOT NULL,
 PRIMARY KEY (customer_id, location_id), 
 FOREIGN KEY (customer_id) REFERENCES customer(customer_id)),

 orders
  (order_id NUMBER,
   location_id NUMBER(6),
   customer_id NUMBER(6),
   when_placed  DATE NOT NULL,
   status NUMBER(2) NOT NULL,
 PRIMARY KEY (order_id, location_id, customer_id), 
 FOREIGN KEY (customer_id) REFERENCES customer(customer_id)),

 invoices
  (invoice_id NUMBER,
   order_id NUMBER,
   total   NUMBER,
   paid    NUMBER,
 PRIMARY KEY (invoice_id), 
 FOREIGN KEY (order_id) REFERENCES order(order_id));

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;

Automatic Passthrough for Hybrid Cache Groups

Set the TT_DynamicPassthrough optimizer hint to notify TimesTen to pass through qualified SELECT statements to the Oracle database for cache groups created without a WHERE clause.

For cache groups without a WHERE clause, you can set the TT_DynamicPassthrough(N) optimizer hint that notifies TimesTen to pass through any SELECT statement to the Oracle database if it results in a dynamic load of a cache instance with >= N number of rows. See Automatic Passthrough of Dynamic Load to the Oracle Database.

Restrictions for a Dynamic Hybrid Read-Only Cache Group

Restrictions for using a dynamic hybrid read-only cache group.

The following are restrictions for a dynamic hybrid read-only cache group:

You can execute a SELECT statement on the root table, as this may help in diagnosing problems. However, a dynamic load is not triggered if you execute a SELECT on the root table in TimesTen.
Hybrid cache groups do not support manually loading the cache group with the LOAD CACHE GROUP statement.
LRU aging is on by default for dynamic cache groups, including hybrid cache groups. Currently, time-based aging is not supported for hybrid cache groups.
Currently, the WHERE clause is not supported in CREATE CACHE GROUP for hybrid cache groups.
Currently, the WITH ID clause is not supported in UNLOAD CACHE GROUP for hybrid cache groups.