UNLOAD CACHE GROUP

The UNLOAD CACHE GROUP statement removes data from the cache group.

Required privilege

No privilege is required for the cache group owner.

UNLOAD or UNLOAD 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

UNLOAD CACHE GROUP [Owner.]CacheGroupName 
  [WHERE ConditionalExpression]
  [COMMIT EVERY n ROWS]

or

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

Note:

The WITH ID clause is not supported in TimesTen Scaleout.

Parameters

Parameter Description

[Owner.]CacheGroupName

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 unloaded) at which a commit is issued during the unload operation.

ROWS refers to the number of rows that are deleted from the cache group. For example, if your cache group has one cache instance and the cache instance consists of 1 parent row and 10 child rows, and you issue COMMIT EVERY 2 ROWS, TimesTen issues one commit after the entire cache instance is deleted. TimesTen does not commit in the middle of deleting an cache instance. So once the unload operation reaches its threshold (2 rows in this case), TimesTen issues a commit after all rows are deleted for that cache instance.

If you specify this clause, the cache agent must be running and the unload must be the only operation in the transaction.

Express n as an integer where (n >= 0). If you specify 0 for n, the UNLOAD CACHE GROUP statement is executed as one transaction and the cache agent does the delete.

To improve performance, use this clause when you are performing operations on cache groups that affect large amounts of data.

Do not use this clause when you have cache groups with a small amount of data.

WITH ID ColumnValueList

The WITH ID clauses enables you to use primary key values to unload 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

  • The UNLOAD CACHE GROUP statement deletes rows from the TimesTen cache tables without affecting the data in the Oracle database tables.

  • If your table has out of line columns and there are millions of rows, consider calling the ttCompact built-in procedure to free memory.

  • If the cache group is replicated, an UNLOAD CACHE GROUP statement deletes the entire contents of any replicated cache group as well.

  • Execution of the UNLOAD CACHE GROUP statement for an AWT cache group waits until updates on the rows have been propagated to the Oracle database.

  • The UNLOAD CACHE GROUP statement can be used for any type of cache group. See "CREATE CACHE GROUP" for information on cache groups.

  • Use the UNLOAD CACHE GROUP statement carefully with cache groups that have the AUTOREFRESH attribute. A row that is unloaded can reappear in the cache group as the result of an autorefresh operation if the row or its child rows are updated in the Oracle database.

  • Following the execution of an UNLOAD 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 unloaded.

  • If you specify the COMMIT EVERY n ROWS clause, the cache agent performs the unload operation and commits the transaction after unloading the data. Make sure the cache agent is up and running. If you do not specify the COMMIT EVERY n ROWS clause, the unload operation is executed by the application.

  • If you specify the COMMIT EVERY n ROWS clause, you cannot rollback the unload operation. If the unload operation fails when you specify the COMMIT EVERY n ROWS clause (where n >= 0), the cache group could be in an inconsistent state since some unloaded rows are already committed. Therefore, some cache instances may be partially unloaded. If this occurs, unload the cache group again.

  • Use the WITH ID clause to specify binding parameters.

  • The UNLOAD CACHE GROUP operation is executed in its own transaction.

Restrictions

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

  • Do not reference child tables in the WHERE clause.

  • Do not user the WITH ID clause on static read-only cache groups, or static user-managed cache groups with the autorefresh attribute.

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

Examples

Use the UNLOAD CACHE GROUP... COMMIT EVERY n ROWS to unload data from cached tables. The cache agent unloads the data because the COMMIT EVERY n ROWS clause is used. 

Command> UNLOAD CACHE GROUP testcache WHERE sampleuser.orders.order_id > 100
         COMMIT EVERY 100 ROWS;
2 cache instances affected.

CREATE and UNLOAD a cache group. The application performs the unload operation because the COMMIT EVERY n ROWS clause is not used. 

CREATE CACHE GROUP recreation.cache
    FROM recreation.clubs (
        clubname CHAR(15) NOT NULL,
        clubphone SMALLINT,
        activity CHAR(18),
        PRIMARY KEY(clubname))
    WHERE (recreation.clubs.activity IS NOT NULL);
UNLOAD CACHE GROUP recreation.cache;

See also