REFRESH CACHE GROUP

The REFRESH CACHE GROUP statement replaces data in the TimesTen cache tables with the most current committed data from the Oracle database cached tables.

Required Privilege

CREATE SESSION on the Oracle Database schema and SELECT on the Oracle Database tables.

No privilege for the cache group is required for the cache group owner.

REFRESH or REFRESH ANY CACHE GROUP for another user's cache group.

INSERT, DELETE, UPDATE privileges on underlying tables.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

REFRESH CACHE GROUP [Owner.]GroupName 
[WHERE ConditionalExpression]
COMMIT EVERY n ROWS
[PARALLEL NumThreads]

or

REFRESH CACHE GROUP [Owner.]GroupName 
WITH ID (ColumnValueList)

Note:

The WITH ID clause is not supported in TimesTen Scaleout.

Parameters

Parameter	Description
[Owner.]GroupName	Name assigned to the cache group.
WHERE ConditionalExpression	Use the WHERE clause to specify a search condition to qualify the target rows of the cache operation. If you use more than one table in the WHERE clause and the tables have columns with the same names, fully qualify the table names.
COMMIT EVERY n ROWS	Use the COMMIT EVERY n ROWS clause to indicate the frequency (based on the number of rows that are refreshed in the cache group) at which a commit is issued during the refresh operation. This clause is required if you do not specify the WITH ID clause. n represents the number of rows that are refreshed before the transaction is committed. Express n as an integer (where n >= 0). If you specify 0 for n, the REFRESH CACHE GROUP statement is executed as one transaction.
[PARALLEL NumThreads]	Provides parallel loading for cache group tables. Specifies the number of loading threads to run concurrently. One thread performs the bulk fetch from the Oracle database and the other threads (NumThreads -1 threads) perform the inserts into TimesTen. Each thread uses its own connection or transaction. The minimum value for NumThreads is 2. The maximum value is 10. If you specify a value greater than 10, TimesTen assigns the value 10.
WITH ID ColumnValueList	The WITH ID clauses enables you to use primary key values to refresh the cache instance. Specify ColumnValueList as either a list of literals or binding parameters to represent the primary key values. The WITH ID clause is not supported in TimesTen Scaleout.

Description

• A REFRESH CACHE GROUP statement must be executed in its own transaction.

• Before issuing the REFRESH CACHE GROUP statement, ensure that the replication agent is running if the cache group is replicated or is an AWT cache group. Make sure the cache agent is running.

• The REFRESH CACHE GROUP statement replaces data in the TimesTen cached tables with the most current committed data from the cached Oracle database tables, including data that already exists in the TimesTen cached tables. For an explicitly loaded cache group, a refresh operation is equivalent to issuing an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement. Operations on all rows in the Oracle database tables including inserts, updates, and deletes are applied to the cache tables. For dynamic cache groups, a refresh operation refreshes only rows that are updated or deleted on the Oracle database tables into the cache tables. For more information on explicitly loaded and dynamic cache groups, see Transmitting Changes Between the TimesTen and Oracle Databases in Oracle TimesTen In-Memory Database Cache Guide.

• When refreshing a read-only cache group:

  • The AUTOREFRESH state must be paused.

  • If the cache group is a read-only dynamic cache group, do not use the PARALLEL clause.

• If the automatic refresh state of a cache group (dynamic or explicitly loaded) is PAUSED, the state is changed to ON after an unconditional REFRESH CACHE GROUP statement issued on the cache group completes.

• If the automatic refresh state of a dynamic cache group is PAUSED, the state remains PAUSED after a REFRESH CACHE GROUP...WITH ID statement completes.

• Generally, you do not have to fully qualify the column names in the WHERE clause of the REFRESH CACHE GROUP statement. 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.

• If the REFRESH CACHE GROUP statement fails when you specify COMMIT EVERY n ROWS (where n >= 0), the content of the target cache group could be in an inconsistent state since some loaded rows are already committed. Some cache instances may be partially loaded. Use the UNLOAD CACHE GROUP statement to unload the cache group, then use the LOAD CACHE GROUP statement to reload the cache group.

• Following the execution of a REFRESH CACHE GROUP statement, the ODBC function SQLRowCount(), the JDBC method getUpdateCount(), and the OCI function OCIAttrGet() with the OCI_ATTR_ROW_COUNT argument return the number of cache instances that were refreshed.

• Use the WITH ID clause:

  • In place of the WHERE clause for faster refreshing of the cache instance

  • To specify binding parameters

  • To roll back the refresh transaction upon failure

Restrictions

• The REFRESH CACHE GROUP...WITH ID clause is not supported in TimesTen Scaleout.

• Do not specify the PARALLEL clause:

  • With the WITH ID clause

  • With the COMMIT EVERY n ROWS clause

  • When database level locking is enabled (connection attribute LockLevel is set to 1)

  • For read-only dynamic cache groups

• Do not use the WITH ID clause when refreshing these types of cache groups:

  • Static read-only cache groups

  • Static user managed cache groups with the autorefresh attribute

  • User managed cache groups with the autorefresh and propagate attributes

• Do not use the WITH ID clause with the COMMIT EVERY n ROWS clause.

• Do not use the WHERE clause with dynamic or read-only cache groups.

Examples

REFRESH CACHE GROUP recreation.cache COMMIT EVERY 30 ROWS;

Is equivalent to:

UNLOAD CACHE GROUP recreation.cache;
LOAD CACHE GROUP recreation.cache COMMIT EVERY 30 ROWS;

Use the HR schema to illustrate the use of the PARALLEL clause with the REFRESH CACHE GROUP statement. The COMMIT EVERY n ROWS is required. Issue the CACHEGROUPS command. You see cache group cg2 is defined and the autorefresh state is paused. Specify the REFRESH CACHE GROUP statement with the PARALLEL clause to provide parallel loading. You see 25 cache instances refreshed.

Command> CACHEGROUPS;

Cache Group SAMPLEUSER.CG2:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 1.5 Minutes

  Root Table: SAMPLEUSER.COUNTRIES
  Table Type: Read Only

  Child Table: SAMPLEUSER.LOCATIONS
  Table Type: Read Only

  Child Table: SAMPLEUSER.DEPARTMENTS
  Table Type: Read Only

1 cache group found.
Command> REFRESH CACHE GROUP cg2 COMMIT EVERY 20 ROWS PARALLEL 2;
25 cache instances affected.

See also

• ALTER CACHE GROUP
• CREATE CACHE GROUP
• DROP CACHE GROUP
• FLUSH CACHE GROUP
• LOAD CACHE GROUP
• UNLOAD CACHE GROUP