LOAD CACHE GROUP

The LOAD CACHE GROUP statement loads data from an Oracle table into a TimesTen cache group. The load operation is local. It is not propagated across cache grid members.

Required privilege

No privilege is required for the cache group owner.

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

SQL syntax

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

or

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

Parameters

The LOAD CACHE GROUP has the parameters:

Description

• Loads all new instances from Oracle that satisfy the cache group definition and are not yet present in the cache group.

• LOAD CACHE GROUP is executed in its own transaction, and must be the first operation in a transaction.

• For an explicitly loaded cache group, LOAD CACHE GROUP does not update cache instances that are already present in the TimesTen cache tables. Therefore, LOAD CACHE GROUP loads only inserts on Oracle tables into the corresponding TimesTen cache tables.

• For a dynamic cache group, LOAD CACHE GROUP loads rows that have been inserted, updated and deleted on Oracle tables into the cache tables. For more information about explicitly loaded and dynamic cache groups, see Oracle In-Memory Database Cache User's Guide.

• The transaction size is the number of rows inserted before committing the work. The value of n in COMMIT EVERY n ROWS must be nonnegative and is rounded up to the nearest multiple of 256 for performance reasons.

• Errors cause a rollback. When rows are committed periodically, errors abort the remainder of the load. The load is rolled back to the last commit.

• If the LOAD statement fails when you specify the 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 statement to unload the cache group, then load again.

• Table names in subqueries in the WHERE clause of the LOAD CACHE GROUP statement must be fully qualified.

• When loading a read-only cache group:

  • The AUTOREFRESH state must be paused, and

  • The LOAD CACHE GROUP statement cannot have a WHERE clause (except on a dynamic cache group), and

  • The cache group must be empty.

• If the automatic refresh state of a cache group (explicitly loaded or dynamic) is PAUSED, the state is changed to ON after a LOAD CACHE GROUP statement issued on the cache group completes.If the automatic refresh state of a dynamic cache group is PAUSED and the cache tables are populated, the state remains PAUSED after a LOAD CACHE GROUP statement issued on the cache group completes.

• Following the execution of a LOAD 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 loaded.

• Use the WITH ID clause:

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

  • To specify binding parameters.

  • If you want to roll back the load 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 loading these types of cache groups:

  • Explicitly loaded read-only cache groups

  • Explicitly loaded user managed with the autorefresh attribute

  • User managed with the autorefresh and propagate attributes

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

• The WITH ID clause cannot be used to acquire a cache instance from another cache grid member.

Examples

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);

LOAD CACHE GROUP recreation.cache
    COMMIT EVERY 30 ROWS;

Use the HR schema to illustrate the use of the PARALLEL clause with the LOAD 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. Unload cache group cg2, then specify the LOAD CACHE GROUP statement with the PARALLEL clause to provide parallel loading. You see 25 cache instances loaded.

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> UNLOAD CACHE GROUP cg2;
25 cache instances affected.
Command> COMMIT;
Command> LOAD CACHE GROUP cg2 COMMIT EVERY 10 ROWS PARALLEL 2;
25 cache instances affected.
Command> COMMIT;

The following example loads only the cache instances for customers whose customer number is greater than or equal to 5000 into the TimesTen cache tables in the new_customers cache group from the corresponding Oracle tables:

LOAD CACHE GROUP new_customers WHERE (oratt.customer.cust_num >= 5000) COMMIT EVERY 256 ROWS;

See also

REFRESH CACHE GROUP
UNLOAD CACHE GROUP