A cache group defines the set of Oracle database data to cache in a 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.
Note:
TimesTen cache is not supported with TimesTen Scaleout databases.This chapter includes topics:
Creating a cache group including:
To create a cache group, right-click the Cache Groups node under the node of the connection name for the TimesTen database and select New Cache Group.
In the Create cache group dialog, specify a name for the cache group in the Name field within the Cache Group Attributes tab. For Type, choose 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, then choose Dynamic in the Type section.
In an explicitly loaded cache group, data is loaded manually into its cache tables from the corresponding Oracle database 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 the corresponding Oracle database tables using a dynamic load operation, or manually using a load operation.
A read-only, asynchronous writethrough (AWT) or synchronous writethrough (SWT) cache group can be defined as a dynamic cache group. A user managed cache group can be defined as a dynamic cache group if it does not contain all of the following characteristics:
Automatic refresh is defined.
At least one of its cache tables are updatable.
Committed updates on one or more of its updatable cache tables are propagated to the corresponding Oracle database tables.
For more information about explicitly loaded and dynamic cache groups, see "Loading data into a cache group: Explicitly loaded and dynamic cache groups" and "Dynamic cache groups" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
Within the Cache Group Attributes tab of the Create cache group dialog, if you are creating a user managed cache group and you select Autorefresh within the Table Attributes section to enable automatic refresh when either All tables readonly or Propagate all tables is selected, or you are creating a read-only cache group, then for the following automatic refresh characteristics:
Set the automatic refresh mode in the Mode field by choosing either Full or Incremental. The default automatic refresh mode is Incremental.
Set the automatic refresh interval in the Refresh Interval field by specifying a numeric value followed by a unit of time in minutes, seconds or milliseconds. The default automatic refresh interval is 5 minutes.
Set the automatic refresh state in the Refresh State field by choosing either Paused, On or Off. The default automatic refresh state is Paused.
For more information about automatic refresh of cache groups, see "AUTOREFRESH cache group attribute" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
For more information about read-only cache groups, see "Read-only cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
If you are creating a user managed cache group, then from the Table Attributes section in the Cache Group Attributes tab within the Create cache group dialog, choose one:
All tables readonly: All cache tables are read-only.
Propagate all tables: All cache tables are updatable and committed updates on the cache tables are propagated to the corresponding Oracle database tables.
Varies from table to table: Some cache tables are read-only and other cache tables are updatable where committed updates on the cache tables may or may not be propagated to the corresponding Oracle database tables.
Figure 9-4 User managed cache group attributes
For more information about user managed cache groups, see "User-managed cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
Click the Tables tab in the Create cache group dialog. Right-click in the Tables panel and select Add root table.
The Select the root table dialog appears. The list of Oracle database tables and synonyms that you can choose to designate as the root table for your cache group is displayed. Oracle database tables that have primary keys and Oracle database tables that have unique non-null indexes are available for selection.
Select the table and click OK.
Figure 9-6 List of tables and synonyms for root table
To show only Oracle database tables owned by the current connection user, choose Show only my tables.
For more information on caching Oracle database synonyms, see "Caching Oracle Database synonyms" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
In the Columns tab within the Tables tab of the Create cache group dialog, select the check boxes under the Inline field to specify the INLINE
attribute for the column. You can specify the INLINE
attribute for columns of type VARCHAR2
, NVARCHAR2
, and VARBINARY
.
Select the check boxes under the Allow null values field for each cached column to accept null values. By default, the nullability of a cache table's column is identical to the nullability of the corresponding Oracle database table's column.
Select the check boxes under the Cached field for each column to cache. By default, all columns of the table are selected and are cached. The Oracle database table's unique identifying columns (primary key, unique index or unique constraint) must be included in the TimesTen cache table and constitutes the cache table's primary key.
To select all columns or deselect all columns, except for the Oracle database table's unique identifying columns, to accept null values or to cache, select or deselect the check boxes that appear to the left of the Allow null values and Cached fields, respectively.
For more information about the characteristics of cache tables and cache groups, see "Cache groups and cache tables" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
Some Oracle database data types have multiple compatible TimesTen data types. For columns in the Oracle database table whose data type can map multiple TimesTen data types, a menu appears under the Data type in TimesTen field for those columns. Select the desired data type for the column in the cache table. The default compatible TimesTen data type for the Oracle database data type appears in italics.
For more information about compatibility between Oracle database and TimesTen data types, see "Mappings between Oracle Database and TimesTen data types" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
For updatable cache tables, you can specify a default column value under the Default Value field for each cached column to define a default value. By default, the default value of a cache table's column is identical to the default value of the corresponding Oracle database table's column.
By default, TimesTen creates a range index 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 Create cache group, choose Use hash. You must size the hash index by specifying either the maximum number of rows the underlying cache table is anticipated to have or the number of pages to allocate to the index in the Hash sizing policy menu and the adjacent field after the colon (:) which requires a numeric value. The default selection is Current table row count.
For more information about the index that is automatically created on a cache table's primary key columns, see "CREATE CACHE GROUP" in the Oracle TimesTen In-Memory Database SQL Reference.
For more information about properly sizing a hash index, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.
By default, the primary key columns of a cache table are identical to the primary key columns of the corresponding Oracle database table. You can define a different column or set of columns as the primary key for the cache table.
In the Manual table management section of the Table Attributes tab within the Tables tab of the Create cache group dialog, click Redefine primary key.
In the Define Primary Key(s) dialog, the non-nullable unique columns of the corresponding Oracle database table is shown in the Non-null unique columns list. Select the columns for the primary key of the cache table and then choose >> to move those columns into the Primary key(s) list. The order of the columns in a composite primary key is significant if a range index is to be created on the columns. Click OK to define the alternate primary key for the cache table.
If the primary key of the cache table consists of a different set of columns than the primary key of the corresponding Oracle database table, then the tables may become unsynchronized when committed updates are made on the cache table or the corresponding Oracle database table. Unique constraint violations can occur when:
Committed updates on the cache table are propagated to the corresponding Oracle database table.
Committed updates on the Oracle database table are refreshed to the corresponding cache table.
You can specify an optional WHERE
clause for the cache table in the WHERE clause field of the Table Attributes tab within the Tables tab of Create cache group. 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.
For more information about specifying a WHERE
clause in the table definition of a cache group, see "Using a WHERE clause" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
If the cache table is in a user managed cache group that has selected the Varies from table to table in the Table attributes section within the Cache Group Attributes tab of the Create cache group dialog, then you must specify the table attribute for the cache table.
In the Propagation Type section of the Table Attributes tab within the Tables tab of Create cache group, choose:
Readonly to define the cache table as read-only
Propagate to define the cache table as updatable with updates on the table to be propagated to the corresponding Oracle database table.
Neither to define the cache table as updatable with updates on the table not propagated to the corresponding Oracle database table.
By default, cache tables in a user managed cache group are updatable with updates on the table not propagated to the corresponding Oracle database table.
You can specify an aging policy for the cache table in the Aging tab of the Create cache group dialog. An aging policy is explicitly defined on the root table of a cache group and it implicitly applies to all the child tables.
Choose:
Usage-based to define a least recently used (LRU) aging policy
Time-based to define a time-based aging policy
No aging to not define an aging policy
An LRU aging policy can be defined for tables in an explicitly loaded AWT, SWT or user managed cache group that does not have automatic refresh defined. The default aging state is set to off for explicitly loaded cache groups. For dynamic cache groups that do not have automatic refresh defined, the default aging state is set to on and the type of aging is set to usage-based.
The TimesTen database memory usage thresholds that determine when data starts and stops being deleted from tables that are subject to LRU aging are shown when the Usage-based is selected. The default usage threshold that determines when data starts being deleted from the tables is 90%. The default usage threshold that determines when data stops being deleted from the tables is 80%. The default LRU aging cycle is 1 minute.
For information about how to change the memory usage thresholds and LRU aging cycle, see "Changing the memory usage thresholds and LRU aging cycle".
Figure 9-11 Specifying usage-based aging
For more information about defining an LRU aging policy on cache tables, see "LRU aging" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
A time-based aging policy can be defined on a cache table that contains a non-nullable DATE
or TIMESTAMP
column. Columns in the table of these data types appear in the Column within the Aging tab of the Create cache group dialog when Time-based is selected. Select the column to store the timestamp value indicating when each row was inserted or most recently updated.
In the Life-time field, indicate the length of time in which data that has not been updated is to be kept in the table by specifying a numeric value followed by a unit of time in minutes, hours or days from the drop-down menu list.
In the Cycle field, indicate the frequency in which data is to be aged out of the table by specifying a numeric value followed by a unit of time in minutes, hours or days in the drop-down menu list. The default time-based aging cycle is 5 minutes.
Specify an aging state by choosing on or off.
For more information about defining a time-based aging policy on cache tables, see "Time-based aging" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
After you have defined the tables and columns to cache, the type of index to create on the primary key columns, the table attributes including an optional WHERE
clause, and an optional aging policy for the root table, you can view the CREATE CACHE GROUP
statement used to create the cache group and its cache table in the DDL tab of the Create cache group dialog.
Click Apply to create the cache group and its cache table if you are not going to add any child tables to the cache group.
Figure 9-13 Verifying the cache group definition
For information about the syntax and semantics of the CREATE CACHE GROUP
statement, see "CREATE CACHE GROUP" in the Oracle TimesTen In-Memory Database SQL Reference.
Within the Tables tab of the Create cache group dialog, right-click in the Tables panel and select Add child tables.
The Select one or more child tables dialog appears. This dialog displays the list of Oracle database tables and Oracle database synonyms that you can choose to designate as the child table(s) for your cache group. Oracle database tables that have primary keys and Oracle database tables that have unique non-null indexes are available for selection.
Select the tables to designate as the child tables for your cache group and click OK.
For more information on caching Oracle database synonyms, see "Caching Oracle Database synonyms" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
Figure 9-14 List of table and synonyms for child tables
To select multiple child tables, press and hold the CTRL key, and click the desired tables. The child tables appear under their appropriate parent table. You can also drag and drop a particular child table so that it appears under the desired parent table.
To show only Oracle database tables owned by the current connection user, choose Show only my tables.
Click a child table and in the Columns tab within the Tables tab of the Create cache group dialog, select the check boxes under the Allow null values field for each cached column to accept null values. By default, the nullability of a cache table's column is identical to the nullability of the corresponding Oracle database table's column.
Select the check boxes under the Cached field for each column to cache. By default, all columns of the table are selected and are cached. The Oracle database table's unique identifying columns must be included in the TimesTen cache table and constitute the cache table's primary key. The Oracle database table's foreign key columns that reference its parent table must also be included in the cache table.
To select all columns or deselect all columns, except for the Oracle database table's unique identifying columns and the foreign key columns that reference its parent table, to accept null values or to cache, select or deselect the check boxes that appear to the left of the Allow null values and Cache fields.
For columns in the Oracle database table whose data type can map to multiple TimesTen data types, a drop-down select list appears under the Data type in TimesTen field for those columns. Select the desired data type for the column in the child cache table.
For updatable cache tables, you can specify a default column value under the Default Value field for each cached column in the child cache table that defines a default value.
In the Table Attributes tab within the Tables tab of the Create cache group dialog, select whether to create a range index or a hash index on the child cache table's primary key columns in a similar fashion as was specified for the root table. You can define a different column or set of columns as the primary key for the cache table than the columns of the corresponding Oracle database table in a similar fashion as was specified for the root table. You can also specify an optional WHERE
clause for the cache table in a similar fashion as was specified for the root table. 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.
Figure 9-15 Specifying attributes for child table
If you would like the foreign key columns of a 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 automatically deleted, then select Cascade delete foreign key.
By default, the foreign key columns of a child cache table are identical to the foreign key columns of the corresponding Oracle database table. You can define a different column or set of columns as the foreign key for the cache table.
In the Manual table management section of the Table Attributes tab within the Tables tab of the Create cache group dialog, select Redefine foreign key. See Figure 9-15, "Specifying attributes for child table".
In the Define Foreign Key(s) dialog, the columns in the child table whose data types are compatible with the primary key columns of the parent table are shown in the Foreign Key(s) drop-down select list. Select the columns for the foreign key of the cache table. Click OK to define the alternate foreign key for the cache table.
For more information about the hierarchy and relationship of cache tables in a multiple-table cache group, see "Multiple-table cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
If the child cache table is in a user managed cache group that has selected Varies from table to table in the Table attributes section within the Cache Group Attributes tab of the Create cache group dialog, then you must specify the table attribute for the cache table in a similar fashion as was specified for the root table. If the cache table is updatable, then specify whether updates on the table are to be propagated or not propagated to the corresponding Oracle database table.
Define the columns to cache, the type of index to create on the primary key columns, the cascade delete setting for the foreign key columns, and the table attributes including an optional WHERE clause for any remaining child tables.
In the DDL tab of the Create cache group dialog, you can view the CREATE CACHE
GROUP
statement used to create the cache group and its cache tables.
Click Apply to create the cache group and its cache tables.
Figure 9-17 Viewing the cache group definition
If you created an AWT cache group, then you must start the replication agent for committed updates on its cache tables to be asynchronously propagated to the corresponding Oracle database tables. For information about how to start the replication agent, see "Starting the replication agent".
When you drop a cache group, its cache tables are also dropped.
Under the Cache Groups node within the node of the connection name for the TimesTen database, right-click the name of the cache group to drop and select Drop.
Click Apply to drop the cache group.
Figure 9-19 Drop cache group confirmation
For more information about dropping a cache group, see "Dropping a cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.