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