FLUSH CACHE GROUP

The FLUSH CACHE GROUP statement flushes data from TimesTen cache tables to Oracle tables. This statement is available only for user managed cache groups. For a description of cache group types, see "User managed and system 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) is turned off. Instead of propagating every transaction upon commit, many transactions can be committed before changes are propagated to Oracle. 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 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 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 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 Oracle tables.

Required privileges

No privilege is required for the cache group owner.

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

SQL syntax

FLUSH CACHE GROUP [Owner.]GroupName
[WHERE ConditionalExpression];

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

Parameters

The FLUSH CACHE GROUP statement has the parameters:

Parameter	Description
`[Owner.]GroupName`	Name of the cache group to be flushed.
`ConditionalExpression`	A search condition to qualify the target rows of the operation.
`WITH ID` `ColumnValueList`	The WITH ID clauses allows 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 instances, rather than to a single instance or to all instances. The flush operation uses the WHERE clause to determine which instances to send to the Oracle database.
All table names used in cache group WHERE clauses should be fully qualified with an owner name to allow other users to execute the same WHERE clauses against the same cache group. Without an owner name, all tables referenced by cache group WHERE clauses are assumed to be 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 Oracle tables. When the WHERE clause is included, it is allowed to include only the root table.
If propagates to Oracle tables are turned off (such as when the ttCachePropagateFlagSet built-in procedure has been called with an argument of zero in the current transaction) then all tables, with the exception of read-only tables, can be flushed to Oracle tables. Otherwise, only tables which are not marked as READ ONLY or PROPAGATE can be flushed to Oracle tables.
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 on AWT or SWT cache groups, user managed cache groups with the propagate attribute, or autorefreshed and propagated user managed cache groups unless the cache group is a dynamic cache group.
Do not use the WITH ID clause with the COMMIT EVERY n ROWS clause.

Examples

FLUSH CACHE GROUP marketbasket;

FLUSH CACHE GROUP marketbasket
WITH ID(10);