Skip Headers
Oracle® In-Memory Database Cache User's Guide
11g Release 2 (11.2.2)

Part Number E21634-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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 data to cache in the TimesTen database. When you create a cache group, cache tables are created in the TimesTen database that correspond to the Oracle tables being cached.

A separate table definition must be specified in the cache group definition for each Oracle 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 table. The cache table can contain all or a subset of the columns and rows of the cached Oracle table. Each TimesTen cache table must have a primary key.

Before you define the cache group table, create the Oracle tables that are to be cached. Each table should be either:

A TimesTen database can contain multiple cache groups. A cache group can contain one or more cache tables. An Oracle 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 table. Otherwise, it can cause unique constraint failures in the cache table that do not occur in the cached Oracle 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 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 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 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 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 tables while the TimesTen database was unavailable are automatically refreshed to the TimesTen cache tables.

The following are the definitions of the Oracle tables that will be cached in the read-only cache groups that are defined in Example 4-1, Example 4-10, Example 4-11, Example 4-19 and Example 4-20. The Oracle 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 Oracle 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 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 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 Oracle user with the same name as the TimesTen cache manager user must be granted the INSERT, UPDATE and DELETE privileges on the Oracle 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 tables.

If you manually created the Oracle objects used to enforce the predefined behaviors of an autorefresh cache group as described in "Manually create Oracle objects used to manage caching of Oracle data", 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 table that is cached in the read-only cache group. See "Manually creating Oracle 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 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 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 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 tables as shown in Figure 4-6.

Figure 4-6 Asynchronous writethrough cache group

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

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

The following is the definition of the Oracle table that will be cached in the AWT cache groups that are defined in Example 4-2, Example 4-12 and Example 4-14. The Oracle 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 Oracle user with the same name as 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 Oracle cache administration user must be granted the INSERT, UPDATE and DELETE privileges on the oratt.customer table for asynchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.

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

Configuring parallel propagation to Oracle 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 tables in commit order.

Parallel propagation is supported for the following configurations:

  • AWT cache groups in a standalone database

  • AWT cache groups with aging

  • AWT cache groups in an active standby pair replication scheme

  • AWT cache groups in a cache grid

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 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 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 is 2 times the setting for ReplicationParallelism. For example, if ReplicationParallelism=3, the number of threads that apply changes to Oracle 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. 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 tables is 6. This enables the number of threads used to be different for parallel replication and parallel propagation to Oracle tables.

Note:

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

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.


Note:

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

Any unique index, unique constraint, or foreign key constraint on columns in Oracle tables that are to be cached must also be created on the cached tables within TimesTen. If you cannot create the appropriate unique index, unique constraint, or foreign key constraint on the cached table, either disable parallel propagation or do not cache these columns. For example, if a table referenced by the foreign key constraint is too large to cache in TimesTen, you would not be able to create the foreign key.

Foreign keys in Oracle tables that are to be cached must have indexes created on the foreign keys. Consider these Oracle 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);

Parallel propagation cannot identify the case where two transactions have an implicit dependency if the transactions act on different cache groups. This occurs because there are no foreign keys on tables that are in different cache groups, even when there are foreign keys in the Oracle tables. Do not use parallel propagation to Oracle when your data model includes foreign keys in Oracle that cannot be created on cache tables.

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 tables resumes after the agent is restarted or is able to reconnect 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 Oracle cause the transaction in the Oracle database to be rolled back. For example, an update on Oracle may fail because of a unique constraint violation. Transactions that contain execution errors are not retried.

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

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

    • An update is committed on a cache table in an AWT cache group. The same update is committed on the cached Oracle table using a passthrough operation. The cache table update, which is automatically and asynchronously propagated to Oracle, may overwrite the passed through update that was processed directly on the cached Oracle table depending on when the propagated update and the passed through update is processed on Oracle.

    • In two separate TimesTen databases (DB1 and DB2), different AWT cache groups cache the same Oracle 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 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.

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

  • TimesTen does not detect or resolve update conflicts that occur on Oracle. Committed updates made directly on a cached Oracle table may be overwritten by a committed update made on the TimesTen cache table when the cache table update is propagated to Oracle.

  • 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 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 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 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. Oracle, 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 Oracle, it causes a unique constraint violation with the row that has the value 2 in the NUMBER column of the cached Oracle table.

Reporting Oracle execution errors for AWT cache groups

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

Execution errors are reported to the TimesTenDatabaseFileName.awterrs text file that resides in the same directory as the TimesTen database checkpoint files.

Note:

See "Oracle errors reported by TimesTen for AWT" in Oracle TimesTen In-Memory Database Troubleshooting Guide for information about the contents of this file.

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 tables as shown in Figure 4-7.

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 Oracle, the application must roll back the transaction in TimesTen. If the Oracle 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 tables. To manually resynchronize the cache tables with the cached Oracle 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. You can also resynchronize the cache tables with the cached Oracle tables by reloading the accompanying cache groups.

The following is the definition of the Oracle table that will be cached in the SWT cache group that is defined in Example 4-6. The Oracle 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 Oracle user with the same name as 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 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 table.

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

Example 4-6 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));

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 views or materialized views.

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. For example:

  • 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 tables are propagated or refreshed to each other.

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

  • You can specify the READONLY or the PROPAGATE cache table attribute on individual cache tables in a user managed cache group to define read-only or synchronous writethrough behavior at the table level.

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

The following are the definitions of the Oracle tables that will be cached in the user managed cache groups that are defined in Example 4-7 and Example 4-8. The Oracle 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-7 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"

All columns except region are cached. Only customers whose customer ID is greater than or equal to 1001 are cached. Committed updates on the oratt.active_customer cache table or the oratt.active_customer cached Oracle table are transmitted to the corresponding table.

The Oracle user with the same name as 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 table to the TimesTen cache table. This Oracle 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 table.

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

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

If you manually created the Oracle 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 objects used to manage caching of Oracle data", 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 table that is cached in the user managed cache group.

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

Example 4-8 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 Oracle user with the same name as 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. This Oracle 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 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 tables. The oratt.cust_interests child table uses the READONLY cache table attribute so that it cannot be updated directly.

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 are automatically and synchronously propagated to the cached Oracle table such that:

  1. The commit is first attempted in the Oracle database. If the commit fails in Oracle, the commit is not attempted in the TimesTen database and the application must roll back the TimesTen transaction. As a result, the Oracle database never misses updates committed in TimesTen.

  2. If the commit succeeds in the Oracle database, it is then attempted in the TimesTen database. 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 tables.

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

By default, a cache table in a user managed cache group is created with the NOT PROPAGATE cache table attribute such that committed updates on the cache table are not propagated to the cached Oracle table.

When a cache table uses the PROPAGATE cache table attribute, you may occasionally need to commit updates on the cache table that you do not want propagated to the cached Oracle table. Use the ttCachePropagateFlagSet built-in procedure to disable automatic propagation so that committed updates on a cache table is not propagated to the cached Oracle table.

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 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 table. Therefore, updates should only be performed directly on the TimesTen cache tables or the cached Oracle tables, but not both.

In Example 4-7, the oratt.active_customer cache table uses the PROPAGATE cache table attribute.

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.

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.

In Example 4-8, the oratt.cust_interests cache table uses the READONLY cache table attribute.

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 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 tables are automatically refreshed to the TimesTen cache tables based on the cache group's autorefresh interval. Incremental autorefresh mode uses Oracle objects to track committed updates on cached Oracle tables. See "Managing a caching environment with Oracle 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 tables.

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

When using incremental autorefresh mode, committed updates on cached Oracle tables are tracked in change log tables in the Oracle database. Under certain circumstances, it is possible for some of the 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 tables are not tracked.

When the cache group's autorefresh state is PAUSED, committed updates on the cached Oracle 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 table is not automatically refreshed to the TimesTen cache table. Before issuing a TRUNCATE TABLE statement on a cached Oracle 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 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 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 table, while each row update is executed separately in TimesTen. If the unique index exists on the Oracle table that is being cached, then uniqueness is enforced on the Oracle table and does not need to be verified again in TimesTen.

In Example 4-7, 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-9 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 objects for autorefresh cache groups

If you manually created the Oracle objects used to enforce the predefined behaviors of an autorefresh cache group as described in "Manually create Oracle objects used to manage caching of Oracle data", 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 table that is cached in the autorefresh cache group. These Oracle objects are used to track updates on the cached Oracle 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-10 Creating a read-only cache group when Oracle 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 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-8, 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 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 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-11 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 tables that use the ON DELETE CASCADE attribute. ON DELETE CASCADE actions on the cached Oracle 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 tables as a cascaded operation.

  • Matching of foreign keys between the TimesTen cache tables and the cached Oracle 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 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-12 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 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 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 user with the same name as the owner of the cache group that caches the synonym.

Caching Oracle LOB data

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

Example 4-13 Caching Oracle 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 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 VARCHAR2(4194304 BYTE)
  , b VARBINARY(4194304)
  , nc NVARCHAR2(2097152));

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 LOB data

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

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

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 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-14 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-15 Setting the LRU aging attributes

The following 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 ttAgingLRUConfig 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 tables that will be cached in the AWT cache group defined in Example 4-17 are defined in Example 4-16. The Oracle 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-16 Oracle 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 Oracle user with the same name as 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 Oracle cache administration user must be granted the INSERT, UPDATE and DELETE privileges on the oratt.orders and oratt.order_item tables for asynchronous writethrough operations to occur from the TimesTen cache tables to the cached Oracle tables.

Example 4-17 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-18 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 ttAgingScheduleNow, 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 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 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-19 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 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 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-20 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 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 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 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 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 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 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 Oracle before they become candidates for aging.

Global cache groups

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

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 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 table that will be cached in the dynamic AWT global cache group that is created in Example 4-21. The Oracle 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 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 Oracle cache administration user must be granted the INSERT, UPDATE and DELETE privileges on the oratt.subscriber table for asynchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.

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

Example 4-21 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 tables. When data is loaded into the local grid member from the Oracle 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 table.

To ensure consistency among the grid members, an Oracle 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 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 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 table concurrently, update only the cache table. The cached Oracle 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 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-22 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-23 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 is used to uniquely identify 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.