Create a Cache Group

A cache group defines the set of Oracle 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 tables being cached.

In the Create cache group dialog box, specify a name for the cache group in the Name field within the Cache Group Attributes tab. In the Type pull-down menu, select the desired cache group type (read-only, asynchronous writethrough, synchronous writethrough or user managed).

Cache groups are categorized as either explicitly loaded or dynamic. If you are creating a dynamic cache group, check the Dynamic check box in the Type section. Otherwise, leave the check box unchecked if you are creating a explicitly loaded cache group.

In an explicitly loaded cache group, data is loaded manually into its cache tables from its corresponding Oracle tables using a load or refresh operation, or automatically using an automatic refresh operation. In a dynamic cache group, data is loaded into its cache tables on demand from its corresponding Oracle tables using a dynamic load operation, or manually using a load operation.

Cache groups are also classified as either local or global. If you are creating a global cache group, check the Global check box in the Type section. Otherwise, leave the check box unchecked if you are creating a local cache group.

In a local cache group, data in its cache tables are not shared across TimesTen databases even if the databases are members of the same cache grid. In a global cache group, data in its cache tables are shared across TimesTen databases that are members of the same cache grid.

Only a dynamic AWT cache group can be defined as global cache group.

Dynamic and global cache groups are supported in TimesTen 11g or later.

All cache groups in TimesTen 7.0 are categorized as explicitly loaded and classified as local.

In the DDL tab of the Create cache group dialog box, you can view the CREATE CACHE GROUP statement that will be used to create the cache group and its cache tables.

Click the Apply button to create the cache group and its cache tables.

If you created an AWT cache group, you must start the replication agent in order for committed updates on its cache tables to be asynchronously propagated to its corresponding Oracle tables.

If you created a global cache group, you must attach the TimesTen database to the cache grid in order for data in its cache tables to maintain consistency among the TimesTen databases within the grid. This task must be performed outside of SQL Developer using command-line interfaces such as the ttIsql utility, or within SQL Developer using SQL Worksheet.

See also the following related topics:

Set the Automatic Refresh Cache Group Attributes

Within the Cache Group Attributes tab of the Create cache group dialog box, if you are creating a read-only cache group, or a user managed cache group and you put a check in the Autorefresh check box within the Table Attributes section to enable automatic refresh when either the All tables readonly or Propagate all tables button is selected, fill in the following automatic refresh attributes:

Specify the Cache Table Attributes of a User Managed Cache Group

If you are creating a user managed cache group, select one of the following buttons from the Table Attributes section in the Cache Group Attributes tab within the Create cache group dialog box:

Define the Root Table

Click the Tables tab in the Create cache group dialog box. Right-click in the Tables panel and select Add root table.

In the list of Oracle tables shown within the Select the root table dialog box, select the table that you want to designate as the root table for your cache group and then click the OK button.

To show only tables owned by the Oracle user who has the same name as the TimesTen cache manager user, put a check in the Show only my tables check box.

See also the following related topics:

Select the Columns to Cache

In the Columns tab within the Tables tab of the Create cache group dialog box, put a check in the check boxes under the Cached field for each column that you want to cache. By default, all columns of the table are selected and will be cached. The Oracle table's unique identifying columns (primary key, unique index or unique constraint) for each row must be included in the TimesTen cache table and will constitute the cache table's primary key.

Put a check in the check boxes under the Allow null values field for each cached column that you want null values to be accepted in. The Oracle table's unique identifying columns cannot accept null values.

To select all columns, or deselect all columns except for the Oracle table's unique identifying columns, to be cached or accept null values, put (or do not put) a check in the check boxes that appear to the left of the Cached and Allow null values fields, respectively.

Some Oracle data types have more than one compatible TimesTen data type. For columns in the Oracle table with one of these Oracle data types, a pull-down menu appears under the Data type in TimesTen field for those columns. The pull-down menu allows you to select the desired data type for the column in the cache table. The non-default compatible or mapped data types appear in italics.

For updatable cache tables, you can specify a default column value under the Default Value field for each cached column that you want a default value defined.

Specify the Primary Key

By default, TimesTen creates a range index (called a T-tree index in TimesTen 7.0) on the cache table's primary key columns. To create a hash index instead of a range index on the primary key columns, in the Primary Key section of the Table Attributes tab within the Tables tab of the Create cache group dialog box, put a check in the Use hash check box. You will then need to size the hash index by specifying either the number of pages to allocate to the index or the maximum number of rows the underlying cache table is anticipated to have in the with number of pull-down menu and adjacent field after the colon (:) which requires a numeric value.

By default, the primary key columns of a cache table is the same as the primary key columns of the corresponding Oracle table. You can define a different column or set of columns as the primary key of the cache table.

In the Manual table management section of the Table Attributes tab with the Tables tab of the Create cache group dialog box, click the Redefine primary key button.

In the Define Primary Key(s) dialog box, the non-nullable unique columns of the corresponding Oracle table is shown in the Non-null unique columns list. Select the columns that you want the primary key to consist of and then click the >> button to move those columns into the Primary key(s) list. The order of a composite primary key's columns is significant if a range index is to be created on the columns. Click the OK button to define the new set of primary key columns.

Specify a WHERE Clause

You can specify an optional WHERE clause for the table in the WHERE clause field of the Table Attributes tab within the Tables tab of the Create cache group dialog box. A WHERE clause is only allowed for table definitions in a read-only cache group or a user managed cache group that has automatic refresh defined.

Specify the Attributes of User Managed Cache Group Tables

If the cache table is in a user managed cache group that has selected the Varies from table to table button in the Table attributes section within the Cache Group Attributes tab of the Create cache group dialog box, you need to specify the attribute for the table.

In the Propagation Type section of the Table Attributes tab within the Tables tab of the Create cache group dialog box, select the Readonly button to make the cache table read-only, the Propagate button to make the cache table updatable and updates on the table be propagated to its corresponding Oracle table, and the Neither button to make the cache table updatable and updates on the table not be propagated to its corresponding Oracle table.

By default, cache tables in a user managed cache group are updatable and updates on the table are not propagated to its corresponding Oracle table.

Specify an Aging Policy

You can specify an optional aging policy for the cache table in the Aging tab of the Create cache group dialog box. An aging policy is explicitly defined on the root table of a cache group but it implicitly applies to all its child tables as well.

Specify an LRU Aging Policy

Select the Usage-based button to define a least recently used (LRU) aging policy, the Time-based button to define a time-based aging policy, or the No aging button to define no aging policy.

An LRU aging policy can only be defined for tables in an explicitly loaded AWT, SWT or user managed cache group that does not have automatic refresh defined. LRU aging is defined by default for tables in a dynamic cache group.

The TimesTen database memory usage thresholds that determine when data starts and stops being removed from the tables are shown when the Usage-based button is selected. The default usage threshold that determines when data starts being removed from the tables is 90%. The default usage threshold that determines when data stops being removed from the tables is 80%. The default LRU aging cycle is 1 minute.

Specify an aging state by selecting either the on or off button. The default aging state is on.

Specify a Time-Based Aging Policy

A time-based aging policy can only be defined on a cache table that contains a non-nullable DATE or TIMESTAMP column. Columns of these data types for the table will appear in the Column pull-down menu within the Aging tab of the Create cache group dialog box when the Time-based button is selected. Select the column that you want to store the timestamp value indicating when each row was added or most recently updated.

Indicate the length of time in which data that has not been updated is to be kept in the table by specifying, in the Life-time field, a numeric value followed by a unit of minutes, hours or days in the pull-down menu.

Indicate the frequency at which data is to be aged out of the table by specifying, in the Cycle field, a numeric value followed by a unit of minutes, hours or days in the pull-down menu. The default time-based aging cycle is 5 minutes.

Specify an aging state by selecting either the on or off button. The default aging state is on.

Related Topics

TimesTen Tasks

SQL Developer Concepts and Usage

Define the Child Tables

Within the Tables tab of the Create cache group dialog box, right-click in the Tables panel and select Add child tables.

In the list of Oracle tables shown within the Select one or more child tables dialog box, select the tables that you want to designate as the child tables for your cache group and then click the OK button.

To select more than one child table, press and hold the CTRL key, and click the desired tables. The child tables will appear under their appropriate parent table.

Define the columns to cache, type of index to create on the primary key columns, cascade delete setting for the foreign key columns, the table attributes including an optional WHERE clause, and an optional aging policy for all child tables.

See also the following related topics:

Select the Columns to Cache

Click a child table and in the Columns tab within the Tables tab of the Create cache group dialog box, put a check in the check boxes under the Cached field for each column that you want to cache. By default, all columns of the table are selected and will be cached. The Oracle table's unique identifying columns for each row must be included in the TimesTen cache table and will constitute the cache table's primary key. The Oracle table's foreign key columns that reference its parent table must also be included in the cache table.

Put a check in the check boxes under the Allow null values field for each cached column that you want null values to be accepted in. The Oracle table's unique identifying columns cannot accept null values.

To select all columns, or deselect all columns except for the Oracle table's unique identifying columns and the foreign key columns that reference its parent table, to be cached or accept null values, put (or do not put) a check in the check boxes that appear to the left of the Cached and Allow null values fields, respectively.

For columns in the Oracle table with more than one compatible TimesTen data type, select the desired data type for the columns in the cache table from the pull-down menu that appears under the Data Type in TimesTen field for those columns.

For updatable cache tables, you can specify a default column value under the Default Value field for each cached column that you want a default value defined.

Specify the Primary Key and WHERE Clause

In the Table Attributes tab within the Tables tab of the Create cache group dialog box, specify whether to create a range index or a hash index on the child cache table's primary key columns, and define a different column or set of columns as the primary key of the cache table than the corresponding Oracle table.

You can specify an optional WHERE which is only allowed for table definitions in a read-only cache group or a user managed cache group that has automatic refresh defined.

Specify the Foreign Key

If you would like the foreign key columns of the child table to have cascade delete enabled such that when rows containing referenced key values are deleted from its parent table, rows in the child table with dependent foreign key values are also deleted, put a check in the Cascade delete foreign key check box.

By default, the foreign key columns of a child cache table is the same as the foreign key columns of the corresponding Oracle table. You can define a different column or set of columns as the foreign key of the cache table.

In the Manual table management section of the Table Attributes tab within the Tables tab of the Create cache group dialog box, click the Redefine foreign key button.

In the Define Foreign Key(s) dialog box, the list of columns in the child table whose data types are compatible with the primary key columns of the root table are shown in the Foreign Key(s) pull-down menu. Select the columns that you want the foreign key to consist of. Click the OK button to define the new set of foreign key columns.

Specify the Attributes of User Managed Cache Group Tables

If the cache table is in a user managed cache group that has selected the Varies from table to table button in the Table attributes section within the Cache Group Attributes tab of the Create cache group dialog box, select whether the cache table is read-only or updatable. If the table is updatable, specify whether updates on the table are to be or not to be propagated to its corresponding Oracle table.

Related Topics

TimesTen Tasks

SQL Developer Concepts and Usage