CREATE CACHE GROUP

The CREATE CACHE GROUP statement:

  • Creates the table defined by the cache group.

  • Loads all new information associated with the cache group in the appropriate system tables.

A cache group is a set of tables related through foreign keys that cache data from tables in an Oracle database. There is one root table that does not reference any of the other tables. All other cache tables in the cache group reference exactly one other table in the cache group. In other words, the foreign key relationships form a tree.

A cache table is a set of rows satisfying the conditions:

  • The rows constitute a subset of the rows of a vertical partition of an Oracle database table.

  • The rows are stored in a TimesTen table with the same name as the Oracle database table.

If a database has more than one cache group, the cache groups must correspond to different Oracle database (and TimesTen) tables.

Cache group instance refers to a row in the root table and all the child table rows related directly or indirectly to the root table rows.

A cache group can be either system managed or user managed.

A system managed cache group is fully managed by TimesTen and has fixed properties. System managed cache group types include:

  • Read-only cache groups are updated in the Oracle database, and the updates are propagated from the Oracle database to the cache.

  • Asynchronous writethrough (AWT) cache groups are updated in the cache and the updates are propagated to the Oracle database. Transactions continue executing on the cache without waiting for a commit on the Oracle database.

  • Synchronous writethrough (SWT) cache groups are updated in the cache and the updates are propagated to the Oracle database. Transactions are committed on the cache after notification that a commit has occurred on the Oracle database.

Because TimesTen manages system managed cache groups, including loading and unloading the cache group, certain statements and clauses cannot be used in the definition of these cache groups, including:

  • WHERE clauses in AWT and SWT cache group definitions

  • READONLY, PROPAGATE and NOT PROPAGATE in cache table definitions

  • AUTOREFRESH in AWT and SWT cache group definitions

The FLUSH CACHE GROUP and REFRESH CACHE GROUP operations are not allowed for AWT and SWT cache groups.

You must stop the replication agent before creating an AWT cache group.

A user managed cache group must be managed by the application or user. PROPAGATE in a user managed cache group is synchronous. The table-level READONLY keyword can only be used for user managed cache groups.

In addition, both TimesTen and Oracle Database must be able to parse all WHERE clauses.

Cache groups can be explicitly or dynamically loaded.

In cache groups that are explicitly loaded, new cache instances are loaded manually into the TimesTen cache tables from the Oracle database tables using a LOAD CACHE GROUP or REFRESH CACHE GROUP statement or automatically using an autorefresh operation.

In a dynamic cache group, new cache instances can be loaded manually into the TimesTen cache tables by using a LOAD CACHE GROUP or on demand using a dynamic load operation. In a dynamic load operation, data is automatically loaded into the TimesTen cache tables from the cached Oracle database tables when a SELECT, UPDATE, DELETE or INSERT statement is issued on one of the cache tables, where the data is not present in the cache table but does exist in the cached Oracle database table. A manual refresh or automatic refresh operation on a dynamic cache group can result in the updating or deleting of existing cache instances, but not in the loading of new cache instances.

Any cache group type (read-only, asynchronous writethrough, synchronous writethrough, user managed) can be defined as an explicitly loaded cache group.

Any cache group type can be defined as a dynamic cache group except a user managed cache group that has both the AUTOREFRESH cache group attribute and the PROPAGATE cache table attribute.

Data in a dynamic cache group is aged out because LRU aging is defined by default. Use the ttAgingLRUConfig and/or the ttAgingTableLRUConfig built-in procedures to override the space usage thresholds for LRU aging. You can also define time-based aging on a dynamic cache group to override LRU aging.

For more information on static and dynamic cache groups, see Cache Groups and Cache Tables in Oracle TimesTen In-Memory Database Cache Guide.

Required Privilege

CREATE CACHE GROUP or CREATE ANY CACHE GROUP and CREATE TABLE (if all tables in the cache group are owned by the current user) or CREATE ANY TABLE (if at least one of the tables in the cache group is not owned by the current user).

Usage with TimesTen Scaleout

Static read-only cache groups with incremental autorefresh are supported.

SQL Syntax: TimesTen Scaleout

For static read-only cache groups in TimesTen Scaleout:
CREATE READONLY CACHE GROUP [Owner.]CacheGroupName
 [AUTOREFRESH 
    [MODE INCREMENTAL]
    [INTERVAL IntervalValue {MINUTE[S] | SECOND[S] | MILLISECOND[S]}]
    [STATE {ON|OFF|PAUSED}]
  ]
  FROM
    [Owner.]TableName (ColumnDefinition[,...][,PRIMARY KEY(ColumnName[,...])])
  [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
  [ParentDistributionClause]
    [WHERE ExternalSearchCondition]
  [,[Owner.]TableName (ColumnDefinition[,...]
    [,PRIMARY KEY(ColumnName[,...])]
    [,FOREIGN KEY(ColumnName[,...])
        REFERENCES RefTableName (ColumnName [,...])[ON DELETE CASCADE]])
 [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [ChildDistributionClause]
   [WHERE ExternalSearchCondition]
 [,...]
 ]

The syntax for the distribution clause for a parent:
ParentDistributionClause::= DISTRIBUTE BY HASH [(ColumnName [,...])] | DUPLICATE

The syntax for the distribution clause for a child:
ChildDistributionClause::= DISTRIBUTE BY HASH [(ColumnName [,...])] |
 DISTRIBUTE BY REFERENCE [(ForeignKeyConstraint)] | DUPLICATE

SQL Syntax: TimesTen Classic

For read-only cache groups:

CREATE [DYNAMIC] [HYBRID] READONLY CACHE GROUP [Owner.]CacheGroupName
 [AUTOREFRESH
  [MODE {INCREMENTAL | FULL}]
  [INTERVAL IntervalValue {MINUTE[S] | SECOND[S] | MILLISECOND[S] }]
  [STATE {ON|OFF|PAUSED}]
 ]
 FROM
  {[Owner.]TableName (
    {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
    [,FOREIGN KEY(ColumnName [,...])
            REFERENCES RefTableName (ColumnName [,...])]
                    [ON DELETE CASCADE])
 [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [AGING {LRU|
         USE ColumnName
         LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}
         [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
        }[ON|OFF]
 ]
 [WHERE ExternalSearchCondition]
} [,...]

For asynchronous writethrough cache groups:

CREATE [DYNAMIC] [ASYNCHRONOUS] WRITETHROUGH CACHE GROUP   [Owner.]CacheGroupName
  FROM
   {[Owner.]TableName (
     {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
     [FOREIGN KEY(ColumnName [,...])
          REFERENCES RefTableName (ColumnName [,...])]
      [ON DELETE CASCADE])
 [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [AGING {LRU|
     USE ColumnName
          LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}
         [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
     }[ON|OFF]
 ]
} [,...]

For synchronous writethrough cache groups:

CREATE [DYNAMIC] SYNCHRONOUS WRITETHROUGH
CACHE GROUP [Owner.]CacheGroupName
 FROM 
   {[Owner.]TableName (
     {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
     [FOREIGN KEY(ColumnName [,...])
            REFERENCES RefTableName (ColumnName [,...])]
   [ON DELETE CASCADE])
 [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [AGING {LRU|
     USE ColumnName
         LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}
         [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
     }[ON|OFF]
 ]
} [,...]

For user managed cache groups:

CREATE [DYNAMIC][USERMANAGED] CACHE GROUP [Owner.]CacheGroupName
 [AUTOREFRESH
   [MODE {INCREMENTAL | FULL}]
   [INTERVAL IntervalValue {MINUTE[S] | SECOND[S] | MILLISECOND[S] }]
   [STATE {ON|OFF|PAUSED}]
 ]
  FROM 
   {[Owner.]TableName (
    {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
    [FOREIGN KEY(ColumnName[,...])
          REFERENCES RefTableName (ColumnName [,...])]
    [ON DELETE CASCADE]
    [,{READONLY | PROPAGATE | NOT PROPAGATE}])
 [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [AGING {LRU|
         USE ColumnName
             LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}
             [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
        }[ON|OFF]
 ]
 [WHERE ExternalSearchCondition]
} [,...]

Parameters

Following are the parameters for the cache group definition before the FROM keyword:

Parameter Description

[Owner.]CacheGroupName

Owner and name assigned to the new cache group.

DYNAMIC

Supported in TimesTen Classic only.

If specified, a dynamic cache group is created.
HYBRID

Supported in TimesTen Classic only.

If specified, a dynamic read-only cache group where the root table does not exist in the Oracle database.

AUTOREFRESH

The AUTOREFRESH parameter automatically propagates changes from the Oracle database to the cache group.

MODE [INCREMENTAL | FULL]

Determines which rows in the cache are updated during an autorefresh. If the INCREMENTAL clause is specified, TimesTen refreshes only rows that have been changed on the Oracle database since the last propagation. If the FULL clause is specified, TimesTen updates all rows in the cache with each autorefresh. The default autorefresh mode is INCREMENTAL.

In TimesTen Scaleout, MODE INCREMENTAL is supported.

INTERVAL IntervalValue

Indicates the interval at which autorefresh should occur in units of minutes, seconds or milliseconds. IntervalValue is an integer value that specifies how often autorefresh should be scheduled, in minutes, seconds, or milliseconds. The default IntervalValue value is 5 minutes. An autorefresh interval set to 0 milliseconds enables continuous autorefresh, where the next autorefresh cycle is scheduled immediately after the last autorefresh cycle has ended. See Automatically Refreshing a Cache Group in the Oracle TimesTen In-Memory Database Cache Guide for more information.

If the specified interval is not long enough for an autorefresh to complete, a runtime warning is generated and the next autorefresh waits until the current one finishes. An informational message is generated in the support log if the wait queue reaches 10.

STATE [ON | OFF | PAUSED]

Specifies whether autorefresh should be ON or OFF or PAUSED when the cache group is created. You can alter this setting later by using the ALTER CACHE GROUP statement. By default, the AUTOREFRESH state is PAUSED.

FROM

Designates one or more table definitions for the cache group.

Everything after the FROM keyword comprises the definitions of the Oracle database tables cached in the cache group. The syntax for each table definition is similar to that of a CREATE TABLE statement. However, primary key constraints are required for the cache group table.

Table definitions have the following parameters.

Parameter Description

[Owner.]TableName

Owner and name to be assigned to the new table. If you do not specify the owner name, your login becomes the owner name for the new table.

ColumnDefinition

Name of an individual column in a table, its data type and whether it is nullable. Each table must have at least one column.

PRIMARY KEY (ColumnName[,...])

Specifies that the table has a primary key. Primary key constraints are required for a cache group. ColumnName is the name of the column that forms the primary key for the table to be created. Up to 16 columns can be specified for the primary key. Cannot be specified with UNIQUE in one specification.

FOREIGN KEY (ColumnName[,...])

Specifies that the table has a foreign key. ColumnName is the name of the column that forms the foreign key for the table to be created.

REFERENCES RefTableName (ColumnName[,...])

Specifies the table which the foreign key is associated with. RefTableName is the name of the referenced table and ColumnName is the name of the column referenced in the table.

[ON DELETE CASCADE]

Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.

READONLY

Specifies that changes cannot be made on the cached table.

PROPAGATE|NOT PROPAGATE

Supported in TimesTen Classic only.

Specifies whether changes to the cached table are automatically propagate to the corresponding Oracle database table at commit time.

UNIQUE HASH ON (HashColumnName)

Specifies that a hash index is created on this table. HashColumnName identifies the column that is to participate in the hash key of this table. The columns specified in the hash index must be identical to the columns in the primary key.

PAGES = PrimaryPages

Sizes the hash index to reflect the expected number of pages in your table. To determine the value for PrimaryPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for PrimaryPages (256000/256=1000).

The value for PrimaryPages must be a positive constant and must be greater than 0.

If your estimate for PrimaryPages is too small, performance may be degraded.

For more information on hash indexes, see CREATE TABLE.

WHERE ExternalSearchCondition

The WHERE clause evaluated by the Oracle database for the cache group table. This WHERE clause is added to every LOAD and REFRESH operation on the cache group. It may not directly reference other tables. It is parsed by both TimesTen and Oracle Database. See Using a WHERE Clause in Oracle TimesTen In-Memory Database Cache Guide.

ParentDistributionClause

In TimesTen Scaleout, distribution clause for a parent table in a static read-only cache group with incremental autorefresh. These distribution schemes are supported for parent tables:

  • DISTRIBUTE BY HASH [(ColumnName [,...])]

  • DUPLICATE

ChildDistributionClause

In TimesTen Scaleout, distribution clause for a child table in a static read-only cache group with incremental autorefresh. These distribution schemes are supported for child tables:

  • DISTRIBUTE BY HASH [(ColumnName [,...])]

  • DISTRIBUTE BY REFERENCE [(ForeignKeyConstraint)]

  • DUPLICATE

AGING LRU [ON | OFF]

Supported in TimesTen Classic only.

If specified, defines the LRU aging policy on the root table. The LRU aging policy applies to all tables in the cache group. The LRU aging policy defines the type of aging (least recently used (LRU)), the aging state (ON or OFF) and the LRU aging attributes.

Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON.

In dynamic cache groups, LRU aging is ON by default. However, you can specify time-based aging or set LRU aging to OFF at the syntax level.

LRU aging cannot be specified on a cache group with the autorefresh attribute, unless the cache group is dynamic.

LRU attributes are defined by calling the ttAgingLRUConfig and/or the ttAgingTableLRUConfig built-in procedures. LRU attributes are not defined at the SQL level. See ttAgingLRUConfig and ttAgingTableLRUConfig in the Oracle TimesTen In-Memory Database Reference and Implementing an Aging Policy in Your Tables in the Oracle TimesTen In-Memory Database Operations Guide for more information.

AGING USE ColumnName...[ON|OFF]

Supported in TimesTen Classic only.

If specified, defines the time-based aging policy on the root table. The time-based aging policy applies to all tables in the cache group. The time-based aging policy defines the type of aging (time-based), the aging state (ON or OFF) and the time-based aging attributes.

Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON.

Time-based aging attributes are defined at the SQL level and are specified by the LIFETIME and CYCLE clauses.

Specify ColumnName as the name of the column used for time-based aging. Define the column as NOT NULL and of data type TIMESTAMP or DATE. The value of this column is subtracted from SYSDATE, truncated using the specified unit (second, minute, hour, day) and then compared to the LIFETIME value. If the result is greater than the LIFETIME value, then the row is a candidate for aging.

The values of the column used for aging are updated by your applications. If the value of this column is unknown for some rows, and you do not want the rows to be aged, define the column with a large default value (the column cannot be NULL).

For more information about time-based aging, see Implementing Aging in a Cache Group for TimesTen Classic in Oracle TimesTen In-Memory Database Cache Guide.

LIFETIME Num1 {SECOND[S]|MINUTE[S]|HOUR[S]DAY[S]}

Supported in TimesTen Classic only.

LIFETIME is a time-based aging attribute and is a required clause.

Specify the LIFETIME clause after the AGING USE ColumnName clause.

The LIFETIME clause specifies the minimum amount of time data is kept in cache.

Specify Num1 as a positive integer constant to indicate the unit of time expressed in seconds, minutes, hours or days that rows should be kept in cache. Rows that exceed the LIFETIME value are aged out (deleted from the table).

The concept of time resolution is supported. If DAYS is specified as the time resolution, then all rows whose timestamp belongs to the same day are aged out at the same time. If HOURS is specified as the time resolution, then all rows with timestamp values within that hour are aged at the same time. A LIFETIME of 3 days is different than a LIFETIME of 72 hours (3*24) or a LIFETIME of 432 minutes (3*24*60).

[CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S]|DAY[S]}]

Supported in TimesTen Classic only.

CYCLE is a time-based aging attribute and is optional. Specify the CYCLE clause after the LIFETIME clause.

The CYCLE clause indicates how often the system should examine rows to see if data exceeds the specified LIFETIME value and should be aged out (deleted).

Specify Num2 as a positive integer constant.

If you do not specify the CYCLE clause, then the default value is 5 minutes. If you specify 0 for Num2, then the aging thread wakes up every second.

If the aging state is OFF, then aging is not done automatically and the CYCLE clause is ignored.

Cache Groups in TimesTen Scaleout

TimesTen Scaleout supports static read-only cache groups with incremental autorefresh. You can specify a distribution scheme on a parent table and on one or more child tables. The distribution scheme specifies how data is distributed across the elements of the database.

A distribution scheme is denoted by the DISTRIBUTE BY clause:
  • For a single table cache group, the default distribution scheme is HASH.
  • If you do not specify a column in the DISTRIBUTE BY clause, the primary key columns are used as the key columns for the distribution scheme.
  • For a multiple table cache group, you can specify either the HASH or the DUPLICATE distribution scheme on the parent table. If you define the DUPLICATE distribution scheme, you can only specify HASH or DUPLICATE on the child tables.
  • For a multiple table cache group, HASH is the default distribution scheme for the parent table and all child tables default to the REFERENCE distribution scheme. If you specify DUPLICATE on the parent table, and do not specify a distribution scheme for the child tables, the default distribution scheme for the child tables is DUPLICATE.
  • If the foreign key on a child table is identical to the primary key on the parent table, the HASH distribution scheme is used for the child table as an optimization.
  • It is best practice to distribute child tables by reference.
The following are not supported:
  • Full autorefresh mode
  • Aging
  • Materialized views
  • Global indexes

Cache Groups in TimesTen Classic

Dynamic Hybrid Read-Only Cache Groups

A dynamic hybrid read-only cache group is a dynamic read-only cache group where the root table does not exist in the Oracle database. The root table is automatically created in the TimesTen database from the cache group definition. The cache group definition includes the description of this root table, as if it existed in the Oracle database.

Description and restrictions:
  • The root table must not exist in the Oracle database.
  • The root table in the TimesTen database must have a primary key.
  • The root table can only contain columns in the primary key. The primary key must be referenced by at least one child table.
  • For dynamic load triggering, you can use a derived table in the FROM clause of the SELECT statement. You can also specify more than one table of the same hybrid cache group in the SELECT query.
  • If you issue a SELECT query on the root table in the TimesTen database, this SELECT operation does not trigger a dynamic load.
  • You cannot specify time-based aging on this type of cache group. LRU aging is enabled by default.
  • The WHERE clause in the cache group definition is not supported.
  • You cannot use the LOAD CACHE GROUP statement to manually load the cache group.
  • The UNLOAD CACHE GROUP ...WITH ID statement is not supported.

See Hybrid Cache Group in the Oracle TimesTen In-Memory Database Cache Guide for more information on dynamic hybrid read-only cache groups.

Description of Cache Groups

  • Two cache groups cannot have the same owner name and group name. If you do not specify the owner name, your schema becomes the owner name for the new cache group.

  • Neither a cache table name nor a cache group name can contain #.

  • Dynamic parameters are not allowed in the WHERE clause.

  • Oracle Database temporary tables cannot be cached.

  • Each table must correspond to a table in the Oracle database.

  • In the Oracle database, you can define a parent/child relationship and then insert a null value into the foreign key column of the child table. This means this row in the child table references a null parent. You can then create a cache group and cache the parent/child relationship of the Oracle database tables. However, if you load data from the Oracle database tables into the cache group, the row that contains the null value of the foreign key column is not loaded. TimesTen recommends that you do not create cache groups if the tables you cache define a parent/child relationship in which the foreign key represents a null parent.

  • You cannot use lowercase delimited identifiers to name your cache tables. Table names in TimesTen are case-insensitive and are stored as uppercase. The name of the cache table must be the same as the Oracle database table name. Uppercase table names on TimesTen will not match mixed case table names on the Oracle database. As a workaround, create a synonym for your table in the Oracle database and use that synonym as the table name for the cache group. This workaround is not available for read-only cache groups or cache groups with the AUTOREFRESH parameter set.

  • Each column in the cache table must match each column in the Oracle database table, both in name and in data type. See Mappings Between Oracle Database and TimesTen Data Types in Oracle TimesTen In-Memory Database Cache Guide. In addition, each column name must be fully qualified with an owner and table name when referenced in a WHERE clause.

  • The WHERE clause can only directly refer to the cache group table. Tables that are not in the cache group can only be referenced with a subquery.

  • Generally, you do not have to fully qualify the column names in the WHERE clause of the CREATE CACHE GROUP, LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP or FLUSH CACHE GROUP statements. However, since TimesTen automatically generates queries that join multiple tables in the same cache group, a column must be fully qualified if there is more than one table in the cache group that contains columns with the same name.

  • By default, a range index is created to enforce the primary key for a cache group table. Use the UNIQUE HASH clause if you want to specify a hash index for the primary key.

    • If your application performs range queries over a cache group table's primary key, then choose a range index for that cache group table by omitting the UNIQUE HASH clause.

    • If, however, your application performs only exact match lookups on the primary key, then a hash index may offer better response time and throughput. In such a case, specify the UNIQUE HASH clause. See CREATE TABLE for more information on the UNIQUE HASH clause.

    • Use ALTER TABLE to change the representation of the primary key index for a table.

  • For cache group tables with the PROPAGATE attribute and for tables of SWT and AWT cache groups, foreign keys specified with ON DELETE CASCADE must be a proper subset of foreign keys with ON DELETE CASCADE in the Oracle database tables.

  • You cannot execute the CREATE CACHE GROUP statement when performed under the serializable isolation level. An error message is returned when attempted.

The AUTOREFRESH parameter automatically propagates changes from the Oracle database to TimesTen cache groups. For static cache groups, deletes, updates and inserts are automatically propagated from the Oracle database to the cache group. For dynamic cache groups, only deletes and updates are propagated. Inserts to the specified Oracle database tables are not propagated to dynamic cache groups. They are dynamically loaded into TimesTen Cache when referenced by the application. They can also be explicitly loaded by the application.

To use autorefresh with a cache group, you must specify AUTOREFRESH when you create the cache group. You can change the MODE, STATE and INTERVAL AUTOREFRESH settings after a cache group has been created by using the ALTER CACHE GROUP statement. Once a cache group has been specified as either AUTOREFRESH or PROPAGATE, you cannot change these attributes. If you are creating a read-only cache group, you do not need to specify the autorefresh clause. A read-only cache group defaults to incremental autorefresh.

TimesTen supports FULL or INCREMENTAL AUTOREFRESH. In FULL mode, the entire cache is periodically unloaded and then reloaded. In INCREMENTAL mode, TimesTen installs triggers in the Oracle database to track changes and periodically updates only the rows that have changed in the specified Oracle database tables. The first incremental refresh is always a full refresh, unless the autorefresh state is PAUSED. The default mode is INCREMENTAL.

FULL AUTOREFRESH is more efficient when most of the Oracle database table rows have been changed. INCREMENTAL AUTOREFRESH is more efficient when there are fewer changes.

TimesTen schedules an autorefresh operation when the transaction that contains a statement with AUTOREFRESH specified is committed. The statement types that cause autorefresh to be scheduled are:

  • A CREATE CACHE GROUP statement in which AUTOREFRESH is specified, and the AUTOREFRESH state is specified as ON.

  • An ALTER CACHE GROUP statement in which the AUTOREFRESH state has been changed to ON.

  • A LOAD CACHE GROUP statement on an empty cache group whose autorefresh state is PAUSED.

The specified interval determines how often autorefresh occurs.

The current STATE of AUTOREFRESH can be ON, OFF or PAUSED. By default, the autorefresh state is PAUSED.

The NOT PROPAGATE attribute cannot be used with the AUTOREFRESH attribute.

Aging in cache groups:

  • You can implement sliding windows with time-based aging. See Configuring a Sliding Window in TimesTen Classic in Oracle TimesTen In-Memory Database Cache Guide.

  • After you have defined an aging policy for the table, you cannot change the policy from LRU to time-based or from time-based to LRU. You must first drop aging and then alter the table to add a new aging policy.

  • The aging policy must be defined to change the aging state.

  • LRU and time-based aging can be combined in one system. If you use only LRU aging, the aging thread wakes up based on the cycle specified for the whole database. If you use only time-based aging, the aging thread wakes up based on an optimal frequency. This frequency is determined by the values specified in the CYCLE clause for the database. If you use both LRU and time-based aging, then the thread wakes up based on a combined consideration of both types.

  • Call the ttAgingScheduleNow procedure to schedule the aging process right away regardless if the aging state is ON or OFF.

  • The following rules determine if a row is accessed or referenced for LRU aging:

    • Any rows used to build the result set of a SELECT statement.

    • Any rows used to build the result set of an INSERT...SELECT statement.

    • Any rows that are about to be updated or deleted.

  • Compiled commands are marked invalid and need recompilation when you either drop LRU aging from or add LRU aging to tables that are referenced in the commands.

  • use

    For LRU aging, if a child row is not a candidate for aging, then neither this child row nor its parent row are deleted. ON DELETE CASCADE settings are ignored.

  • For time-based aging, if a parent row is a candidate for aging, then all child rows are deleted. ON DELETE CASCADE (whether specified or not) is ignored.

  • Specify either the LRU aging or time-based aging policy on the root table. The policy applies to all tables in the cache group.

  • For the time-based aging policy, you cannot add or modify the aging column. This is because you cannot add or modify a NOT NULL column.

  • Restrictions on defining aging for a cache group:

    • LRU aging is not supported on a cache group defined with the autorefresh attribute, unless it is a dynamic cache group.

    • The aging policy cannot be added, altered, or dropped for read-only cache groups or cache groups with the AUTOREFRESH attribute while the cache agent is active. Stop the cache agent first.

    • You cannot drop the column that is used for time-based aging.

Examples: TimesTen Classic

These examples are specific to TimesTen Classic. For information and examples on using cache groups in TimesTen Scaleout, see Using Cache Groups in TimesTen Scaleout in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Create a read-only cache group:

CREATE READONLY CACHE GROUP customerorders
AUTOREFRESH INTERVAL 10 MINUTES
FROM
customer (custid INT NOT NULL,
       name CHAR(100) NOT NULL,
       addr CHAR(100),
       zip INT,
       region CHAR(10),
       PRIMARY KEY(custid)),
ordertab (orderid INT NOT NULL,
       custid INT NOT NULL,
       PRIMARY KEY (orderid),
       FOREIGN KEY (custid) REFERENCES customer(custid));

Create an asynchronous writethrough cache group:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP cstomers
FROM
customer (custid INT NOT NULL,
       name CHAR(100) NOT NULL,
       addr CHAR(100),
       zip INT,
       PRIMARY KEY(custid));

Create a synchronous writethrough cache group:

CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP customers
FROM
customer (custid INT NOT NULL,
       name CHAR(100) NOT NULL,
       addr CHAR(100),
       zip INT,
       PRIMARY KEY(custid));

Create a user managed cache group:

CREATE USERMANAGED CACHE GROUP updateanywherecustomers
AUTOREFRESH
       MODE INCREMENTAL
       INTERVAL 30 SECONDS
       STATE ON
FROM
customer (custid INT NOT NULL,
       name CHAR(100) NOT NULL,
       addr CHAR(100),
       zip INT,
       PRIMARY KEY(custid),
       PROPAGATE);

Create a cache group with time-based aging. Specify agetimestamp as the column for aging. Specify LIFETIME 2 hours, CYCLE 30 minutes. Aging state is not specified, so the default setting (ON) is used. 

CREATE READONLY CACHE GROUP agingcachegroup
AUTOREFRESH
       MODE INCREMENTAL
       INTERVAL 5 MINUTES
       STATE PAUSED
FROM
customer (customerid NUMBER NOT NULL,
       agetimestamp TIMESTAMP NOT NULL,
       PRIMARY KEY (customerid))
       AGING USE agetimestamp LIFETIME 2 HOURS CYCLE 30 MINUTES;

Command> DESCRIBE customer;
Table USER.CUSTOMER:
  Columns:
   *CUSTOMERID                      NUMBER NOT NULL
    AGETIMESTAMP                    TIMESTAMP (6) NOT NULL
  AGING USE AgeTimestamp LIFETIME 2 HOURS CYCLE 30 MINUTES ON
1 table found.
(primary key columns are indicated with *)

Use a synonym for a mixed case delimited identifier table name in the Oracle database so the mixed case table name can be cached in TimesTen. First attempt to cache the mixed case Oracle database table name. You see the error "Could not find 'NameofTable' in Oracle": 

Command> AUTOCOMMIT 0;
Command> PASSTHROUGH 3;
Command> CREATE TABLE "MixedCase" (col1 NUMBER PRIMARY KEY NOT NULL);
Command> INSERT INTO "MixedCase" VALUES (1);
1 row inserted.
Command> COMMIT;
Command> CREATE CACHE GROUP MixedCase1 from "MixedCase" 
        (col1 NUMBER PRIMARY KEY NOT NULL);
 5140: Could not find SAMPLEUSER.MIXEDCASE in Oracle.  May not have privileges.
The command failed.

Now, using the PassThrough attribute, create the synonym "MIXEDCASE" in the Oracle database and use that synonym as the table name. 

Command> AUTOCOMMIT 0;
Command> PASSTHROUGH 3;
Command> CREATE SYNONYM "MIXEDCASE" FOR "MixedCase";
Command> COMMIT;
Command> CREATE CACHE GROUP MixedCase2 FROM "MIXEDCASE" 
         (col1 NUMBER PRIMARY KEY NOT NULL);
Warning  5147: Cache group contains synonyms
Command> COMMIT;

Attempt to use a synonym name with a read-only cache group or a cache group with the AUTOREFRESH attribute. You see an error: 

Command> AUTOCOMMIT 0;
Command> PASSTHROUGH 3;
Command> CREATE SYNONYM "MIXEDCASE_AUTO" FOR "MixedCase";
Command> COMMIT;
Command> CREATE READONLY CACHE GROUP MixedCase3 AUTOREFRESH MODE
         INCREMENTAL INTERVAL 10 MINUTES FROM "MIXEDCASE_AUTO" 
         (Col1 NUMBER PRIMARY KEY NOT NULL);
 5142: Autorefresh is not allowed on cache groups with Oracle synonyms
The command failed.

See Also