3 Creating Cache Groups
A cache group defines the set of Oracle 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 tables being cached.
This chapter includes topics:
-
Creating a cache group including:
Creating a cache group
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 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 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 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 In-Memory Database Cache User's Guide.
Cache groups are classified as either local or global. If you are creating a global cache group, then select the Global in the Type section.
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 an AWT cache group can be defined as global cache group.
For more information about local and global cache groups, see "Sharing data across a cache grid: Local and global cache groups" in the Oracle In-Memory Database Cache User's Guide.
Defining a cache group and cache table attributes
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 In-Memory Database Cache User's Guide.
For more information about read-only cache groups, see "Read-only cache group" in the Oracle In-Memory 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 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 tables
Figure 3-4 User managed cache group attributes
Description of "Figure 3-4 User managed cache group attributes"
For more information about user managed cache groups, see "User managed cache group" in the Oracle In-Memory Database Cache User's Guide.
Defining the root table
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 tables and Oracle synonyms that you can choose to designate as the root table for your cache group is displayed. Oracle tables that have primary keys and Oracle tables that have unique non-null indexes are available for selection.
Select the table and click OK.
Figure 3-6 List of tables and synonyms for root table
Description of "Figure 3-6 List of tables and synonyms for root table"
To show only Oracle tables owned by the current connection user, choose Show only my tables.
For more information on caching Oracle synonyms, see "Caching Oracle synonyms" in the Oracle In-Memory Database Cache User's Guide.
Selecting the columns to cache
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 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 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 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 In-Memory Database Cache User's Guide.
Some Oracle data types have multiple compatible TimesTen data types. For columns in the Oracle 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 data type appears in italics.
For more information about compatibility between Oracle and TimesTen data types, see "Mappings between Oracle and TimesTen data types" in the Oracle In-Memory 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 table's column.
Specifying the table attributes
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 with number of menu and the adjacent field after the colon (:) which requires a numeric value. The default selection is row.
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 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 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 table, then the tables may become unsynchronized when committed updates are made on the cache table or the corresponding Oracle table. Unique constraint violations can occur when:
-
Committed updates on the cache table are propagated to the corresponding Oracle table
-
Committed updates on the Oracle 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 In-Memory 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 table
-
Neither to define the cache table as updatable with updates on the table not propagated to the corresponding Oracle table
By default, cache tables in a user managed cache group are updatable with updates on the table not propagated to the corresponding Oracle table.
Specifying an aging policy
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 global 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".
For more information about defining an LRU aging policy on cache tables, see "LRU aging" in the Oracle In-Memory 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 In-Memory Database Cache User's Guide.
Verifying the root table definition
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 3-13 Verifying the cache group definition
Description of "Figure 3-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.
Defining the child tables
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 tables and Oracle synonyms that you can choose to designate as the child table(s) for your cache group. Oracle tables that have primary keys and Oracle 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 synonyms, see "Caching Oracle synonyms" in the Oracle In-Memory Database Cache User's Guide.
Figure 3-14 List of table and synonyms for child tables
Description of "Figure 3-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 tables owned by the current connection user, choose Show only my tables.
Selecting the columns to cache
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 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 table's unique identifying columns must be included in the TimesTen cache table and 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.
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 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 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.
Specifying the table attributes
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 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 3-15 Specifying attributes for child table
Description of "Figure 3-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 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 3-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 In-Memory 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 table.
Completing the definitions of the child tables
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.
Verifying the cache group definition
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 3-17 Viewing the cache group definition
Description of "Figure 3-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 tables. For information about how to start the replication agent, see "Starting the replication agent".
If you created a global cache group, then you must attach the TimesTen database to the cache grid that it is associated with before you can perform operations on the cache group or on its cache tables. This task must be performed either outside of SQL Developer using a command-line interface such as the ttIsql utility, or within SQL Developer using SQL Worksheet. See "Attaching a TimesTen database to a cache grid" in the Oracle In-Memory Database Cache User's Guide for more information.
Dropping a cache group
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 3-19 Drop cache group confirmation
Description of "Figure 3-19 Drop cache group confirmation"
For more information about dropping a cache group, see "Dropping a cache group" in the Oracle In-Memory Database Cache User's Guide.