CREATE CACHE GROUP

The CREATE CACHE GROUP statement:

• Creates the table defined by the cache group

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

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

If a data store has more than one cache group, the cache groups must correspond to different Oracle (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.

User managed and system managed cache groups

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:

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

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

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

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 must be able to parse all WHERE clauses.

Explicitly loaded cache groups and dynamic cache groups

Cache groups can be explicitly loaded or dynamic.

In cache groups that are explicitly loaded, new cache instances are loaded manually into the TimesTen cache tables from the Oracle 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. 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 inserting or loading of new cache instances.

Any cache group type (READONLY, ASYNCHRONOUS WRITETHROUGH, SYNCHRONOUS WRITETHROUGH, USERMANAGED) 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 built-in procedure 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 explicitly loaded and dynamic cache groups, see Oracle In-Memory Database Cache User's Guide. For more information about the dynamic load operation, see "Dynamically loading a cache group" in Oracle In-Memory Database Cache User's Guide.

Local and global cache groups

You can create either local or global cache groups.

In a local cache group, data in the cache tables are not shared across TimesTen databases even if the databases are members of the same cache grid. Therefore, the databases can have overlapping data or the same data. Any cache group type can be defined as a local cache group. A local cache group can be either dynamic or explicitly loaded.

In a global cache group, data in the cache tables are shared among TimesTen databases within a cache grid. Updates to the same data by different grid members are coordinated by the grid. Only a dynamic AWT cache group can be defined as a global cache group.

For more information on local and global cache groups, see Oracle In-Memory Database Cache User's Guide. In particular, see "Example of data sharing among the grid members" in Oracle In-Memory Database Cache User's 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)

SQL syntax

There are CREATE CACHE GROUP statements for each type of cache group:

• CREATE READONLY CACHE GROUP

• CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP

• CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP

• CREATE USERMANAGED CACHE GROUP

There is one CREATE CACHE GROUP statement to create a global cache group:

• CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP

CREATE READONLY CACHE GROUP

For READONLY cache groups, the syntax is:

CREATE [DYNAMIC] READONLY CACHE GROUP [Owner.]GroupName
 [AUTOREFRESH
  [MODE {INCREMENTAL | FULL}]
  [INTERVAL IntervalValue {MINUTE[S] | SECOND[S] | MILLESECOND[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]
 [WHERE ExternalSearchCondition]
 [AGING USE ColumnName
        LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] | DAY[S]}
        [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
 [ON|OFF]
 ]
} [,...];

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP

For ASYNCHRONOUS WRITETHROUGH cache groups, the syntax is:

CREATE [DYNAMIC] [ASYNCHRONOUS] WRITETHROUGH CACHE GROUP   [Owner.]GroupName
  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]
 ]
} [,...];

CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP

For SYNCHRONOUS WRITETHROUGH cache groups, the syntax is:

CREATE [DYNAMIC] SYNCHRONOUS WRITETHROUGH
CACHE GROUP [Owner.]GroupName
 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]
 ]
} [,...];

CREATE USERMANAGED CACHE GROUP

For user managed cache groups, the syntax is:

CREATE [DYNAMIC][USERMANAGED] CACHE GROUP [Owner.]GroupName
 [AUTOREFRESH
   [MODE {INCREMENTAL | FULL}]
   [INTERVAL IntervalValue {MINUTE[S] | SECOND[S] | MILLESECOND[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]
 [WHERE ExternalSearchCondition]
 [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]
 ]
} [,...];

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP

To create a global dynamic cache group to cache data within a cache grid:

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP  [Owner.]GroupName
  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]
 ]
} [,...];

Parameters

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

Everything after the FROM keyword comprises the definitions of the Oracle 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 parameters:

Description

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

• Dynamic parameters are not allowed in the WHERE clause.

• Oracle temporary tables cannot be cached.

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

• 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 table name. Uppercase table names on TimesTen will not match mixed case table names on Oracle. As a workaround, create a synonym for your table in Oracle 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 attribute.

• Each column in the cache table must match each column in the Oracle table, both in name and in data type. See "Mappings between Oracle and TimesTen data types" in Oracle In-Memory Database Cache User's 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 sub-select.

• Generally, you do not have to fully qualify the column names in the WHERE clause of the CREATE CACHE GROUP, LOAD, UNLOAD, REFRESH or FLUSH statements. However, since TimesTen automatically generates queries that join multiple tables in the same cache group, a column needs to 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 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 tables.

AUTOREFRESH in cache groups

The AUTOREFRESH parameter automatically propagates changes from the Oracle database to TimesTen cache groups. For explicitly loaded 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 tables are not propagated to dynamic cache groups. They are dynamically loaded into IMDB 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 command. Once a cache group has been specified as either AUTOREFRESH or PROPAGATE, you cannot change these attributes.

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 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 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 Oracle In-Memory Database Cache User's 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 data store. 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 all tables. 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.

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

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

Cache grid

To cache data in a cache grid, you must create a dynamic asynchronous writethrough global cache group. Before you can create this cache group, the TimesTen database must be associated with a cache grid. For more information on creating and using a cache grid and creating and using global cache groups, see "Cache grid" and "Global cache group" in Oracle In-Memory Database Cache User's Guide.

Examples

Create a READONLY 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 ASYNCHROUS 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 USERMANAGED 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 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

ALTER CACHE GROUP
ALTER TABLE
DROP CACHE GROUP
FLUSH CACHE GROUP
LOAD CACHE GROUP
UNLOAD CACHE GROUP