4 Defining Cache Groups

Single-table cache group

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

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

Figure 4-1 Cache group with a single table

Description of "Figure 4-1 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 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 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 Solution: Create two cache groups"

Read-only cache group

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

Figure 4-5 Read-only cache group

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

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

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

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

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

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

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

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

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

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

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

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

Asynchronous writethrough (AWT) cache group

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

Figure 4-6 Asynchronous writethrough cache group

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

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

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

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

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

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

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

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

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

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

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

• Configuring parallel propagation to Oracle Database tables

• What an AWT cache group does and does not guarantee

• Restrictions with AWT cache groups

• Reporting Oracle Database permanent errors for AWT cache groups

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

% ttAdmin -repStart cachealone1

Command> call ttRepStop;

% ttAdmin -repStop cachealone1

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

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

% ttAdmin -repPolicy always cachealone1

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

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

CREATE INDEX idx_1 ON child(c2);
CREATE INDEX idx_2 ON grchild(c2);
CREATE INDEX idx_3 ON grchild(c3);

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

SQL> CREATE INDEX order_idx on ordertab (custid);

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

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

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

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

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

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

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

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

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

Synchronous writethrough (SWT) cache group

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

Figure 4-7 Synchronous writethrough cache group

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

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

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

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

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

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

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

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

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

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

User managed cache group

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

• You can specify the READONLY cache table attribute on individual cache tables in a user managed cache group to define read-only behavior where the data is refreshed on TimesTen from the Oracle database at the table level.

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

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

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

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

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

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

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

• READONLY cache table attribute

• PROPAGATE cache table attribute

• Examples of user managed cache groups

Examples of user managed cache groups

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

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

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

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

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

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

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

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

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

Figure 4-8 Single-table user managed cache group

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

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

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

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

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

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

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

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

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

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

Figure 4-9 Multiple-table user managed cache group

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

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

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

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

AUTOREFRESH cache group attribute

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

The following are the default settings of the autorefresh attributes:

• The autorefresh mode is incremental.

• The autorefresh interval is 5 minutes.

• The autorefresh state is PAUSED.

TimesTen supports two autorefresh modes:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  user_name.table_name and user_name.table_name.column_name

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

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

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

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

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

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

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

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;

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

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

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

ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE PAUSED;

Using a WHERE clause

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  user_name.table_name and user_name.table_name.column_name

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

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)

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

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

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;

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

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.

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

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

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

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

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

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

UNIQUE HASH ON cache table attribute

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

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.

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.

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 region's in-use size over the region'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.

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

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

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

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

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

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

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

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

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

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

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

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

Time-based aging

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

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

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

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

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

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

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

Command> CALL ttAgingScheduleNow('oratt.orders');

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

When you call the ttAgingScheduleNow built-in procedure, the aging process starts regardless of whether the table's aging state is ON or OFF. If you want to start an aging process on a particular cache group, specify the name of the cache group's root table when you call the built-in procedure. If the ttAgingScheduleNow built-in procedure 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 the ttAgingScheduleNow built-in procedure does not change the aging state of any table. If a table's aging state is OFF when you call the built-in procedure, the aging process starts, but it is not scheduled to run again after the process has completed. To continue aging a table whose aging state is OFF, you must call ttAgingScheduleNow again or change the table's aging state to ON.

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

Configuring a sliding window

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

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

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

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

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

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

Dynamic global cache groups

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

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

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

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

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

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

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

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

• Aging

• A DELETE statement issued on the cache table

• An UNLOAD CACHE GROUP statement issued on the cache group

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

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

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

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

• Dynamic load operation

• Grid data transfer operation

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

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

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

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

• Grid data transfer operation

• Dynamic load operation

• LOAD CACHE GROUP ... WITH ID statement

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

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

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

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

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

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

Explicitly loaded global cache groups

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

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

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

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

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.

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