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.