4 Defining Cache Groups

The following sections describe the different types of cache groups and how to define them:

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 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.

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. Create the job_history table with a composite key on the Oracle database:

    SQL> 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 TimesTen with all columns of the composite primary key:

    Command> CREATE WRITETHROUGH CACHE GROUP job_hist_cg
           > FROM oratt.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 create Oracle Database unique indexes 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 sales(
          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 sales_index ON sales(prod_id, cust_id);
    Index created.
    

    After creation of the Oracle Database table and unique index, you can create cache groups on TimesTen for these tables using the unique index columns as the primary key definition as shown below:

    Command> CREATE WRITETHROUGH CACHE GROUP region_cg
     > FROM oratt.regions
     > (region_id NUMBER NOT NULL PRIMARY KEY, 
     >  region_name VARCHAR2(25));
    
    Command> CREATE WRITETHROUGH CACHE GROUP sales_cg
     > FROM oratt.sales 
     > (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. An Oracle Database table cannot be cached in more than one cache group within the same TimesTen database.

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.

Single-table cache group

The simplest cache group is one that caches a single Oracle Database table. In a single-table cache group, there is a root table but no child tables.

Figure 4-1 shows a single-table cache group target_customers that caches the customer table.

Figure 4-1 Cache group with a single table

Description of Figure 4-1 follows
Description of "Figure 4-1 Cache group with a single table"

Multiple-table cache group

A multiple-table cache group is one that defines a root table and one or more child tables. A cache group can only contain one root table. Each child table must reference the primary key or a unique index of the root table or of another child table in the cache group using a foreign key constraint. Although tables in a multiple-table cache group must be related to each other in the TimesTen database through foreign key constraints, it is not required that the tables be related to each other in the Oracle database. The root table does not reference any table in the cache group with a foreign key constraint.

Figure 4-2 shows a multiple-table cache group customer_orders that caches the customer, orders and order_item tables. Each parent table in the customer_orders cache group has a primary key that is referenced by a child table through a foreign key constraint. The customer table is the root table of the cache group because it does not reference any table in the cache group with a foreign key constraint. The primary key of the root table is considered the primary key of the cache group. The orders table is a child table of the customer root table. The order_item table is a child table of the orders child table.

Figure 4-2 Cache group with multiple tables

Description of Figure 4-2 follows
Description of "Figure 4-2 Cache group with multiple tables"

The table hierarchy in a multiple-table cache group can designate child tables to be parents of other child tables. A child table cannot reference more than one parent table. However, a parent table can be referenced by more than one child table.

Figure 4-3 shows an improper cache table hierarchy. Neither the customer nor the product table references a table in the cache group with a foreign key constraint. This results in the cache group having two root tables which is invalid.

Figure 4-3 Problem: Cache group contains two root tables

Description of Figure 4-3 follows
Description of "Figure 4-3 Problem: Cache group contains two root tables"

To resolve this problem and cache all the tables, create a cache group which contains the customer, orders, and order_item tables, and a second cache group which contains the product and the inventory tables as shown in Figure 4-4.

Figure 4-4 Solution: Create two cache groups

Description of Figure 4-4 follows
Description of "Figure 4-4 Solution: Create two cache groups"

Creating a cache group

You create cache groups by using a CREATE CACHE GROUP SQL statement or by using Oracle SQL Developer, a graphical tool. For more information about SQL Developer, see Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.

Cache groups are identified as either system managed or user managed. System managed cache groups enforce specific behaviors, while the behavior of a user managed cache group can be customized. System managed cache group types include:

See "User managed cache group" for information about user managed cache groups.

The following topics also apply to creating a cache group:

Cache groups must be created by and are owned by the cache manager user.

You cannot cache Oracle Database data in a temporary database.

Read-only cache group

A read-only cache group enforces a caching behavior where the TimesTen cache tables cannot be updated directly, and committed updates on the cached Oracle Database tables are automatically refreshed to the cache tables as shown in Figure 4-5.

Figure 4-5 Read-only cache group

Description of Figure 4-5 follows
Description of "Figure 4-5 Read-only cache group"

If the TimesTen database is unavailable for whatever reason, you can still update the Oracle Database tables that are cached in a read-only cache group. When the TimesTen database returns to operation, updates that were committed on the cached Oracle Database tables while the TimesTen database was unavailable are automatically refreshed to the TimesTen cache tables.

Note:

When TimesTen manages operations for read only cache groups, it connects to the Oracle database using the cache administration user name and password set with the ttCacheUidPwdSet built-in procedure. For more details on ttCacheUidPwdSet, see "Set the cache administration user name and password".

The following are the definitions of the Oracle Database tables that are to be cached in the read-only cache groups that are defined in Example 4-1, Example 4-12, Example 4-13, Example 4-21 and Example 4-22. The Oracle Database tables are owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

CREATE TABLE orders
(ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
 cust_num     NUMBER(6) NOT NULL,
 when_placed  DATE NOT NULL,
 when_shipped DATE NOT NULL);

The companion Oracle Database user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.customer and oratt.orders tables in order for the cache manager user to create a read-only cache group that caches these tables, and for autorefresh operations to occur from the cached Oracle Database tables to the TimesTen cache tables.

Use the CREATE READONLY CACHE GROUP statement to create a read-only cache group.

Example 4-1 Creating a read-only cache group

The following statement creates a read-only cache group customer_orders that caches the tables oratt.customer (root table) and oratt.orders (child table):

CREATE READONLY CACHE GROUP customer_orders
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num)),
oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));

The cache tables in a read-only cache group cannot be updated directly. However, you can set the passthrough level to 2 to allow committed update operations issued on a TimesTen cache table to be passed through and processed on the cached Oracle Database table, and then have the updates be automatically refreshed into the cache table. See "Setting a passthrough level".

The effects of a passed through statement on cache tables in a read-only cache group do not occur in the transaction in which the update operation was issued. Instead, they are seen after the passed through update operation has been committed on the Oracle database and the next automatic refresh of the cache group has occurred. The companion Oracle Database user of the TimesTen cache manager user must be granted the INSERT, UPDATE and DELETE privileges on the Oracle Database tables that are cached in the read-only cache group in order for the passed through update operations to be processed on the cached Oracle Database tables.

If you manually created the Oracle Database objects used to enforce the predefined behaviors of an autorefresh cache group as described in "Manually create Oracle Database objects used to manage data caching", you need to set the autorefresh state to OFF when creating the cache group.

Then you need to run the ttIsql utility's cachesqlget command to generate a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle Database table that is cached in the read-only cache group. See "Manually creating Oracle Database objects for autorefresh cache groups" for information about how to create these objects.

Restrictions with read-only cache groups

The following restrictions apply when using a read-only cache group:

  • The cache tables on TimesTen cannot be updated directly.

  • Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.

    See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE cache table attribute.

    See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON cache table attribute.

  • A FLUSH CACHE GROUP statement cannot be issued on the cache group.

    See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.

  • A TRUNCATE TABLE statement issued on a cached Oracle Database table is not automatically refreshed to the TimesTen cache table.

  • A LOAD CACHE GROUP statement can only be issued on the cache group if the cache tables are empty, unless the cache group is dynamic.

    See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP statement.

    See "Dynamic cache groups" for more information about dynamic cache groups.

  • The autorefresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement on the cache group, unless the cache group is dynamic, in which case the autorefresh state must be PAUSED or ON. The LOAD CACHE GROUP statement cannot contain a WHERE clause, unless the cache group is dynamic, in which case the WHERE clause must be followed by a COMMIT EVERY n ROWS clause.

    See "AUTOREFRESH cache group attribute" for more information about autorefresh states.

    See "Using a WHERE clause" for more information about WHERE clauses in cache group definitions and operations.

  • The autorefresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement on the cache group. The REFRESH CACHE GROUP statement cannot contain a WHERE clause.

    See "Loading and refreshing a cache group" for more information about the REFRESH CACHE GROUP statement.

  • All tables and columns referenced in WHERE clauses when creating, loading or unloading the cache group must be fully qualified. For example:

    user_name.table_name and user_name.table_name.column_name

  • Least recently used (LRU) aging cannot be specified on the cache group, unless the cache group is dynamic where LRU aging is defined by default.

    See "LRU aging" for more information about LRU aging.

  • Read-only cache groups cannot cache Oracle Database views or materialized views.

Asynchronous writethrough (AWT) cache group

An asynchronous writethrough (AWT) cache group enforces a caching behavior where committed updates on the TimesTen cache tables are automatically and asynchronously propagated to the cached Oracle Database tables as shown in Figure 4-6.

Note:

You should avoid executing DML statements on Oracle Database tables cached in an AWT cache group. This can result in an error condition. For more information, see "Restrictions with AWT cache groups".

Figure 4-6 Asynchronous writethrough cache group

Description of Figure 4-6 follows
Description of "Figure 4-6 Asynchronous writethrough cache group"

Since an AWT cache group propagates data from the TimesTen database to the Oracle database, any data modified by the user in the cached tables on the Oracle database is not automatically uploaded from the Oracle database to the TimesTen database. In this case, you must explicitly unload and then reload the AWT cache groups on TimesTen.

The transaction commit on the TimesTen database occurs asynchronously from the commit on the Oracle database. This enables an application to continue issuing transactions on the TimesTen database without waiting for the Oracle Database transaction to complete. However, your application cannot ensure when the transactions are completed on the Oracle database.

Execution of the UNLOAD CACHE GROUP statement for an AWT cache group waits until updates on the rows have been propagated to the Oracle database.

You can update cache tables in an AWT cache group even if the Oracle database is unavailable. When the Oracle database returns to operation, updates that were committed on the cache tables while the Oracle database was unavailable are automatically propagated to the cached Oracle Database tables.

If there are updates from DML statements that you do not want propagated to the Oracle database, then you can disable propagation of committed updates (as a result of executing DML statements) within the current transaction to the Oracle database by setting the flag in the ttCachePropagateFlagSet built-in procedure to zero. After the flag is set to zero, the effects of executing any DML statements are never propagated to the back-end Oracle database. Thus, these updates exist only on the TimesTen database. You can then re-enable propagation by resetting the flag to one with the ttCachePropagateFlagSet built-in procedure. After the flag is set back to one, propagation of all committed updates to the Oracle database resumes. The propagation flag automatically resets to one after the transaction is committed or rolled back. See "ttCachePropagateFlagSet" in the Oracle TimesTen In-Memory Database Reference for more details.

Note:

When TimesTen manages operations for AWT cache groups, it connects to the Oracle database using the cache administration user name and password set with the ttCacheUidPwdSet built-in procedure. For more details on ttCacheUidPwdSet, see "Set the cache administration user name and password".

The following is the definition of the Oracle Database table that is to be cached in the AWT cache groups that are defined in Example 4-2, Example 4-14 and Example 4-16. The Oracle Database table is owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

The companion Oracle Database user of the TimesTen cache manager user must be granted the SELECT privilege on the oratt.customer table in order for the cache manager user to create an AWT cache group that caches this table. The cache administration user must be granted the INSERT, UPDATE and DELETE Oracle Database privileges on the oratt.customer table for asynchronous writethrough operations to be applied to the Oracle Database.

Use the CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement to create an AWT cache group.

Example 4-2 Creating an AWT cache group

The following statement creates an asynchronous writethrough cache group new_customers that caches the oratt.customer table:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num));

The following sections describe configuration, behavior, and management for AWT cache groups:

Managing the replication agent

Performing asynchronous writethrough operations requires that the replication agent be running on the TimesTen database that contains AWT cache groups. Executing a CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement creates a replication scheme that enables committed updates on the TimesTen cache tables to be asynchronously propagated to the cached Oracle Database tables.

After you have created AWT cache groups, start the replication agent on the TimesTen database.

Example 4-3 Starting the replication agent

The replication agent can be manually started programmatically by calling the ttRepStart built-in procedure as the cache manager user:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttRepStart;

It can also be started from a command line by running a ttAdmin -repStart utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -repStart cachealone1

The replication agent does not start unless there is at least one AWT cache group or replication scheme in the TimesTen database.

If the replication agent is running, it must be stopped before you can issue another CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement or a DROP CACHE GROUP statement on an AWT cache group.

Example 4-4 Stopping the replication agent

The replication agent can be manually stopped programmatically by calling the ttRepStop built-in procedure as the cache manager user:

Command> call ttRepStop;

It can also be stopped from a command line by running a ttAdmin -repStop utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -repStop cachealone1

You can set a replication agent start policy to determine how and when the replication agent process starts on a TimesTen database.

The default start policy is manual which means the replication agent must be started manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command. To manually stop a running replication agent process, call the ttRepStop built-in procedure or run a ttAdmin -repStop utility command.

The start policy can be set to always so that the replication agent starts automatically when the TimesTen main daemon process starts. With the always start policy, the replication agent cannot be stopped when the main daemon is running unless the start policy is changed to either manual or norestart and then a manual stop is issued by calling the ttRepStop built-in procedure or running a ttAdmin -repStop utility command.

With the manual and always start policies, the replication agent automatically restarts after a failure such as a database invalidation.

The start policy can be set to norestart which means the replication agent must be started manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command, and stopped manually by calling the ttRepStop built-in procedure or running a ttAdmin -repStop utility command.

With the norestart start policy, the replication agent does not automatically restart after a failure such as a database invalidation. You must restart the replication agent manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command.

Example 4-5 Setting a replication agent start policy

As the instance administrator, grant the ADMIN privilege to the cache manager user:

% ttIsql cachealone1
Command> GRANT ADMIN TO cacheuser;
Command> exit

The replication agent start policy can be set programmatically by calling the ttRepPolicySet built-in procedure as the cache manager user:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttRepPolicySet('manual');
Command> exit

It can also be set from a command line by running a ttAdmin -repPolicy utility command as a TimesTen external user with the ADMIN privilege:

% ttAdmin -repPolicy always cachealone1

Since the AWT cache group uses the replication agent to asynchronously propagate transactions to the Oracle database, these transactions remain in the transaction log buffer and transaction log files until the replication agent confirms they have been fully processed by the Oracle database. You can monitor the propagation for these transactions with the ttLogholds built-in procedure. When you call the ttLogHolds built-in procedure, the description field contains "_ORACLE" to identify the transaction log hold for the AWT cache group propagation.

Command> call ttLogHolds();
< 0, 18958336, Checkpoint                    , cachealone1.ds0 >
< 0, 19048448, Checkpoint                    , cachealone1.ds1 >
< 0, 19050904, Replication                   , ADC6160529:_ORACLE >
3 rows found.

For more details on the ttLogHolds built-in procedure and how to monitor replication through bookmarks and log sequence numbers, see the "Show replicated log holds" section in the Oracle TimesTen In-Memory Database Replication Guide.

Configuring parallel propagation to Oracle Database tables

To improve throughput for an AWT cache group, you can configure multiple threads that act in parallel to propagate and apply transactional changes to the Oracle database. Parallel propagation enforces transactional dependencies and applies changes in AWT cache tables to Oracle Database tables in commit order.

Parallel propagation is supported for AWT cache groups with the following configurations:

  • AWT cache groups in a cache grid

  • AWT cache groups involved in an active standby pair replication scheme

  • AWT cache groups in a single TimesTen database (without a replication scheme configuration)

  • AWT cache groups configured with any aging policy

The following data store attributes enable parallel propagation and control the number of threads that operate in parallel to propagate changes from AWT cache tables to the corresponding Oracle Database tables:

  • ReplicationApplyOrdering enables parallel propagation by default.

  • ReplicationParallelism defines the number of transmitter threads on the source database and the number of receiver threads on the target database for parallel replication in a replication scheme. This value can be between 2 and 32 when used solely for parallel replication. The default is 1. In addition, the value of ReplicationParellelism cannot exceed half the value of LogBufParallelism.

  • CacheAWTParallelism, when set, determines the number of threads used in parallel propagation of changes from AWT cache tables to the Oracle Database tables. Set this attribute to a number from 2 to 31. The default is 1.

Parallel propagation for an AWT cache group is configured with one of the following scenarios:

  • ReplicationApplyOrdering is set to 0 and ReplicationParallelism is greater than 1.

    If you do not set CacheAWTParallelism, the number of threads that apply changes to Oracle Database is 2 times the setting for ReplicationParallelism. For example, if ReplicationParallelism=3, the number of threads that apply changes to Oracle Database tables is 6. In this case, ReplicationParallelism can only be set from 2 to 16; otherwise, twice the value would exceed the maximum number of 31 threads for parallel propagation. If the value is set to 16, the maximum number of threads defaults to 31.

  • ReplicationApplyOrdering is set to 0, ReplicationParallelism is equal to or greater than 1, and CacheAWTParallelism is greater than 1. The value for CacheAWTParallelism must be greater than or equal to the value set for ReplicationParallelism and less than or equal to 31.

    If CacheAWTParallelism is not specified, then ReplicationParallelism is used to determine the number of threads that are used for parallel propagation to Oracle Database. However, since this value is doubled for parallel propagation threads, you can only set ReplicationParallelism to a number from 2 to 16. If the value is set to 16, the maximum number of threads defaults to 31.

    If both ReplicationParallelism and CacheAWTParallelism attributes are set, the value set in CacheAWTParallelism configures the number of threads used for parallel propagation. The setting for CacheAWTParallelism determines the number of apply threads for parallel propagation and the setting for ReplicationParallelism determines the number of threads for parallel replication. Thus, if ReplicationParallelism is set to 4 and CacheAWTParallelism is set to 6, then the number of threads that apply changes to Oracle Database tables is 6. This enables the number of threads used to be different for parallel replication and parallel propagation to Oracle Database tables.

Note:

For more information about parallel replication, see "Configuring parallel replication" in the Oracle TimesTen In-Memory Database Replication Guide.

For more details on these data store attributes, see "ReplicationApplyOrdering," "ReplicationParallelism," and "CacheAWTParallelism" in the Oracle TimesTen In-Memory Database Reference.

These data store attributes are interrelated. Table 4-1 shows the result with the combination of the various possible attribute values.

Table 4-1 Results of Parallel Propagation Data Store Attribute Relationships

ReplicationApply Ordering ReplicationParallelism CacheAWTParallelism Number of parallel propagation threads

Set to 0, which enables parallel propagation

Set to > 1 for multiple tracks and <= 16.

Not specified.

Set to twice the value of ReplicationParallelism.

Set to 0, which enables parallel propagation

Set to > 16 and <= 32 for multiple tracks.

Not specified.

Error is thrown. If CacheAWTParallelism is not set, then 2 times the value set in ReplicationParallelism specifies the number of threads. Thus, in this case, ReplicationParallelism cannot be greater than 16.

Set to 0, which enables parallel propagation

Set to > 1 and <= 32 for multiple tracks.

Set to >= to ReplicationParallelism.

Set to number specified by CacheAWTParallelism.

Set to 0, which enables parallel propagation

Set to > 1 and <= 32 for multiple tracks.

Set to < ReplicationParallelism.

Error is thrown at database creation. The CacheAWTParallelism must be set to a value greater than or equal to ReplicationParallelism.

Set to 0, which enables parallel propagation

Set to 1 or not specified. Single track.

Set to > 1

Set to number specified by CacheAWTParallelism.

Set to 1, which disables parallel propagation.

N/A

Set to > 1

Error is thrown at database creation, since parallelism is turned off, but CacheAWTParallelism is set to a value, expecting parallel propagation to be enabled.


Foreign keys in Oracle Database tables that are to be cached must have indexes created on the foreign keys. Consider these Oracle Database tables:

CREATE TABLE parent (c1 NUMBER PRIMARY KEY NOT NULL);
CREATE TABLE child (c1 NUMBER PRIMARY KEY NOT NULL, 
                    c2 NUMBER REFERENCES parent(c1));
CREATE TABLE grchild (c1 NUMBER PRIMARY KEY NOT NULL, 
                      c2 NUMBER REFERENCES parent(c1), 
                      c3 NUMBER REFERENCES parent(c1));

These indexes must be created:

CREATE INDEX idx_1 ON child(c2);
CREATE INDEX idx_2 ON grchild(c2);
CREATE INDEX idx_3 ON grchild(c3);
Table constraint restrictions when using parallel propagation for AWT cache groups

When you use parallel propagation for AWT cache groups, you must manually enforce data consistency. Any unique index, unique constraint, or foreign key constraint that exists on columns in the Oracle Database tables that are to be cached should also be created on the AWT cache tables within TimesTen. If you cannot create these constraints on the AWT cache tables and you have configured for parallel propagation, then TimesTen serializes any transactions with DML operations to any table with missing constraints. For example, if a unique index created on a table in the Oracle database cannot be created on the corresponding cached table in TimesTen, all transactions for this table are serialized.

TimesTen automatically checks for missing constraints on the Oracle database that are not cached on TimesTen when you issue any of the following SQL statements:

  • When you create an AWT cache group with the CREATE ASYNCHRONOUS CACHE GROUP statement

  • When you create a unique index on an AWT cache table with the CREATE UNIQUE INDEX statement

  • When you drop a unique index on an AWT cache table with the DROP INDEX statement

Note:

You can manually initiate a check for missing constraints with the ttCacheCheck built-in procedure. For example, TimesTen does not automatically check for missing constraints after a schema change on cached Oracle Database tables. After any schema change on the Oracle database, you should perform an manual check for missing constraints by executing ttCacheCheck on the TimesTen database.

See "Manually initiate check for missing constraints" for other conditions where you should manually check for missing constraints.

If the check notes missing constraints on the cached tables, TimesTen issues warnings about each missing constraint.

For the following scenarios, the cached table is marked so that transactions that include DML operations are serialized when propagated to the Oracle database.

  • Transactions that apply DML operations to AWT cache tables that are missing unique indexes or unique constraints.

  • Missing foreign key constraints for tables within a single AWT cache group.

    • If both the referencing table and the referenced table for the foreign key relationship are in the same AWT cache group and the foreign key relationship is not defined, both tables are marked for transaction serialization.

    • If the referencing table is in an AWT cache group and the referenced table is not in an AWT cache group, the table inside the cache group is not marked for transaction serialization. Only a warning is issued to notify the user of the missing constraint.

    • If the referenced table is in an AWT cache group and the referencing table is not in an AWT cache group, the table inside the cache group is not marked for transaction serialization. Only a warning is issued to notify the user of the missing constraint.

  • Missing foreign key constraints between cache groups. When you have tables defined in separate AWT cache groups that are missing a foreign key constraint, both tables are marked for serialized transactions.

  • If a missing foreign key constraint causes a chain of foreign key constraints to be broken between two AWT cache groups, transactions for all tables within both AWT cache groups are serialized.

Note:

An Oracle Database trigger may introduce an operational dependency of which TimesTen may not be aware. In this case, you should either disable parallel propagation for the AWT cache group or do not cache the table in an AWT cache group on which the trigger is created.

Example 4-6 Examples of missing constraints when creating an AWT cache group

The following example creates two tables in the oratt schema in the Oracle database. There is a foreign key relationship between active_customer and the ordertab tables. Because the examples use these tables for parallel propagation, an index is created on the foreign key in the ordertab table.

SQL> CREATE TABLE active_customer
       (custid NUMBER(6) NOT NULL PRIMARY KEY,
        name VARCHAR2(50),
        addr VARCHAR2(100),
        zip VARCHAR2(12),
        region VARCHAR2(12) DEFAULT 'Unknown');
Table created.
 
SQL> CREATE TABLE ordertab
       (orderid NUMBER(10) NOT NULL PRIMARY KEY,
        custid NUMBER(6) NOT NULL);
Table created.
 
SQL> ALTER TABLE ordertab 
      ADD CONSTRAINT cust_fk 
       FOREIGN KEY (custid) REFERENCES active_customer(custid);
Table altered.

SQL> CREATE INDEX order_idx on ordertab (custid);

TimesTen automatically checks for missing constraints when each CREATE CACHE GROUP is issued. In the following example, a single cache group is created that includes the active_customer table. Only a warning is issued since the active_customer is the referenced table and the referencing table, ordertab, is not in any AWT cache group. The active_customer table is not marked for serialized transactions.

CREATE WRITETHROUGH CACHE GROUP update_cust
 FROM oratt.active_customer
 (custid NUMBER(6) NOT NULL PRIMARY KEY,
 name VARCHAR2(50),
 addr VARCHAR2(100),
 zip VARCHAR2(12));
Warning  5297: The following Oracle foreign key constraints on AWT cache table 
ORATT.ACTIVE_CUSTOMER contain cached columns that do not have corresponding 
foreign key constraints on TimesTen: ORATT.CUST_FK [Outside of CG].

The following example creates two AWT cache groups on TimeTen, one that includes the active_customer table and the other includes the ordertab table. There is a missing foreign key constraint between the cache groups. Thus, a warning is issued for both tables, but only the ordertab table is marked for serial transactions since it is the referencing table that should contain the foreign key.

CREATE WRITETHROUGH CACHE GROUP update_cust
 FROM oratt.active_customer
 (custid NUMBER(6) NOT NULL PRIMARY KEY,
 name VARCHAR2(50),
 addr VARCHAR2(100),
 zip VARCHAR2(12);
Warning  5297: The following Oracle foreign key constraints on AWT cache table 
oratt.update_customer contain cached columns that do not have corresponding 
foreign key constraints on TimesTen: ordertab.cust_fk [Outside of CG].

CREATE WRITETHROUGH CACHE GROUP update_orders
 FROM oratt.ordertab
 (orderid NUMBER(10) NOT NULL PRIMARY KEY,
  custid NUMBER(6) NOT NULL);
Warning  5295: Propagation will be serialized on AWT cache table 
ORATT.ORDERTAB because the following Oracle foreign key constraints on this 
table contain cached columns that do not have corresponding foreign key 
constraints on TimesTen: ORDERTAB.CUST_FK [Across AWT cache groups].
Manually initiate check for missing constraints

The ttCacheCheck built-in procedure performs the same check for missing constraints for cached tables on the Oracle database as performed automatically by TimesTen. The ttCacheCheck provides appropriate messages about missing constraints and the tables marked for serialized propagation. With the ttCacheCheck built-in procedure, you can check for missing constraints for a given cache group or for all cache groups in TimesTen to ensure that all cache groups are not missing constraints.

Note:

Since ttCacheCheck updates system tables to indicate if DML executed against a table should or should not be serialized, you must commit or roll back after the ttCacheCheck built-in completes.

For more details of the ttCacheCheck built-in procedure, see "ttCacheCheck" in the Oracle TimesTen In-Memory Database Reference.

You may need to manually call the ttCacheCheck built-in procedure to update the known dependencies after any of the following scenarios:

  • After dropping a series of AWT cache groups on TimesTen with the DROP CACHE GROUP statement.

  • After adding or dropping a unique index, unique constraint, or foreign key on an Oracle Database table that is cached in an AWT cache group. If you do not call the ttCacheCheck built-in procedure after adding a constraint, you may receive a run time error on the AWT cache group. After dropping a constraint, TimesTen may serialize transactions even if it is not necessary. Calling the ttCacheCheck built-in procedure verifies whether serialization is necessary.

  • You can use this built-in procedure to determine why some transactions are being serialized.

Note:

The ttCacheCheck built-in procedure cannot be called while the replication agent is running.

If a DDL statement is being executed on an AWT cache group when ttCacheCheck is called, then ttCacheCheck waits for the statement to complete or until the timeout period is reached.

If you have not defined the CacheAwtParallelism data store attribute to greater than one or the specified cache group is not an AWT cache group, then the ttCacheCheck built-in procedure returns an empty result set.

Example 4-7 Manually executing ttCacheCheck update missing dependencies

The following example shows the user manually executing the ttCacheCheck built-in procedure to determine if there are any missing constraints for an AWT cache group update_orders that is owned by cacheuser. A result set is returned that includes the error message. The ordertab table in the update_orders cache group is marked for serially propagated transactions.

Command> call ttCacheCheck(NULL, 'cacheuser', 'update_orders');

< CACHEUSER, UPDATE_ORDERS, CACHEUSER, ORDERTAB, Foreign Key, CACHEUSER, 
CUST_FK, 1, Transactions updating this table will be serialized to Oracle
because: The missing foreign key connects two AWT cache groups., 
table CACHEUSER.ORDERTAB constraint CACHEUSER.CUST_FK foreign key(CUSTID) 
references CACHEUSER.ACTIVE_CUSTOMER(CUSTID) >
1 row found.

Whenever the cache group schema changes in either the TimesTen or Oracle databases, you can call ttCacheCheck against all AWT cache groups to verify all constraints. The following example shows the user manually executing the ttCacheCheck built-in procedure to determine if there are any missing constraints for any AWT cache group in the entire TimesTen database by providing a NULL value for all input parameters. A result set is returned that includes any error messages.

Command> call ttCacheCheck(NULL, NULL, NULL);

< CACHEUSER, UPDATE_ORDERS, CACHEUSER, ORDERTAB, Foreign Key, CACHEUSER, 
CUST_FK, 1, Transactions updating this table will be serialized to Oracle
because: The missing foreign key connects two AWT cache groups., 
table CACHEUSER.ORDERTAB constraint CACHEUSER.CUST_FK foreign key(CUSTID) 
references CACHEUSER.ACTIVE_CUSTOMER(CUSTID) >
1 row found.
Configuring batch size for parallel propagation for AWT cache groups

When using AWT cache groups, TimesTen batches together one or more transactions that are to be applied in parallel to the back-end Oracle database. The CacheParAwtBatchSize parameter configures a threshold value for the number of rows included in a single batch. Once the maximum number of rows is reached, TimesTen includes the rest of the rows in the transaction (TimesTen does not break up any transactions), but does not add any more transactions to the batch.

For example, a user sets the CacheParAwtBatchSize to 200. For the next AWT propagation, there are three transactions, each with 120 rows, that need to be propagated and applied to the Oracle database. TimesTen includes the first two transactions in the first batch for a total of 240 rows. The third transaction is included in a second batch.

The default value for the CacheParAwtBatchSize parameter is 125 rows. The minimum value is 1. For more details on the CacheParAwtBatchSize parameter in the ttDBConfig built-in procedure, see "ttDBConfig" in the Oracle TimesTen In-Memory Database Reference.

You can retrieve the current value of CacheParAwtBatchSize as follows:

call ttDBConfig('CacheParAwtBatchSize');
< CACHEPARAWTBATCHSIZE, 125 >
1 row found.
 

You can set the CacheParAwtBatchSize parameter to 200 as follows:

call ttDBConfig('CacheParAwtBatchSize','200');
< CACHEPARAWTBATCHSIZE, 200 >
1 row found
 

Set the CacheParAwtBatchSize parameter only when advised by Oracle Support, who analyzes the workload and any dependencies in the workload to determine if a different value for CacheParAwtBatchSize could improve performance. Dependencies exist when transactions concurrently change the same data. Oracle Support may advise you to reduce this value if there are too many dependencies in the workload.

What an AWT cache group does and does not guarantee

An AWT cache group can guarantee that:

  • No transactions are lost because of communication failures between the TimesTen and Oracle databases.

  • If the replication agent is not running or loses its connection to the Oracle database, automatic propagation of committed updates on the TimesTen cache tables to the cached Oracle Database tables resumes after the agent restarts or reconnects to the Oracle database.

  • Transactions are committed in the Oracle database in the same order they were committed in the TimesTen database.

An AWT cache group cannot guarantee that:

  • All transactions committed successfully in the TimesTen database are successfully propagated to and committed in the Oracle database. Execution errors on the Oracle database cause the transaction in the Oracle database to be rolled back. For example, an update on the Oracle database may fail because of a unique constraint violation. Transactions that contain execution errors are not retried.

    Execution errors are considered permanent errors and are reported to the TimesTenDatabaseFileName.awterrs file that resides in the same directory as the TimesTen database's checkpoint files. See "Reporting Oracle Database permanent errors for AWT cache groups" for more information.

  • The absolute order of Oracle Database updates is preserved because TimesTen does not resolve update conflicts. The following are some examples:

    • In two separate TimesTen databases (DB1 and DB2), different AWT cache groups cache the same Oracle Database table. An update is committed on the cache table in DB1. An update is then committed on the cache table in DB2. The two cache tables reside in different TimesTen databases and cache the same Oracle Database table. Because the writethrough operations are asynchronous, the update from DB2 may get propagated to the Oracle database before the update from DB1, resulting in the update from DB1 overwriting the update from DB2.

      Using a dynamic AWT global cache group resolves this write inconsistency. See "Global cache groups" for more information about global cache groups.

    • An update is committed on a cache table in an AWT cache group. The same update is committed on the cached Oracle Database table using a passthrough operation. The cache table update, which is automatically and asynchronously propagated to the Oracle database, may overwrite the passed through update that was processed directly on the cached Oracle Database table depending on when the propagated update and the passed through update is processed on the Oracle database. For this and other potential error conditions, TimesTen recommends that you do not execute DML statements directly against Oracle Database tables cached in an AWT cache group. For more information, see "Restrictions with AWT cache groups".

Restrictions with AWT cache groups

The following restrictions apply when using an AWT cache group:

  • Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.

    See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE cache table attribute.

    See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON cache table attribute.

  • A FLUSH CACHE GROUP statement cannot be issued on the cache group.

    See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.

  • The cache table definitions cannot contain a WHERE clause.

    See "Using a WHERE clause" for more information about WHERE clauses in cache group definitions and operations.

  • A TRUNCATE TABLE statement cannot be issued on the cache tables.

  • AWT cache groups cannot cache Oracle Database views or materialized views.

  • The replication agent must be stopped before creating or dropping an AWT cache group.

    See "Managing the replication agent" for information about how to stop and start the replication agent.

  • Committed updates on the TimesTen cache tables are not propagated to the cached Oracle Database tables unless the replication agent is running.

  • To create an AWT cache group, the length of the absolute path name of the TimesTen database cannot exceed 248 characters.

  • You should avoid executing DML statements on Oracle Database tables cached in an AWT cache group. This could result in an error condition. Any insert, update, or delete operation on the cached Oracle Database table can negatively affect the operations performed on TimesTen for the affected rows. TimesTen does not detect or resolve update conflicts that occur on the Oracle database. Committed updates made directly on a cached Oracle Database table may be overwritten by a committed update made on the TimesTen cache table when the cache table update is propagated to the Oracle database. In addition, deleting rows on the cached Oracle Database table could cause an empty update if TimesTen tries to update a row that no longer exists.

    To ensure that not all data is restricted from DML statements on Oracle Database, you can partition the data on Oracle Database to separate the data that is to be included in the AWT cache group from the data to be excluded from the AWT cache group.

  • TimesTen performs deferred checking when determining whether a single SQL statement causes a constraint violation with a unique index.

    For example, suppose there is a unique index on a cached Oracle Database table's NUMBER column, and a unique index on the same NUMBER column on the TimesTen cache table. There are five rows in the cached Oracle Database table and the same five rows in the cache table. The values in the NUMBER column range from 1 to 5.

    An UPDATE statement is issued on the cache table to increment the value in the NUMBER column by 1 for all rows. The operation succeeds on the cache table but fails when it is propagated to the cached Oracle Database table.

    This occurs because TimesTen performs the unique index constraint check at the end of the statement's execution after all the rows have been updated. The Oracle database, however, performs the constraint check each time after a row has been updated.

    Therefore, when the row in the cache table with value 1 in the NUMBER column is changed to 2 and the update is propagated to the Oracle database, it causes a unique constraint violation with the row that has the value 2 in the NUMBER column of the cached Oracle Database table.

Reporting Oracle Database permanent errors for AWT cache groups

If transactions are not successfully propagated to and committed in the Oracle database, then the permanent errors cause the transaction in the Oracle database to be rolled back. For example, an update on the Oracle database may fail because of a unique constraint violation. Transactions that contain permanent errors are not retried.

Permanent errors are always reported to the TimesTenDatabaseFileName.awterrs text file that resides in the same directory as the TimesTen database checkpoint files. See "Oracle Database errors reported by TimesTen for AWT" in the Oracle TimesTen In-Memory Database Troubleshooting Guide for information about the contents of this file.

You can configure TimesTen to report these errors in both ASCII and XML formats with the ttCacheConfig built-in procedure.

Note:

Do not pass in any values to the tblOwner and tblName parameters for ttCacheConfig as they are not applicable to setting the format for the errors file.
  • To configure TimesTen to report permanent errors to only the TimesTenDatabaseFileName.awterrs text file, call the ttCacheConfig built-in procedure with the ASCII parameter. This is the default.

    Command> call ttCacheConfig('AwtErrorXmlOutput',,,'ASCII');
    
  • To configure TimesTen to report permanent errors to both the TimesTenDatabaseFileName.awterrs text file as well as to an XML file named TimesTenDatabaseFileName.awterrs.xml, call the ttCacheConfig built-in procedure with the XML parameter.

    Command> call ttCacheConfig('AwtErrorXmlOutput',,,'XML');
    

Note:

Before calling ttCacheConfig to direct permanent errors to the XML file, you must first stop the replication agent. Then, restart the replication agent after the built-in procedure completes.

For full details on this built-in procedure, see "ttCacheConfig" in the Oracle TimesTen In-Memory Database Reference.

When you configure error reporting to be reported in XML format, the following two files are generated when Oracle Database permanent errors occur:

  • TimesTenDatabaseFileName.awterrs.xml contains the Oracle Database permanent error messages in XML format.

  • TimesTenDatabaseFileName.awterrs.dtd is the file that contains the XML Document Type Definition (DTD), which is used when parsing the TimesTenDatabaseFileName.awterrs.xml file.

    The XML DTD, which is based on the XML 1.0 specification, is a set of markup declarations that describes the elements and structure of a valid XML file containing a log of errors. The XML file is encoded using UTF-8. The following are the elements for the XML format.

    Note:

    For more information on reading and understanding XML Document Type Definitions, see http://www.w3.org/TR/REC-xml.
    <!ELEMENT ttawterrorreport (awterrentry*) >
    <!ELEMENT awterrentry(header, (failedop)?, failedtxn) >
    <!ELEMENT header (time, datastore, oracleid, transmittingagent, errorstr,
     (ctn)?, (batchid)?, (depbatchid)?) >
    <!ELEMENT failedop (sql) >
    <!ELEMENT failedtxn ((sql)+) >
    <!ELEMENT time (hour, min, sec, year, month, day) >
    <!ELEMENT hour (#PCDATA) >
    <!ELEMENT min (#PCDATA) >
    <!ELEMENT sec (#PCDATA) >
    <!ELEMENT year (#PCDATA) >
    <!ELEMENT month (#PCDATA) >
    <!ELEMENT day (#PCDATA) >
    <!ELEMENT datastore (#PCDATA) >
    <!ELEMENT oracleid (#PCDATA) >
    <!ELEMENT transmittingagent (transmitingname, pid, threadid) >
    <!ELEMENT pid (#PCDATA) >
    <!ELEMENT threadid (#PCDATA) >
    <!ELEMENT transmittingname (#PCDATA) >
    <!ELEMENT errorstr (#PCDATA) >
    <!ELEMENT ctn (timestamp, seqnum) >
    <!ELEMENT timestamp(#PCDATA) >
    <!ELEMENT seqnum(#PCDATA) >
    <!ELEMENT batchid(#PCDATA) >
    <!ELEMENT depbatchid(#PCDATA) >
    <!ELEMENT sql(#PCDATA) >
    

Synchronous writethrough (SWT) cache group

A synchronous writethrough (SWT) cache group enforces a caching behavior where committed updates on the TimesTen cache tables are automatically and synchronously propagated to the cached Oracle Database tables as shown in Figure 4-7.

Note:

You should avoid executing DML statements on Oracle Database tables cached in an SWT cache group. This can result in an error condition. For more information, see "Restrictions with SWT cache groups".

Figure 4-7 Synchronous writethrough cache group

Description of Figure 4-7 follows
Description of "Figure 4-7 Synchronous writethrough cache group"

The transaction commit on the TimesTen database occurs synchronously with the commit on the Oracle database. When an application commits a transaction in the TimesTen database, the transaction is processed in the Oracle database before it is processed in TimesTen. The application is blocked until the transaction has completed in both the Oracle and TimesTen databases.

If the transaction fails to commit in the Oracle database, the application must roll back the transaction in TimesTen. If the Oracle Database transaction commits successfully but the TimesTen transaction fails to commit, the cache tables in the SWT cache group are no longer synchronized with the cached Oracle Database tables.

Note:

The behavior and error conditions for how commit occurs on both the TimesTen and Oracle databases when committing propagated updates is the same commit process on a user managed cache group with the PROPAGATE cache attribute that is described in "PROPAGATE cache table attribute".

To manually resynchronize the cache tables with the cached Oracle Database tables, call the ttCachePropagateFlagSet built-in procedure to disable update propagation, and then reissue the transaction in the TimesTen database after correcting the problem that caused the transaction commit to fail in TimesTen. Then, call the ttCachePropagateFlagSet built-in procedure to re-enable update propagation. You can also resynchronize the cache tables with the cached Oracle Database tables by reloading the accompanying cache groups.

The following is the definition of the Oracle Database table that is to be cached in the SWT cache group that is defined in Example 4-8. The Oracle Database table is owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.

CREATE TABLE product
(prod_num    VARCHAR2(6) NOT NULL PRIMARY KEY,
 name        VARCHAR2(30),
 price       NUMBER(8,2),
 ship_weight NUMBER(4,1));

The companion Oracle Database user of the TimesTen cache manager user must be granted the SELECT privilege on the oratt.product table in order for the cache manager user to create an SWT cache group that caches this table. This Oracle Database user must also be granted the INSERT, UPDATE, and DELETE privileges on the oratt.product table for synchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle Database table.

Use the CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP statement to create an SWT cache group.

Example 4-8 Creating a SWT cache group

The following statement creates a synchronous writethrough cache group top_products that caches the oratt.product table:

CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP top_products
FROM oratt.product
 (prod_num    VARCHAR2(6) NOT NULL,
  name        VARCHAR2(30),
  price       NUMBER(8,2),
  ship_weight NUMBER(4,1),
  PRIMARY KEY(prod_num));

When TimesTen manages operations for SWT cache groups, it connects to the Oracle database using the current user's credentials as the user name and the OraclePwd connection attribute as the Oracle password. TimesTen does not connect to the Oracle database with the cache administration user name and password set with the ttCacheUidPwdSet built-in procedure when managing SWT cache group operations. For more details, see "Set the cache administration user name and password".

Restrictions with SWT cache groups

The following restrictions apply when using an SWT cache group:

  • Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.

    See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE cache table attribute.

    See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON cache table attribute.

  • A FLUSH CACHE GROUP statement cannot be issued on the cache group.

    See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement

  • The cache table definitions cannot contain a WHERE clause.

    See "Using a WHERE clause" for more information about WHERE clauses in cache group definitions and operations.

  • A TRUNCATE TABLE statement cannot be issued on the cache tables.

  • SWT cache groups cannot cache Oracle Database views or materialized views.

  • You should avoid executing DML statements directly on Oracle Database tables cached in an SWT cache group. This could result in an error condition. Any insert, update, or delete operation on the cached Oracle Database table can negatively affect the operations performed on TimesTen for the affected rows. TimesTen does not detect or resolve update conflicts that occur on the Oracle database. Committed updates made directly on a cached Oracle Database table may be overwritten by a committed update made on the TimesTen cache table when the cache table update is propagated to the Oracle database. In addition, deleting rows on the cached Oracle Database table could cause an empty update if TimesTen tries to update a row that no longer exists.

    To ensure that not all data is restricted from DML statements on Oracle Database, you can partition the data on Oracle Database to separate the data that is to be included in the SWT cache group from the data to be excluded from the SWT cache group.

User managed cache group

If the system managed cache groups (read-only, AWT, SWT) do not satisfy your application's requirements, you can create a user managed cache group that defines customized caching behavior with one or more of the following cache table attributes:

Note:

When TimesTen manages operations for user managed cache groups, it connects to the Oracle database using the current user's credentials as the user name and the OraclePwd connection attribute as the Oracle password. TimesTen does not connect to the Oracle database with the cache administration user name and password set with the ttCacheUidPwdSet built-in procedure for user managed cache group operations. For more details, see "Set the cache administration user name and password".
  • You can specify the READONLY cache table attribute on individual cache tables in a user managed cache group to define read-only behavior where the data is refreshed on TimesTen from the Oracle database at the table level.

  • You can specify the PROPAGATE cache table attribute on individual cache tables in a user managed cache group to define synchronous writethrough behavior at the table level. The PROPAGATE cache table attribute specifies that committed updates on the cache table are automatically and synchronously propagated to the cached Oracle Database table.

  • You can define a user managed cache group to automatically refresh and propagate committed updates between the Oracle and TimesTen databases by using the AUTOREFRESH cache group attribute and the PROPAGATE cache table attribute. Using both attributes enables bidirectional transmit, so that committed updates on the TimesTen cache tables or the cached Oracle Database tables are propagated or refreshed to each other.

    See "AUTOREFRESH cache group attribute" for more information about defining an autorefresh mode, interval, and state.

  • You can use the LOAD CACHE GROUP, REFRESH CACHE GROUP, and FLUSH CACHE GROUP statements to manually control the transmit of committed updates between the Oracle and TimesTen databases.

    See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP and REFRESH CACHE GROUP statements. See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.

  • You can cache Oracle Database materialized views in a user managed cache group that does not use either the PROPAGATE or AUTOREFRESH cache group attributes. The cache group must be manually loaded and flushed. You cannot cache Oracle Database views.

The following sections provide more information about user managed cache groups:

READONLY cache table attribute

The READONLY cache table attribute can be specified only for cache tables in a user managed cache group. READONLY specifies that the cache table cannot be updated directly. By default, a cache table in a user managed cache group is updatable.

Unlike a read-only cache group where all of its cache tables are read-only, in a user managed cache group individual cache tables can be specified as read-only using the READONLY cache table attribute.

Example 4-10 demonstrates the READONLY cache table attribute in the oratt.cust_interests cache table.

The following restrictions apply when using the READONLY cache table attribute:

  • If the cache group uses the AUTOREFRESH cache group attribute, the READONLY cache table attribute must be specified on all or none of its cache tables.

    See "AUTOREFRESH cache group attribute" for more information about using the AUTOREFRESH cache group attribute.

  • You cannot use both the READONLY and PROPAGATE cache table attributes on the same cache table.

    See "PROPAGATE cache table attribute" for more information about using the PROPAGATE cache table attribute.

  • A FLUSH CACHE GROUP statement cannot be issued on the cache group unless one or more of its cache tables use neither the READONLY nor the PROPAGATE cache table attribute.

    See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.

  • After the READONLY cache table attribute has been specified on a cache table, you cannot change this attribute unless you drop the cache group and re-create it.

PROPAGATE cache table attribute

The PROPAGATE cache table attribute can be specified only for cache tables in a user managed cache group. PROPAGATE specifies that committed updates on the TimesTen cache table as part of a TimesTen transaction are automatically and synchronously propagated to the cached Oracle Database table. If the PROPAGATE cache table attribute is not specified, then the default setting for a cache table in a user managed cache group is the NOT PROPAGATE cache table attribute (which does not propagate committed updates on the cache table to the cached Oracle table).

All SQL statements executed by an application on cached tables are applied to the cached tables immediately. All of these operations are buffered until the transaction commits or reaches a memory upper limit. At this time, all operations are propagated to the tables in the Oracle database.

Note:

If the TimesTen database or its daemon fails unexpectedly, the results of the transaction on either the TimesTen or Oracle databases are not guaranteed.

Since the operations in the transaction are applied to tables in both the TimesTen and Oracle databases, the process for committing is as follows:

  1. After the operations are propagated to the Oracle database, the commit is first attempted in the Oracle database.

    • If an error occurs when applying the operations on the tables in the Oracle database, then all operations are rolled back on the tables on the Oracle database. If the commit fails in the Oracle database, the commit is not attempted in the TimesTen database and the application must roll back the TimesTen transaction. If the user tries to execute another statement, an error displays informing them of the need for a rollback. As a result, the Oracle database never misses updates committed in TimesTen.

  2. If the commit succeeds in the Oracle database, the commit is attempted in the TimesTen database.

    • If the transaction successfully commits on the Oracle database, the user's transaction is committed on TimesTen (indicated by the commit log record in the transaction log) and notifies the application. If the application ends abruptly before TimesTen informs it of the success of the local commit, TimesTen is still able to finalize the transaction commit on TimesTen based on what is saved in the transaction log.

    • If the transaction successfully commits on the Oracle database and a failure occurs before returning the status of the commit on TimesTen, then no record of the successful commit is written into the transaction log and the transaction is rolled back.

    • If the commit fails in TimesTen, an error message is returned from TimesTen indicating the cause of the failure. You then need to manually resynchronize the cache tables with the Oracle Database tables.

      Note:

      See "Synchronous writethrough (SWT) cache group" for information on how to resynchronize the cache tables with the Oracle Database tables.

You can disable propagation of committed updates on the TimesTen cached tables to the Oracle database with the ttCachePropagateFlagSet built-in procedure. This built-in procedure can enable or disable automatic propagation so that committed updates on a cache table on TimesTen for the current transaction are never propagated to the cached Oracle Database table. You can then re-enable propagation for DML statements by resetting the flag to one with the ttCachePropagateFlagSet built-in procedure. After the flag is set back to one, propagation of committed updates to the Oracle database resumes. The propagation flag automatically resets to one after the transaction is committed or rolled back. See "ttCachePropagateFlagSet" in the Oracle TimesTen In-Memory Database Reference for more details.

Example 4-9 demonstrates the use of the PROPAGATE cache table attribute in the oratt.active_customer cache table.

Restrictions for the PROPAGATE cache attribute

The following restrictions apply when using the PROPAGATE cache table attribute:

  • If the cache group uses the AUTOREFRESH cache group attribute, the PROPAGATE cache table attribute must be specified on all or none of its cache tables.

    See "AUTOREFRESH cache group attribute" for more information about using the AUTOREFRESH cache group attribute.

  • If the cache group uses the AUTOREFRESH cache group attribute, the NOT PROPAGATE cache table attribute cannot be explicitly specified on any of its cache tables.

  • You cannot use both the PROPAGATE and READONLY cache table attributes on the same cache table.

    See "READONLY cache table attribute" for more information about using the READONLY cache table attribute.

  • A FLUSH CACHE GROUP statement cannot be issued on the cache group unless one or more of its cache tables use neither the PROPAGATE nor the READONLY cache table attribute.

    See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.

  • After the PROPAGATE cache table attribute has been specified on a cache table, you cannot change this attribute unless you drop the cache group and re-create it.

  • The PROPAGATE cache table attribute cannot be used when caching Oracle Database materialized views.

  • TimesTen does not perform a conflict check to prevent a propagate operation from overwriting data that was updated directly on a cached Oracle Database table. Therefore, updates should only be performed directly on the TimesTen cache tables or the cached Oracle Database tables, but not both.

Examples of user managed cache groups

The following are the definitions of the Oracle Database tables that are to be cached in the user managed cache groups that are defined in Example 4-9 and Example 4-10. The Oracle Database tables are owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.

CREATE TABLE active_customer
 (custid NUMBER(6) NOT NULL PRIMARY KEY,
  name   VARCHAR2(50),
  addr   VARCHAR2(100),
  zip    VARCHAR2(12),
  region VARCHAR2(12) DEFAULT 'Unknown');

CREATE TABLE ordertab
 (orderid NUMBER(10) NOT NULL PRIMARY KEY,
  custid  NUMBER(6) NOT NULL);

CREATE TABLE cust_interests
 (custid   NUMBER(6) NOT NULL,
  interest VARCHAR2(10) NOT NULL,
  PRIMARY KEY (custid, interest));

CREATE TABLE orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY (orderid, itemid));

Use the CREATE USERMANAGED CACHE GROUP statement to create a user managed cache group.

Example 4-9 Creating a single-table user managed cache group

The following statement creates a user managed cache group update_anywhere_customers that caches the oratt.active_customer table as shown in Figure 4-8:

CREATE USERMANAGED CACHE GROUP update_anywhere_customers
AUTOREFRESH MODE INCREMENTAL INTERVAL 30 SECONDS
FROM oratt.active_customer
 (custid NUMBER(6) NOT NULL,
  name   VARCHAR2(50),
  addr   VARCHAR2(100),
  zip    VARCHAR2(12),
  PRIMARY KEY(custid),
  PROPAGATE);

Figure 4-8 Single-table user managed cache group

Description of Figure 4-8 follows
Description of "Figure 4-8 Single-table user managed cache group"

In this example, all columns except region from the oratt.active_customer table are cached in TimesTen. Since this is defined with the PROPAGATE cache table attribute, updates committed on the oratt.active_customer cache table on TimesTen are transmitted to the oratt.active_customer cached Oracle Database table. Since the user managed cache table is also defined with the AUTOREFRESH cache attribute, any committed updates on the oratt.active_customer Oracle Database table are transmitted to the update_anywhere_customers cached table.

The companion Oracle Database user of the TimesTen cache manager user must be granted the SELECT privilege on the oratt.active_customer table in order for the cache manager user to create a user managed cache group that caches this table, and for autorefresh operations to occur from the cached Oracle Database table to the TimesTen cache table. The companion Oracle Database user must also be granted the INSERT, UPDATE and DELETE privileges on the oratt.active_customer table for synchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle Database table.

In this example, the AUTOREFRESH cache group attribute specifies that committed updates on the oratt.active_customer cached Oracle Database table are automatically refreshed to the TimesTen oratt.active_customer cache table every 30 seconds.

If you manually created the Oracle Database objects used to enforce the predefined behaviors of a user managed cache group that uses the AUTOREFRESH MODE INCREMENTAL cache group attribute as described in "Manually create Oracle Database objects used to manage data caching", you need to set the autorefresh state to OFF when creating the cache group.

Then you need to run the ttIsql utility's cachesqlget command to generate a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle Database table that is cached in the user managed cache group.

See "Manually creating Oracle Database objects for autorefresh cache groups" for more information.

Example 4-10 Creating a multiple-table user managed cache group

The following statement creates a user managed cache group western_customers that caches the oratt.active_customer, oratt.ordertab, oratt.cust_interests, and oratt.orderdetails tables as shown in Figure 4-9:

CREATE USERMANAGED CACHE GROUP western_customers
FROM oratt.active_customer
 (custid NUMBER(6) NOT NULL,
  name   VARCHAR2(50),
  addr   VARCHAR2(100),
  zip    VARCHAR2(12),
  region VARCHAR2(12),
  PRIMARY KEY(custid),
  PROPAGATE)
  WHERE (oratt.active_customer.region = 'West'),
oratt.ordertab
 (orderid NUMBER(10) NOT NULL,
  custid  NUMBER(6) NOT NULL,
  PRIMARY KEY(orderid),
  FOREIGN KEY(custid) REFERENCES oratt.active_customer(custid),
  PROPAGATE),
oratt.cust_interests
 (custid   NUMBER(6) NOT NULL,
  interest VARCHAR2(10) NOT NULL,
  PRIMARY KEY(custid, interest),
  FOREIGN KEY(custid) REFERENCES oratt.active_customer(custid),
  READONLY),
oratt.orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY(orderid, itemid),
  FOREIGN KEY(orderid) REFERENCES oratt.ordertab(orderid))
  WHERE (oratt.orderdetails.quantity >= 5);

Figure 4-9 Multiple-table user managed cache group

Description of Figure 4-9 follows
Description of "Figure 4-9 Multiple-table user managed cache group"

Only customers in the West region who ordered at least 5 of the same item are cached.

The companion Oracle Database user of the TimesTen cache manager user must be granted the SELECT privilege on the oratt.active_customer, oratt.ordertab, oratt.cust_interests, and oratt.orderdetails tables in order for the cache manager user to create a user managed cache group that caches all of these tables. The companion Oracle Database user must also be granted the INSERT, UPDATE and DELETE privileges on the oratt.active_customer and oratt.ordertab tables for synchronous writethrough operations to occur from these TimesTen cache tables to the cached Oracle Database tables.

Each cache table in the western_customers cache group contains a primary key. Each child table references a parent table with a foreign key constraint. The oratt.active_customer root table and the oratt.orderdetails child table each contain a WHERE clause to restrict the rows to be cached. The oratt.active_customer root table and the oratt.ordertab child table both use the PROPAGATE cache table attribute so that committed updates on these cache tables are automatically propagated to the cached Oracle Database tables. The oratt.cust_interests child table uses the READONLY cache table attribute so that it cannot be updated directly.

AUTOREFRESH cache group attribute

The AUTOREFRESH cache group attribute can be specified when creating a read-only cache group or a user managed cache group using a CREATE CACHE GROUP statement. AUTOREFRESH specifies that committed updates on cached Oracle Database tables are automatically refreshed to the TimesTen cache tables. Autorefresh is defined by default on read-only cache groups.

The following are the default settings of the autorefresh attributes:

  • The autorefresh mode is incremental.

  • The autorefresh interval is 5 minutes.

  • The autorefresh state is PAUSED.

TimesTen supports two autorefresh modes:

  • INCREMENTAL: Committed updates on cached Oracle Database tables are automatically refreshed to the TimesTen cache tables based on the cache group's autorefresh interval. Incremental autorefresh mode uses Oracle Database objects to track committed updates on cached Oracle Database tables. See "Managing a caching environment with Oracle Database objects" for information on these objects.

  • FULL: All cache tables are automatically refreshed, based on the cache group's autorefresh interval, by unloading all their rows and then reloading from the cached Oracle Database tables.

Incremental autorefresh mode incurs some overhead to refresh the cache group for each committed update on the cached Oracle Database tables. There is no overhead when using full autorefresh mode.

When using incremental autorefresh mode, committed updates on cached Oracle Database tables are tracked in change log tables in the Oracle database. Under certain circumstances, it is possible for some change log records to be deleted from the change log table before they are automatically refreshed to the TimesTen cache tables. If this occurs, TimesTen initiates a full automatic refresh on the cache group. See "Monitoring the cache administration user's tablespace" for information on how to configure an action to take when the tablespace that the change log tables reside in becomes full.

The change log table on the Oracle database does not have column-level resolution because of performance reasons. Thus the autorefresh operation updates all of the columns in a row. XLA reports that all of the columns in the row have changed even if the data did not actually change in each column.

The autorefresh interval determines how often autorefresh operations occur in minutes, seconds or milliseconds. Cache groups with the same autorefresh interval are refreshed within the same transaction. You can use the ttCacheAutorefresh built-in procedure to initiate an immediate autorefresh operation. For more information, see "ttCacheAutorefresh" in Oracle TimesTen In-Memory Database Reference.

The autorefresh state can be set to ON, PAUSED or OFF. Autorefresh operations are scheduled by TimesTen when the cache group's autorefresh state is ON.

When the cache group's autorefresh state is OFF, committed updates on the cached Oracle Database tables are not tracked.

When the cache group's autorefresh state is PAUSED, committed updates on the cached Oracle Database tables are tracked in the Oracle database, but are not automatically refreshed to the TimesTen cache tables until the state is changed to ON.

The following restrictions apply when using the AUTOREFRESH cache group attribute:

  • A FLUSH CACHE GROUP statement cannot be issued on the cache group.

    See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.

  • A TRUNCATE TABLE statement issued on a cached Oracle Database table is not automatically refreshed to the TimesTen cache table. Before issuing a TRUNCATE TABLE statement on a cached Oracle Database table, use an ALTER CACHE GROUP statement to change the autorefresh state of the cache group that contains the cache table to PAUSED.

    See "Altering a cache group to change the AUTOREFRESH mode, interval or state" for more information about the ALTER CACHE GROUP statement.

    After issuing the TRUNCATE TABLE statement on the cached Oracle Database table, use a REFRESH CACHE GROUP statement to manually refresh the cache group.

  • A LOAD CACHE GROUP statement can only be issued if the cache tables are empty, unless the cache group is dynamic.

    See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP and REFRESH CACHE GROUP statements.

    See "Dynamic cache groups" for more information about dynamic cache groups.

  • The autorefresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement on the cache group, unless the cache group is dynamic, in which case the autorefresh state must be PAUSED or ON. The LOAD CACHE GROUP statement cannot contain a WHERE clause, unless the cache group is dynamic, in which case the WHERE clause must be followed by a COMMIT EVERY n ROWS clause.

    See "Using a WHERE clause" for more information about WHERE clauses in cache group definitions and operations.

  • The autorefresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement on the cache group. The REFRESH CACHE GROUP statement cannot contain a WHERE clause.

  • All tables and columns referenced in WHERE clauses when creating, loading or unloading the cache group must be fully qualified. For example:

    user_name.table_name and user_name.table_name.column_name

  • To use the AUTOREFRESH cache group attribute in a user managed cache group, all of the cache tables must be specified with the PROPAGATE cache table attribute or all of the cache tables must be specified the READONLY cache table attribute.

  • You cannot specify the AUTOREFRESH cache group attribute in a user managed cache group that contains cache tables that explicitly use the NOT PROPAGATE cache table attribute.

  • The AUTOREFRESH cache table attribute cannot be used when caching Oracle Database materialized views in a user managed cache group.

  • LRU aging cannot be specified on the cache group, unless the cache group is dynamic where LRU aging is defined by default.

    See "LRU aging" for more information about LRU aging.

If you create a unique index on a cache group with the AUTOREFRESH cache group attribute, the index is changed to a non-unique index to avoid a constraint violation. A constraint violation could occur with a unique index because conflicting updates could occur in the same statement execution on the Oracle Database table, while each row update is executed separately in TimesTen. If the unique index exists on the Oracle Database table that is being cached, then uniqueness is enforced on the Oracle Database table and does not need to be verified again in TimesTen.

In Example 4-9, the update_anywhere_customers cache group uses the AUTOREFRESH cache group attribute.

Altering a cache group to change the AUTOREFRESH mode, interval or state

After creating an autorefresh cache group, you can use an ALTER CACHE GROUP statement to change the cache group's autorefresh mode, interval or state. You cannot use ALTER CACHE GROUP to instantiate automatic refresh for a cache group that was originally created without autorefresh defined.

If you change a cache group's autorefresh state to OFF or drop a cache group that has an autorefresh operation in progress:

  • The autorefresh operation stops if the setting of the LockWait connection attribute is greater than 0. The ALTER CACHE GROUP or DROP CACHE GROUP statement preempts the autorefresh operation.

  • The autorefresh operation continues if the LockWait connection attribute is set to 0. The ALTER CACHE GROUP or DROP CACHE GROUP statement is blocked until the autorefresh operation completes or the statement fails with a lock timeout error.

Example 4-11 Altering the autorefresh attributes of a cache group

The following statements change the autorefresh mode, interval and state of the customer_orders cache group:

ALTER CACHE GROUP customer_orders SET AUTOREFRESH MODE FULL;
ALTER CACHE GROUP customer_orders SET AUTOREFRESH INTERVAL 30 SECONDS;
ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE ON;

Manually creating Oracle Database objects for autorefresh cache groups

If you manually created the Oracle Database objects used to enforce the predefined behaviors of an autorefresh cache group as described in "Manually create Oracle Database objects used to manage data caching", you need to set the autorefresh state to OFF when creating the cache group.

Then you need to run the ttIsql utility's cachesqlget command with the INCREMENTAL_AUTOREFRESH option and the INSTALL flag as the cache manager user. This command generates a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle Database table that is cached in the autorefresh cache group. These Oracle Database objects track updates on the cached Oracle Database tables so that the updates can be automatically refreshed to the cache tables.

Next use SQL*Plus to run the script generated by the ttIsql utility's cachesqlget command as the sys user. Then use an ALTER CACHE GROUP statement to change the autorefresh state of the cache group to PAUSED.

Example 4-12 Creating a read-only cache group when Oracle Database objects were manually created

The first statement creates a read-only cache group customer_orders with the autorefresh state set to OFF. The SQL*Plus script generated by the ttIsql utility's cachesqlget command is saved to the /tmp/obj.sql file. The last statement changes the autorefresh state of the cache group to PAUSED.

CREATE READONLY CACHE GROUP customer_orders
AUTOREFRESH STATE OFF
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num)),
oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> cachesqlget INCREMENTAL_AUTOREFRESH customer_orders INSTALL /tmp/obj.sql;
Command> exit

% sqlplus sys as sysdba
Enter password: password
SQL> @/tmp/obj
SQL> exit

ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE PAUSED;

Using a WHERE clause

A cache table definition in a CREATE CACHE GROUP statement can contain a WHERE clause to restrict the rows to cache in the TimesTen database for particular cache group types.

You can also specify a WHERE clause in a LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP or FLUSH CACHE GROUP statement for particular cache group types. Some statements, such as LOAD CACHE GROUP and REFRESH CACHE GROUP, may result in concatenated WHERE clauses in which the WHERE clause for the cache table definition is evaluated before the WHERE clause in the LOAD CACHE GROUP or REFRESH CACHE GROUP statement.

The following restrictions apply to WHERE clauses used in cache table definitions and cache group operations:

  • WHERE clauses can only be specified in the cache table definitions of a CREATE CACHE GROUP statement for read-only and user managed cache groups.

  • A WHERE clause can be specified in a LOAD CACHE GROUP statement except on an explicitly loaded autorefresh cache group.

    See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP statement.

  • A WHERE clause can be specified in a REFRESH CACHE GROUP statement except on an autorefresh cache group.

    See "Loading and refreshing a cache group" for more information about the REFRESH CACHE GROUP statement.

  • A WHERE clause can be specified in a FLUSH CACHE GROUP statement on a user managed cache group that allows committed updates on the TimesTen cache tables to be flushed to the cached Oracle Database tables.

    See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.

  • WHERE clauses in a CREATE CACHE GROUP statement cannot contain a subquery. Therefore, each WHERE clause cannot reference any table other than the one in its cache table definition. However, a WHERE clause in a LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP or FLUSH CACHE GROUP statement may contain a subquery.

  • A WHERE clause in a LOAD CACHE GROUP, REFRESH CACHE GROUP or FLUSH CACHE GROUP statement can reference only the root table of the cache group, unless the WHERE clause contains a subquery.

  • WHERE clauses in the cache table definitions are only enforced when the cache group is manually loaded or refreshed, or the cache tables are dynamically loaded. If a cache table is updatable, you can insert or update a row such that the WHERE clause in the cache table definition for that row is not satisfied.

  • All tables and columns referenced in WHERE clauses when creating, loading, refreshing, unloading or flushing the cache group must be fully qualified. For example:

    user_name.table_name and user_name.table_name.column_name

In Example 4-10, both the oratt.active_customer and oratt.orderdetails tables contain a WHERE clause.

Proper placement of WHERE clause in a CREATE CACHE GROUP statement

In a multiple-table cache group, a WHERE clause in a particular table definition should not reference any table in the cache group other than the table itself. For example, the following CREATE CACHE GROUP statements are valid:

CREATE READONLY CACHE GROUP customer_orders
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))
  WHERE (oratt.customer.cust_num < 100),
oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));
CREATE READONLY CACHE GROUP customer_orders
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num)),
oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));
  WHERE (oratt.orders.cust_num < 100)

The following statement is not valid because the WHERE clause in the child table's definition references its parent table:

CREATE READONLY CACHE GROUP customer_orders
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num)),
oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num))
  WHERE (oratt.customer.cust_num < 100);

Similarly, the following statement is not valid because the WHERE clause in the parent table's definition references its child table:

CREATE READONLY CACHE GROUP customer_orders
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))
  WHERE (oratt.orders.cust_num < 100),
oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));

Referencing Oracle Database PL/SQL functions in a WHERE clause

A user-defined PL/SQL function in the Oracle database can be invoked indirectly in a WHERE clause within a CREATE CACHE GROUP, LOAD CACHE GROUP, or REFRESH CACHE GROUP (for dynamic cache groups only) statement. After creating the function, create a public synonym for the function. Then grant the EXECUTE privilege on the function to PUBLIC.

For example, in the Oracle database:

CREATE OR REPLACE FUNCTION get_customer_name
(c_num oratt.customer.cust_num%TYPE) RETURN VARCHAR2 IS
c_name oratt.customer.name%TYPE;
BEGIN
  SELECT name INTO c_name FROM oratt.customer WHERE cust_num = c_num;
  RETURN c_name;
END get_customer_name;

CREATE PUBLIC SYNONYM retname FOR get_customer_name;
GRANT EXECUTE ON get_customer_name TO PUBLIC;

Then in the TimesTen database, for example, you can create a cache group with a WHERE clause that references the Oracle Database public synonym that was created for the function:

CREATE READONLY CACHE GROUP top_customer
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))
WHERE name = retname(100);

For cache group types that allow a WHERE clause on a LOAD CACHE GROUP or REFRESH CACHE GROUP statement, you can invoke the function indirectly by referencing the public synonym that was created for the function. For example, you can use the following LOAD CACHE GROUP statement to load the AWT cache group new_customers:

LOAD CACHE GROUP new_customers WHERE name = retname(101) COMMIT EVERY 0 ROWS;

ON DELETE CASCADE cache table attribute

The ON DELETE CASCADE cache table attribute can be specified for cache tables in any cache group type. ON DELETE CASCADE specifies that when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign keys are also deleted.

Example 4-13 Using the ON DELETE CASCADE cache table attribute

The following statement uses the ON DELETE CASCADE cache table attribute on the child table's foreign key definition:

CREATE READONLY CACHE GROUP customer_orders
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num)),
oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num) ON DELETE CASCADE);

All paths from a parent table to a child table must be either "delete" paths or "do not delete" paths. There cannot be some "delete" paths and some "do not delete" paths from a parent table to a child table. Specify the ON DELETE CASCADE cache table attribute for child tables on a "delete" path.

The following restrictions apply when using the ON DELETE CASCADE cache table attribute:

  • For AWT and SWT cache groups, and for TimesTen cache tables in user managed cache groups that use the PROPAGATE cache table attribute, foreign keys in cache tables that use the ON DELETE CASCADE cache table attribute must be a proper subset of the foreign keys in the cached Oracle Database tables that use the ON DELETE CASCADE attribute. ON DELETE CASCADE actions on the cached Oracle Database tables are applied to the TimesTen cache tables as individual deletes. ON DELETE CASCADE actions on the cache tables are applied to the cached Oracle Database tables as a cascaded operation.

  • Matching of foreign keys between the TimesTen cache tables and the cached Oracle Database tables is enforced only when the cache group is being created. A cascade delete operation may not work if the foreign keys on the cached Oracle Database tables are altered after the cache group is created.

See the CREATE CACHE GROUP statement in Oracle TimesTen In-Memory Database SQL Reference for more information about the ON DELETE CASCADE cache table attribute.

UNIQUE HASH ON cache table attribute

The UNIQUE HASH ON cache table attribute can be specified for cache tables in any cache group type. UNIQUE HASH ON specifies that a hash index rather than a range index is created on the primary key columns of the cache table. The columns specified in the hash index must be identical to the columns in the primary key. The UNIQUE HASH ON cache table attribute is also used to specify the size of the hash index.

Example 4-14 Using the UNIQUE HASH ON cache table attribute

The following statement uses the UNIQUE HASH ON cache table attribute on the cache table's definition.

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))
  UNIQUE HASH ON (cust_num) PAGES = 100;

See the CREATE CACHE GROUP statement in Oracle TimesTen In-Memory Database SQL Reference for more information about the UNIQUE HASH ON cache table attribute.

Caching Oracle Database synonyms

You can cache a private synonym in an AWT, SWT or user managed cache group that does not use the AUTOREFRESH cache group attribute. The private synonym can reference a public or private synonym, but it must eventually reference a table because it is the table that is actually being cached.

The table that is directly or indirectly referenced by the cached synonym can be owned by a user other than the Oracle Database user with the same name as the owner of the cache group that caches the synonym. The table must reside in the same Oracle database as the synonym. The cached synonym itself must be owned by the Oracle Database user with the same name as the owner of the cache group that caches the synonym.

Caching Oracle Database LOB data

You can cache Oracle Database large object (LOB) data in TimesTen cache groups. TimesTen caches the data as follows:

  • Oracle Database CLOB data is cached as TimesTen CLOB data.

  • Oracle Database BLOB data is cached as TimesTen BLOB data.

  • Oracle Database NCLOB data is cached as TimesTen NCLOB data.

Example 4-15 Caching Oracle Database LOB data

Create a table in the Oracle database that has LOB fields.

CREATE TABLE t (
  i INT NOT NULL PRIMARY KEY
  , c CLOB
  , b BLOB
  , nc NCLOB);

Insert values into the Oracle Database table. The values are implicitly converted to LOB data types.

INSERT INTO t VALUES (1
  , RPAD('abcdefg8', 2048, 'abcdefg8')
  , HEXTORAW(RPAD('123456789ABCDEF8', 4000, '123456789ABCDEF8'))
  , RPAD('abcdefg8', 2048, 'abcdefg8')
);

1 row inserted.

Create a dynamic AWT cache group and start the replication agent.

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP cg1 
  FROM t
 (i INT NOT NULL PRIMARY KEY
  , c CLOB
  , b BLOB
  , nc NCLOB);

CALL ttrepstart;

Load the data dynamically into the TimesTen cache group.

SELECT * FROM t WHERE i = 1;

I:    1
C:    abcdefg8abcdefg8abcdefg8...
B:    123456789ABCDEF8123456789...
NC:   abcdefg8abcdefg8abcdefg8...

1 row found.

Restrictions on caching Oracle Database LOB data

These restrictions apply to caching Oracle Database LOB data in TimesTen cache groups:

  • Column size is enforced when a cache group is created. VARBINARY, VARCHAR2 and NVARCHAR2 data types have a size limit of 4 megabytes. Values that exceed the user-defined column size are truncated at run time without notification.

  • Empty values in fields with CLOB and BLOB data types are initialized but not populated with data. Empty CLOB and BLOB fields are treated as follows:

    • Empty LOB fields in the Oracle database are returned as NULL values.

    • Empty BLOB fields are loaded into the TimesTen cache as NULL values.

    • Empty VARCHAR2 and VARBINARY fields in the TimesTen cache are propagated as NULL values.

In addition, cache groups that are configured for autorefresh operations have these restrictions on caching LOB data:

  • When LOB data is updated in the Oracle database by OCI functions or the DBMS_LOB PL/SQL package, the data is not automatically refreshed in the TimesTen cache group. This occurs because TimesTen caching depends on Oracle Database triggers, and Oracle Database triggers are not executed when these types of updates occur. TimesTen does not notify the user that updates have occurred without being refreshed in TimesTen. When the LOB is updated through a SQL statement, a trigger is fired and autorefresh brings in the change.

  • Autorefresh operations update a complete row in the TimesTen cache. Thus, the cached LOB data may appear to be updated in TimesTen when no change has occurred in the LOB data in the Oracle database.

Implementing aging in a cache group

You can define an aging policy for a cache group that specifies the aging type, the aging attributes, and the aging state. TimesTen supports two aging types, least recently used (LRU) aging and time-based aging.

LRU aging deletes the least recently used or referenced data based on a specified database usage range. Time-based aging deletes data based on a specified data lifetime and frequency of the aging process. You can use both LRU and time-based aging in the same TimesTen database, but you can define only one aging policy for a particular cache group.

An aging policy is specified in the cache table definition of the root table in a CREATE CACHE GROUP statement and applies to all cache tables in the cache group because aging is performed at the cache instance level. When rows are deleted from the cache tables by aging out, the rows in the cached Oracle Database table are not deleted.

You can add an aging policy to a cache group by using an ALTER TABLE statement on the root table. You can change the aging policy of a cache group by using ALTER TABLE statements on the root table to drop the existing aging policy and then add a new aging policy.

This section describes cache group definitions that contain an aging policy. The topics include:

LRU aging

LRU aging enables you to maintain the amount of memory used in a TimesTen database within a specified threshold by deleting the least recently used data. LRU aging can be defined for all cache group types except explicitly loaded autorefresh cache groups. LRU aging is defined by default on dynamic cache groups.

Define an LRU aging policy for a cache group by using the AGING LRU clause in the cache table definition of the CREATE CACHE GROUP statement. Aging occurs automatically if the aging state is set to its default of ON.

Example 4-16 Defining an LRU aging policy on a cache group

The following statement defines an LRU aging policy on the AWT cache group new_customers:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))
AGING LRU ON;

Use the ttAgingLRUConfig built-in procedure to set the LRU aging attributes as a user with the ADMIN privilege. The attribute settings apply to all tables in the TimesTen database that have an LRU aging policy defined and an aging state of ON.

The following are the LRU aging attributes:

  • LowUsageThreshold: The TimesTen database's space usage (the ratio of the permanent partition's in-use size over the partition's allocated size) at or below which LRU aging is deactivated. The default low usage threshold is .8 (80 percent).

  • HighUsageThreshold: The TimesTen database's space usage above which LRU aging is activated. The default high usage threshold is .9 (90 percent).

  • AgingCycle: The frequency in which aging occurs, in minutes. The default aging cycle is 1 minute.

Example 4-17 Setting the LRU aging attributes

The following built-in procedure call specifies that the aging process checks every 5 minutes to see if the TimesTen database's permanent partition space usage is above 95 percent. If it is, the least recently used data is automatically aged out or deleted until the space usage is at or below 75 percent.

CALL ttAgingLRUConfig(.75, .95, 5);

If you set a new value for AgingCycle after an LRU aging policy has been defined on a cache group, the next time aging occurs is based on the current system time and the new aging cycle. For example, if the original aging cycle was 15 minutes and LRU aging occurred 10 minutes ago, aging is expected to occur again in 5 minutes. However, if you change the aging cycle to 30 minutes, aging next occurs 30 minutes from the time you call the ttAgingLRUConfig built-in procedure with the new aging cycle setting.

If a row has been accessed or referenced since the last aging cycle, it is not eligible for LRU aging in the current aging cycle. A row is considered to be accessed or referenced if at least one of the following is true:

  • The row is used to build the result set of a SELECT or an INSERT ... SELECT statement.

  • The row has been marked to be updated or deleted in a pending transaction.

In a multiple-table cache group, if a row in a child table has been accessed or referenced since the last aging cycle, then neither the related row in the parent table nor the row in the child table is eligible for LRU aging in the current aging cycle.

The ALTER TABLE statement can be used to perform the following tasks associated with changing or defining an LRU aging policy on a cache group:

  • Change the aging state of a cache group by specifying the root table and using the SET AGING clause.

  • Add an LRU aging policy to a cache group that has no aging policy defined by specifying the root table and using the ADD AGING LRU clause.

  • Drop the LRU aging policy on a cache group by specifying the root table and using the DROP AGING clause.

To change the aging policy of a cache group from LRU to time-based, use an ALTER TABLE statement on the root table with the DROP AGING clause to drop the LRU aging policy. Then use an ALTER TABLE statement on the root table with the ADD AGING USE clause to add a time-based aging policy.

You must stop the cache agent before you add, alter or drop an aging policy on an autorefresh cache group.

Time-based aging

Time-based aging deletes data from a cache group based on the aging policy's specified data lifetime and frequency. Time-based aging can be defined for all cache group types.

Define a time-based aging policy for a cache group by using the AGING USE clause in the cache table definition of the CREATE CACHE GROUP statement. Aging occurs automatically if the aging state is set to its default of ON.

The definitions of the Oracle Database tables that are to be cached in the AWT cache group defined in Example 4-19 are defined in Example 4-18. The Oracle Database tables are owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.

Example 4-18 Oracle Database table definitions

CREATE TABLE orders
(ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
 cust_num     NUMBER(6) NOT NULL,
 when_placed  DATE NOT NULL,
 when_shipped DATE NOT NULL);

CREATE TABLE order_item
(orditem_id NUMBER(12) NOT NULL PRIMARY KEY,
 ord_num    NUMBER(10),
 prod_num   VARCHAR2(6),
 quantity   NUMBER(3));

The companion Oracle Database user of the TimesTen cache manager user must be granted the SELECT privilege on the oratt.orders and oratt.order_item tables in order for the cache manager user to create an AWT cache group that caches these tables. The cache administration user must be granted the INSERT, UPDATE and DELETE Oracle Database privileges for the oratt.orders and oratt.order_item tables for asynchronous writethrough operations to be applied on the Oracle Database.

Example 4-19 Defining a time-based aging policy on a cache group

The following statement defines a time-based aging policy on the AWT cache group ordered_items:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP ordered_items
FROM oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num))
AGING USE when_placed LIFETIME 45 DAYS CYCLE 60 MINUTES ON,
oratt.order_item
 (orditem_id NUMBER(12) NOT NULL,
  ord_num    NUMBER(10),
  prod_num   VARCHAR2(6),
  quantity   NUMBER(3),
  PRIMARY KEY(orditem_id),
  FOREIGN KEY(ord_num) REFERENCES oratt.orders(ord_num));

Cache instances that are greater than 45 days old based on the difference between the current system timestamp and the timestamp in the when_placed column of the oratt.orders table are candidates for aging. The aging process checks every 60 minutes to see if there are cache instances that can be automatically aged out or deleted from the cache tables.

The AGING USE clause requires the name of a non-nullable TIMESTAMP or DATE column used for time-based aging. We refer to this column as the timestamp column.

For each row, the value in the timestamp column stores the date and time when the row was most recently inserted or updated. The values in the timestamp column is maintained by your application. If the value of this column is unknown for particular rows and you do not want those rows to be aged out of the table, define the timestamp column with a large default value.

You can create an index on the timestamp column to optimize performance of the aging process.

You cannot add a column to an existing table and then use that column as the timestamp column because added columns cannot be defined as non-nullable. You cannot drop the timestamp column from a table that has a time-based aging policy defined.

Specify the lifetime in days, hours, minutes or seconds after the LIFETIME keyword in the AGING USE clause.

The value in the timestamp column is subtracted from the current system timestamp. The result is then truncated to the specified lifetime unit (day, hour, minute, second) and compared with the specified lifetime value. If the result is greater than the lifetime value, the row is a candidate for aging.

After the CYCLE keyword, specify the frequency in which aging occurs in days, hours, minutes or seconds. The default aging cycle is 5 minutes. If you specify an aging cycle of 0, aging is continuous.

The ALTER TABLE statement can be used to perform the following tasks associated with changing or defining a time-based aging policy on a cache group:

  • Change the aging state of a cache group by specifying the root table and using the SET AGING clause.

  • Change the lifetime by specifying the root table and using the SET AGING LIFETIME clause.

  • Change the aging cycle by specifying the root table and using the SET AGING CYCLE clause.

  • Add a time-based aging policy to a cache group that has no aging policy defined by specifying the root table and using the ADD AGING USE clause.

  • Drop the time-based aging policy on a cache group by specifying the root table and using the DROP AGING clause.

To change the aging policy of a cache group from time-based to LRU, use an ALTER TABLE statement on the root table with the DROP AGING clause to drop the time-based aging policy. Then use an ALTER TABLE statement on the root table with the ADD AGING LRU clause to add an LRU aging policy.

You must stop the cache agent before you add, alter or drop an aging policy on an autorefresh cache group.

Manually scheduling an aging process

Use the ttAgingScheduleNow built-in procedure to manually start a one-time aging process on a specified table or on all tables that have an aging policy defined. The aging process starts as soon as you call the procedure unless there is already an aging process in progress. Otherwise the manually started aging process begins when the aging process that is in progress has completed. After the manually started aging process has completed, the start of the table's next aging cycle is set to the time when ttAgingScheduleNow was called if the table's aging state is ON.

Example 4-20 Starting a one-time aging process

The following procedure call starts a one-time aging process on the oratt.orders table based on the time ttAgingScheduleNow is called:

CALL ttAgingScheduleNow('oratt.orders');

Rows in the oratt.orders root table that are candidates for aging are deleted as well as related rows in the oratt.order_item child table.

When you call the ttAgingScheduleNow built-in procedure, the aging process starts regardless of whether the table's aging state is ON or OFF. If you want to start an aging process on a particular cache group, specify the name of the cache group's root table when you call the procedure. If ttAgingScheduleNow is called with no parameters, it starts an aging process and then resets the start of the next aging cycle on all tables in the TimesTen database that have an aging policy defined.

Calling ttAgingScheduleNow does not change the aging state of any table. If a table's aging state is OFF when you call the procedure, the aging process starts, but it is not scheduled to run again after the process has completed. To continue aging a table whose aging state is OFF, you must call ttAgingScheduleNow again or change the table's aging state to ON.

To manually control aging on a cache group, disable aging on the root table by using an ALTER TABLE statement with the SET AGING OFF clause. Then call ttAgingScheduleNow to start an aging process on the cache group.

Configuring a sliding window

You can use time-based aging to implement a sliding window for a cache group. In a sliding window configuration, new rows are inserted into and old rows are deleted from the cache tables on a regular schedule so that the tables contain only the data that satisfies a specific time interval.

You can configure a sliding window for a cache group by using incremental autorefresh mode and defining a time-based aging policy. The autorefresh operation checks the timestamp of the rows in the cached Oracle Database tables to determine whether new data should be refreshed into the TimesTen cache tables. The system time and the time zone must be identical on the Oracle Database and TimesTen systems.

If the cache group does not use incremental autorefresh mode, you can configure a sliding window by using a LOAD CACHE GROUP, REFRESH CACHE GROUP, or INSERT statement, or a dynamic load operation to bring new data into the cache tables.

Example 4-21 Defining a cache group with sliding window properties

The following statement configures a sliding window on the read-only cache group recent_shipped_orders:

CREATE READONLY CACHE GROUP recent_shipped_orders
AUTOREFRESH MODE INCREMENTAL INTERVAL 1440 MINUTES STATE ON
FROM oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num))
AGING USE when_shipped LIFETIME 30 DAYS CYCLE 24 HOURS ON;

New data in the oratt.orders cached Oracle Database table are automatically refreshed into the oratt.orders TimesTen cache table every 1440 minutes. Cache instances that are greater than 30 days old based on the difference between the current system timestamp and the timestamp in the when_shipped column are candidates for aging. The aging process checks every 24 hours to see if there are cache instances that can be aged out of the cache tables. Therefore, this cache group stores orders that have been shipped within the last 30 days.

The autorefresh interval and the lifetime used for aging determine the duration that particular rows remain in the cache tables. It is possible for data to be aged out of the cache tables before it has been in the cache tables for its lifetime. For example, for a read-only cache group if the autorefresh interval is 3 days and the lifetime is 30 days, data that is already 3 days old when it is refreshed into the cache tables is deleted after 27 days because aging is based on the timestamp stored in the rows of the cached Oracle Database tables that gets loaded into the TimesTen cache tables, not when the data is refreshed into the cache tables.

Dynamic cache groups

The data in a dynamic cache group is loaded on demand. For example, a call center application may not want to preload all of its customers' information into TimesTen as it may be very large. Instead it can use a dynamic cache group so that a specific customer's information is loaded only when needed such as when the customer calls or logs onto the system.

Any system managed cache group type (read-only, AWT, SWT) can be defined as a dynamic cache group. A user managed cache group can be defined as a dynamic cache group unless it uses both the AUTOREFRESH cache group attribute and the PROPAGATE cache table attribute.

Use the CREATE DYNAMIC CACHE GROUP statement to create a dynamic cache group.

Example 4-22 Dynamic read-only cache group

This following statement creates a dynamic read-only cache group online_customers that caches the oratt.customer table:

CREATE DYNAMIC READONLY CACHE GROUP online_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num));

With an explicitly loaded cache group, data is initially loaded into the cache tables from the cached Oracle Database tables using a LOAD CACHE GROUP statement. With a dynamic cache group, data may also be loaded into the cache tables using a LOAD CACHE GROUP statement. However, with a dynamic cache group, data is typically loaded automatically when its cache tables are referenced by a SELECT, INSERT, or UPDATE statement and the data is not found in the tables resulting in a cache miss. See "Dynamically loading a cache instance" for more information.

With both explicitly loaded and dynamic cache groups, a LOAD CACHE GROUP statement loads into their cache tables qualified data that exists in the cached Oracle Database tables but not in the TimesTen cache tables. However, if a row exists in a cache table but a newer version exists in the cached Oracle Database table, a LOAD CACHE GROUP statement does not load that row into the cache table even if it satisfies the predicate of the statement.

By contrast, a REFRESH CACHE GROUP statement reloads qualifying rows that exists in the cache tables, effectively refreshing the content of the cache. For an explicitly loaded cache group, the rows that are refreshed are all the rows that satisfy the predicate of the REFRESH CACHE GROUP statement. However, for a dynamic cache group, the rows that are refreshed are the ones that satisfy the predicate and already exist in the cache tables. In other words, rows that end up being refreshed are the ones that have been updated or deleted in the cached Oracle Database table, but not the ones that have been inserted. Therefore, a refresh operation processes only the rows that are already in the cache tables. No new rows are loaded into the cache tables of a dynamic cache group as a result of a refresh.

The data in the cache instance of a dynamic read-only cache group is consistent with the data in the corresponding rows of the Oracle Database tables. At any instant in time, the data in a cache instance of an explicitly loaded cache group is consistent with the data in the corresponding rows of the Oracle Database tables, taking into consideration the state and the interval settings for autorefresh.

The data in a dynamic cache group is subject to aging as LRU aging is defined by default. You can use the ttAgingLRUConfig built-in procedure to override the default or current LRU aging attribute settings for the aging cycle and TimesTen database space usage thresholds. Alternatively, you can define time-based aging on a dynamic cache group to override LRU aging. Rows in a dynamic AWT cache group must be propagated to the Oracle database before they become candidates for aging.

Global cache groups

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 the 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. Also, the contents of the updated cache table are inconsistent between the TimesTen databases.

A TimesTen cache grid prevents this problem by providing users with Oracle databases a means to horizontally scale out global cache groups across multiple systems with read/write data consistency across the TimesTen databases. A cache grid is a set of TimesTen databases that collectively manage the application data cached in global cache groups.

Tables that are cached in separate cache groups within different TimesTen databases must be cached in global cache groups in order for the cache grid to manage consistency of the cache instances across the grid members when updates are committed on the cache tables of the global cache group. In a cache grid, only one copy of a cache instance is allowed to be present in the entire grid at any moment in time. Each cache instance in a global cache group is owned by the grid member where it is currently located. Only the cache grid member that owns the cache instance has the right to update the data. The TimesTen cache grid tracks the ownership for each cache instance, so that it can quickly locate the grid member where each cache instance is currently located and ensure that the same cache instance is not concurrently present in multiple grid members. However, another grid member can obtain ownership of the cache instance from the current owner.

Global cache groups can be defined as dynamic AWT cache groups or as explicitly loaded AWT cache groups.

This section includes the following topics:

Dynamic global cache groups

The following statement is the definition of the Oracle Database table that are to be cached in the dynamic AWT global cache group that is created in Example 4-23. The Oracle Database table is owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.

CREATE TABLE subscriber
(subscriberid       NUMBER(10) NOT NULL PRIMARY KEY,
 name               VARCHAR2(100) NOT NULL,
 minutes_balance    NUMBER(5) NOT NULL,
 last_call_duration NUMBER(4) NOT NULL);

The Oracle Database user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.subscriber table so that the cache manager user can create an AWT cache group that caches this table. The cache administration user must be granted the INSERT, UPDATE and DELETE Oracle Database privileges for the oratt.subscriber table for asynchronous writethrough operations to be applied to the Oracle Database.

Use the CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP statement to create a dynamic AWT global cache group.

Example 4-23 Dynamic global cache group

The following statement creates a dynamic AWT global cache group subscriber_accounts that caches the oratt.subscriber table:

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP subscriber_accounts
FROM oratt.subscriber
 (subscriberid       NUMBER(10) NOT NULL PRIMARY KEY,
  name               VARCHAR2(100) NOT NULL,
  minutes_balance    NUMBER(5) NOT NULL,
  last_call_duration NUMBER(4) NOT NULL);

When a subscriber to a prepaid telephone account makes a call, the cache instance that contains the subscriber's account balance is loaded into the oratt.subscriber cache table of the subscriber_accounts global cache group within one of the cache grid members. The query for the account balance information first searches the grid member on which the query is issued. If the cache tables on the local grid member do not contain data that satisfies a query, then the cache instance is transferred from other grid members to the local grid member in a grid data transfer operation. If the grid does not contain the cache instance that satisfies the query, data is loaded from the Oracle Database tables. When data is loaded into the local grid member from the Oracle Database tables, this operation is called a dynamic load. The grid member that the cache instance is loaded into becomes the owner of the cache instance. Other grid members cannot access the cache instance until the owner has updated the balance of minutes and the duration of the last call, and the committed update has been propagated to the cached Oracle Database table.

To ensure consistency among the grid members, an Oracle Database table that is cached in a global cache group in a TimesTen database should not also be cached in a local cache group in another TimesTen database within the same cache grid. In addition, the Oracle Database table should not be cached in a global cache group in another TimesTen database within a different cache grid.

For cache tables in a dynamic global cache group, a particular cache instance can be read or updated by only one grid member at a time. This grid member is referred to as the owner of the cache instance. When the owner no longer has a pending transaction on any row of the cache instance, another grid member can take ownership by reading or updating that instance. The owner relinquishes ownership of a cache instance when the instance has been deleted from that grid member as a result of:

  • Aging

  • A DELETE statement issued on the cache table

  • An UNLOAD CACHE GROUP statement issued on the cache group

  • A request from another grid member to take ownership of that instance

The owner relinquishes ownership of all its cache instances if that grid member detaches from its cache grid.

Read data consistency between nodes of a cache grid is guaranteed only when using serializable isolation level on the node where cache instances are being read. When using the default read committed isolation level, a connection on a grid node that is reading a cache instance may see a data value that has been subsequently updated to a new value by another connection in the same or a different node.

The cache tables in a dynamic global cache group can be populated using any of these operations:

  • Dynamic load operation

  • Grid data transfer operation

  • INSERT statement on the cache tables (but not an INSERT INTO ... SELECT FROM statement)

  • LOAD CACHE GROUP ... COMMIT EVERY n ROWS statement (can only be used if all the other grid members do not own any of the cache instances to be loaded)

See "Dynamically loading a cache instance" for information about a dynamic load operation.

A grid member can take ownership of a cache instance that is currently owned by another grid member by using any of the following operations:

  • Grid data transfer operation

  • Dynamic load operation

  • LOAD CACHE GROUP ... WITH ID statement

A REFRESH CACHE GROUP statement can be issued on a dynamic global cache group only if it contains a WITH ID clause.

You can set the CacheGridMsgWait connection attribute to the maximum number of seconds that a grid member waits for the owner to relinquish the instance. The owner cannot relinquish ownership of a cache instance if it has a pending transaction on any row of the instance. The default maximum wait time is 60 seconds.

An INSERT statement issued on a cache table in a dynamic global cache group fails if the unique key value in the inserted row already exists in the cached Oracle Database table.

When using a LOAD CACHE GROUP ... COMMIT EVERY n ROWS statement, if any of the cache instances to be loaded within a transaction are owned by another grid member, an error is returned. The transaction is then rolled back and no cache instances are loaded within the failed transaction.

To prevent conflicts that can occur if you update the same row in a TimesTen cache table and the cached Oracle Database table concurrently, update only the cache table. The cached Oracle Database table should not be updated directly.

A TimesTen database that is a member of a cache grid can contain local and global cache groups. Only cache tables in global cache groups are guaranteed to be consistent among the grid members.

Explicitly loaded global cache groups

Cache instances in an explicitly loaded global cache group are initially loaded from the Oracle database. You can reload the cache instances by issuing another LOAD CACHE GROUP statement or reload a single cache instance with the REFRESH CACHE GROUP...WITH ID statement.

If the cache tables on the local grid member do not contain data that satisfies a query, then the cache instance is transferred from other grid members to the local grid member in a grid data transfer operation. If the grid does not contain the cache instance that satisfies the query, data is not loaded from the Oracle Database tables. The query returns no results.

Use the CREATE ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP statement to create an explicitly loaded global cache group. Note that this SQL statement is the same as the SQL statement that creates a dynamic global cache group except that the DYNAMIC keyword is omitted.

Example 4-24 Creating an explicitly loaded global cache group

The following statement creates an explicitly loaded AWT global cache group subscriber_accounts that caches the oratt.subscriber table:

CREATE ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP subscriber_accounts
FROM oratt.subscriber
 (subscriberid       NUMBER(10) NOT NULL PRIMARY KEY,
  name               VARCHAR2(100) NOT NULL,
  minutes_balance    NUMBER(5) NOT NULL,
  last_call_duration NUMBER(4) NOT NULL);

The cache tables in an explicitly loaded global cache group can be populated at any time using any of these operations:

  • Grid data transfer operation

  • INSERT statement on the cache tables (but not an INSERT INTO ... SELECT FROM statement)

  • LOAD CACHE GROUP statement. The statement can be used only if other grid members do not own any of the cache instances to be loaded into the local grid member.

  • REFRESH CACHE GROUP ... WITH ID statement

Aging is disabled by default on an explicitly loaded global cache group.

Set the CacheGridMsgWait connection attribute to the maximum number of seconds that a grid member waits for the owner to relinquish the instance. The owner cannot relinquish ownership of a cache instance if it has a pending transaction on any row of the instance. The default maximum wait time is 60 seconds.

If a query that specifies a primary key or foreign key is issued on a cache table where there is no row that satisfies the query, the cache instance is not transferred to the cache table.

If a row is inserted into a child table whose parent table exists in the cache grid, the cache instance is transferred to the member with the child table. An insert into a child table whose parent is not in the cache grid fails.

Start the replication agent

After you have created a global cache group, start the replication agent on the TimesTen database as the cache manager user, if it is not already running:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttRepStart;
Command> exit

Attach a TimesTen database to a cache grid

All standalone TimesTen databases, and the active and standby databases of an active standby pair that contain global cache groups must attach to the cache grid that they are associated with in order to update the cache tables of the global cache groups. Attaching the databases to the grid allow the databases to become members of the grid so that cache instances in the cache tables of the global cache groups can maintain consistency among the databases within the grid.

Example 4-25 Attaching a TimesTen database to a cache grid

Attach the first standalone database to the ttGrid cache grid that it is associated with by calling the ttGridAttach built-in procedure as the cache manager user. The node number for a standalone TimesTen database is 1. Calling the ttGridAttach built-in procedure automatically starts the cache agent on the TimesTen database if it is not already running.

In this example, alone1 is a name that uniquely identifies the grid member, sys1 is the host name of the TimesTen system where the first standalone database resides, and 5001 is the TCP/IP port for the first standalone database's cache agent process:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttGridAttach(1,'alone1','sys1',5001);
Command> exit

Specify a port for the cache agent on each TimesTen database that attaches to the grid. There is no default port number. A typical grid uses the same port for each member of the grid, but different ports can be specified if desired. The port assignment is a grid member property. The only way to change the properties of a grid member after it has been attached to the grid is to destroy the grid and re-create it. Use the ttGridNodeStatus built-in procedure to determine the members of a grid and their ports.

See "Configuring a cache grid" for more information about a cache grid.