About Cache Groups and Cache Tables

You use cache groups to define which data is to be cached in a TimesTen database from an Oracle database. A cache group can be defined to cache the whole of or just a part of a single Oracle database table or a set of related Oracle database tables. A single TimesTen database can contain multiple cache groups.

A cache group is a SQL object that encapsulates a set of one or more tables that are related through primary key and foreign key relationships. The single top-level table is called the root table and the other tables are below it in a hierarchical parent/child arrangement.

When you create a cache group, you specify one cache table for each Oracle table represented in the cache group. Each Oracle table must have a unique key (either a primary key or a unique index defined over one or more NOT NULL columns). However, you must include all columns of the unique key. You can define all or a subset of the columns of the Oracle table within its cache table when creating a cache group (subsets specified with a WHERE clause).

Decide on the following when creating a cache group that specifies one or more cache tables on the Oracle database.

1. Identify the schema or schemas and tables on the Oracle database that you want to cache in the TimesTen database. You are not required to cache all of the tables in each schema.

2. Create a cache group on the TimesTen database that specifies the Oracle tables that you want cached in the TimesTen database.

The figure below shows a fictional cache group called customer_orders that was created on the TimesTen database to cache a subset of the data that exists within the customers table located on the Oracle database. The customers table is a single table with no children. A subset of data in the customers table on the Oracle database is cached in the customer_orders cache group on the TimesTen database.

Description of the illustration qcachegroup1table.eps

When caching Oracle database tables:

• You can have a cache group that caches only a single Oracle database table. In a single-table cache group, there is a root table but no child tables.

• You can cache multiple related Oracle database tables in the same cache group by defining a root table and one or more child tables. A cache group can contain only one root table. The root table does not reference any table with a foreign key constraint.

In a cache group with multiple tables, each child table must reference the root table or 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 a TimesTen database 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:

• Through an explicit foreign key constraint.

• Without an explicit 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 the TimesTen database. The user application could maintain a relationship between tables that is not enforced by foreign key constraints on the Oracle database.