Cache Groups and Cache Tables

A cache group defines the Oracle Database data to cache in the TimesTen database. When you create a cache group, cache tables are created in the TimesTen database that correspond to the Oracle Database tables being cached.

A separate table definition must be specified in the cache group definition for each Oracle Database table that is being cached. The owner, table name, and cached column names of a TimesTen cache table must match the schema owner, table name, and column names of the corresponding cached Oracle Database table. The cache table can contain all or a subset of the columns and rows of the cached Oracle Database table. Each TimesTen cache table must have a primary key.

An Oracle Database table cannot be cached in more than one cache group within the same TimesTen database. However, the table can be cached in separate cache groups in different TimesTen databases.

If a table is cached in separate AWT cache groups and the same cache instance is updated simultaneously on multiple TimesTen databases, there is no guarantee as to the order in which the updates are propagated to the cached Oracle Database table. In this case, the contents of the updated cache table may be inconsistent between the TimesTen databases.

Before you define the cache group table, create the Oracle Database tables that are to be cached. Each table should be either:

An Oracle Database table with a primary key on non-nullable columns. The TimesTen cache table primary key must be defined on the full Oracle Database table primary key. For example, if the cached Oracle Database table has a composite primary key on columns c1, c2 and c3, the TimesTen cache table must also have a composite primary key on columns c1, c2 and c3.

The following example shows how to create a cache group from an Oracle Database table with a composite primary key. The following job_history table was created with a composite key on the Oracle database:
```
CREATE TABLE job_history
    (employee_id NUMBER(6) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    job_id VARCHAR2(10) NOT NULL,
    department_id NUMBER(4),
    PRIMARY KEY(employee_id, start_date)); 
Table created.
```
Create the cache group on the TimesTen database with all columns of the composite primary key:
```
CREATE WRITETHROUGH CACHE GROUP job_hist_cg
        FROM sales.job_history
        (employee_id NUMBER(6) NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        job_id VARCHAR2(10) NOT NULL,
        department_id NUMBER(4),
        PRIMARY KEY(employee_id, start_date));
```

An Oracle Database table with non-nullable columns upon which a unique index is defined on one or more of the non-nullable columns in the table. The TimesTen cache table primary key must be defined on all of the columns in the unique index. For example, if the unique index for the Oracle Database table is made up of multiple columns c1, c2, and c3, the TimesTen cache table must have a composite primary key on columns c1, c2, and c3.

The following examples show how Oracle Database unique indexes were defined on tables with non-nullable columns.

SQL> CREATE TABLE regions(
      region_id NUMBER NOT NULL, 
      region_name VARCHAR2(25));
Table created.
SQL> CREATE UNIQUE INDEX region_idx 
      ON regions(region_id);
Index created.

SQL> CREATE TABLE products(
      prod_id INT NOT NULL, 
      cust_id INT NOT NULL,
      quantity_sold INT NOT NULL,
      time_id DATE NOT NULL);
Table created.
SQL> CREATE UNIQUE INDEX products_index ON products(prod_id, cust_id);
Index created.

Based on these Oracle Database tables and unique indexes, you can create cache groups on a TimesTen database for these tables using the unique index columns as the primary key definition as shown below:

Command> CREATE WRITETHROUGH CACHE GROUP region_cg
  FROM sales.regions
  (region_id NUMBER NOT NULL PRIMARY KEY, 
   region_name VARCHAR2(25));

Command> CREATE WRITETHROUGH CACHE GROUP products_cg
  FROM sales.products 
  (prod_id INT NOT NULL, cust_id INT NOT NULL, 
   quantity_sold INT NOT NULL, time_id DATE NOT NULL, 
   PRIMARY KEY(prod_id, cust_id));

A TimesTen database can contain multiple cache groups. A cache group can contain one or more cache tables.

Creating indexes on a cache table in TimesTen can help speed up particular queries issued on the table in the same fashion as on a TimesTen regular table. You can create non-unique indexes on a TimesTen cache table. Do not create unique indexes on a cache table that do not match any unique index on the cached Oracle Database table. Otherwise, it can cause unique constraint failures in the cache table that do not occur in the cached Oracle Database table, and result in these tables in the two databases being no longer synchronized with each other when autorefresh operations are performed.

Single-Table Cache Group

The simplest cache group is one that caches a single Oracle Database table. In a single-table cache group, there is a root table but no child tables.

Figure 4-1 shows a single-table cache group target_customers that caches the customer table.

Figure 4-1 Cache Group With a Single Table

Description of "Figure 4-1 Cache Group With a Single Table"

Multiple-Table Cache Group

A multiple-table cache group is one that defines a root table and one or more child tables.

A cache group can only contain one root table. The root table does not reference any table with a foreign key constraint.

In a cache group with multiple cache tables on TimesTen, each child table must reference the primary key or a unique index of the root table or of another child table in the same cache group using a foreign key constraint. Cache tables defined in a multiple-table cache group must be related to each other in TimesTen through foreign key constraints. However, the corresponding tables in the Oracle database do not necessarily need to be related to each other. The tables on the Oracle database can be related:

Related through a foreign key constraint.
Related without a foreign key constraint. You may have tables on the Oracle database that are not related through a foreign key constraint. However, you want to cache the data within these separate tables on TimesTen. The user application could maintain a relationship between tables that is not enforced by foreign key constraints on the Oracle database.

Figure 4-2 shows a multiple-table cache group customer_orders that caches the customer, orders and order_item tables. Each parent table in the customer_orders cache group has a primary key that is referenced by a child table through a foreign key constraint. The customer table is the root table of the cache group because it does not reference any table in the cache group with a foreign key constraint. The primary key of the root table is considered the primary key of the cache group. The orders table is a child table of the customer root table. The order_item table is a child table of the orders child table.

Figure 4-2 Cache Group With Multiple Tables

Description of "Figure 4-2 Cache Group With Multiple Tables"

The table hierarchy in a multiple-table cache group can designate child tables to be parents of other child tables. A child table cannot reference more than one parent table. However, a parent table can be referenced by more than one child table.

Figure 4-3 shows an improper cache table hierarchy. Neither the customer nor the product table references a table in the cache group with a foreign key constraint. This results in the cache group having two root tables which is invalid.

Figure 4-3 Problem: Cache Group Contains Two Root Tables

Description of "Figure 4-3 Problem: Cache Group Contains Two Root Tables"

To resolve this problem and cache all the tables, create a cache group which contains the customer, orders, and order_item tables, and a second cache group which contains the product and the inventory tables as shown in Figure 4-4.

Figure 4-4 Solution: Create Two Cache Groups

Description of "Figure 4-4 Solution: Create Two Cache Groups"