FLUSH CACHE GROUP

This statement is not supported in TimesTen Scaleout.

In TimesTen Classic:

The FLUSH CACHE GROUP statement flushes data from TimesTen cache tables to Oracle Database tables. This statement is available only for user managed cache groups.

There are two variants to this operation: one that accepts a WHERE clause, and one that accepts a WITH ID clause.

FLUSH CACHE GROUP is meant to be used when commit propagation (from TimesTen to Oracle Database) is turned off. Instead of propagating every transaction upon commit, many transactions can be committed before changes are propagated to Oracle Database. For each cache instance ID, if the cache instance exists in the Oracle database, the operation in the Oracle database consists of an update. If the cache instance does not exist in the Oracle database, TimesTen inserts it.

This is useful, for example, in a shopping cart application in which many changes may be made to the cart, which uses TimesTen as a high-speed cache, before the order is committed to the master Oracle database table.

Note:

Using a WITH ID clause usually results in better system performance than using a WHERE clause.

Only inserts and updates are flushed. Inserts are propagated as inserts if the record does not exist in the Oracle database table or as updates (if the record already exists). It is not possible to flush a delete. That is, if a record is deleted on TimesTen, there is no way to "flush" that delete to the Oracle database table. Deletes must be propagated either manually or by turning commit propagation on. Attempts to flush deleted records are silently ignored. No error or warning is issued. Records from tables that are specified as READ ONLY or PROPAGATE cannot be flushed to the Oracle database tables.

Required privilege

No privilege is required for the cache group owner.

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

INSERT, DELETE, UPDATE privileges on underlying tables.

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

FLUSH CACHE GROUP [Owner.]Cache
[WHERE ConditionalExpression]

or

FLUSH CACHE GROUP [Owner.]CacheGroupName
WITH ID (ColumnValueList)

Parameters

Parameter Description

[Owner.]CacheGroupName

Name of the cache group to be flushed.

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.

WITH ID ColumnValueList

The WITH ID clauses enables you to use primary key values to flush the cache instance. Specify ColumnValueList as either a list of literals or binding parameters to represent the primary key values.

Description

  • WHERE clauses are generally used to apply the operation to a set of cache instances, rather than to a single cache instance or to all cache instances. The flush operation uses the WHERE clause to determine which cache instances to send to the Oracle database.

  • Generally, you do not have to fully qualify the column names in the WHERE clause of the FLUSH 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. Without an owner name, all tables referenced by cache group WHERE clauses are owned by the current login name executing the cache group operation.

  • When the WHERE clause is omitted, the entire contents of the cache group is flushed to the Oracle database tables. When the WHERE clause is included, it is allowed to include only the root table.

  • Following the execution of a FLUSH 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 flushed.

  • Use the WITH ID clause to specify binding parameters.

Restrictions

Do not use the WITH ID clause when flushing:

  • Static user managed cache group with the AUTOREFRESH attribute

  • AWT or SWT cache groups

Examples

FLUSH CACHE GROUP marketbasket;

FLUSH CACHE GROUP marketbasket
WITH ID(10);

See also