REFRESH CACHE GROUP

The REFRESH CACHE GROUP statement is equivalent to an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement.

Required privilege

CREATE SESSION on the Oracle schema and SELECT on the Oracle tables.
No privilege for the cache group is required for the cache group owner.
REFRESH CACHE GROUP or REFRESH ANY CACHE GROUP for another user's cache group

SQL syntax

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

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

Parameters

The REFRESH CACHE GROUP has the parameters:

Parameter	Description
`[Owner.]GroupName`	Name assigned to the cache group.
`ConditionalExpression`	A search condition to qualify the target rows of the operation.
`n`	The number of rows to insert into the cache group before committing the work. The value must be a nonnegative integer. If the value is 0, the entire 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 Oracle and (`NumThreads` - 1) performs 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 assumes the value 10.
`WITH ID` `ColumnValueList`	The WITH ID clauses allows 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.

Description

A REFRESH CACHE GROUP statement must be executed in its own transaction.
REFRESH CACHE GROUP replaces all or specified cache instances in the TimesTen cache tables with the most current data from the corresponding Oracle tables even if an instance is already present in the cache tables. For explicitly loaded cache groups, a refresh operation is equivalent to an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement. Operations on all rows in the Oracle 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 Oracle tables into the cache tables. For more information on explicitly loaded and dynamic cache groups, see Oracle In-Memory Database Cache User's Guide.
When refreshing a read-only cache group:
- The AUTOREFRESH statement must be paused, and
- The REFRESH statement cannot have a WHERE clause unless the cache group is a dynamic cache group.
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.
Table names in subqueries in the WHERE clause of the REFRESH CACHE GROUP statement must be fully qualified.
If the REFRESH CACHE GROUP statement fails when you specify COMMIT EVERY n ROWS (where n is greater than 0), the content of the target cache group could be in an inconsistent state. 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
- If you want to roll back the refresh transaction upon failure

Restrictions

Do not specify the PARALLEL clause:
- With the WITH ID clause
- With the COMMIT EVERY 0 ROWS clause
- When data store level locking is enabled (connection attribute LockLevel is set to 1)
Do not use the WITH ID clause when refreshing these types of cache groups:
- Explicitly loaded read-only cache groups
- Explicitly loaded 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.

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 (where n is greater than 0) 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.