5 Cache Group Operations

The following sections describe operations that can be performed on cache groups:

Note:

You can use SQL statements or SQL Developer to perform most of the operations in this chapter. For more information about SQL Developer, see Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.

Transmitting updates between the TimesTen and Oracle databases

You can use the following SQL statements to manually transmit committed updates between the TimesTen cache tables and the cached Oracle Database tables:

SQL statement Description
LOAD CACHE GROUP Load cache instances that are not in the TimesTen cache tables from the cached Oracle Database tables.
REFRESH CACHE GROUP Replace cache instances in the TimesTen cache tables with current data from the cached Oracle Database tables.
FLUSH CACHE GROUP Propagate committed updates on the TimesTen cache tables to the cached Oracle Database tables. Only applicable for user managed cache groups.

For AWT, SWT, and user managed cache groups that use the PROPAGATE cache table attribute, committed updates on the TimesTen cache tables are automatically propagated to the cached Oracle Database tables.

See "Asynchronous writethrough (AWT) cache group" for more information about AWT cache groups.

See "Synchronous writethrough (SWT) cache group" for more information about SWT cache groups.

See "PROPAGATE cache table attribute" for more information about using the PROPAGATE cache table attribute on cache tables in a user managed cache group.

The AUTOREFRESH cache group attribute can be used in a read-only or a user managed cache group to automatically refresh committed updates on cached Oracle Database tables into the TimesTen cache tables. Automatic refresh can be defined on explicitly loaded or dynamic cache groups.

See "AUTOREFRESH cache group attribute" for more information about automatically refreshing a cache group.

Data is manually preloaded into the cache tables of explicitly loaded cache groups. For dynamic cache groups, data is loaded on demand into the cache tables. A cache instance is automatically loaded from the cached Oracle Database tables when a particular statement does not find the data in the cache tables.

See "Dynamically loading a cache instance" for more information about a dynamic load operation.

Dynamic cache groups are typically configured to automatically age out from the cache tables data that is no longer being used.

Loading and refreshing a cache group

You can manually insert or update cache instances in the TimesTen cache tables from the cached Oracle Database tables using either a LOAD CACHE GROUP or REFRESH CACHE GROUP statement. The differences between loading and refreshing a cache group are:

  • LOAD CACHE GROUP only loads committed inserts on the cached Oracle Database tables into the TimesTen cache tables. New cache instances are loaded into the cache tables, but cache instances that already exist in the cache tables are not updated or deleted even if the corresponding rows in the cached Oracle Database tables have been updated or deleted. A load operation is primarily used to initially populate a cache group.

  • REFRESH CACHE GROUP replaces cache instances in the TimesTen cache tables with the most current data from the cached Oracle Database tables including cache instances that are already exist in the cache tables. A refresh operation is primarily used to update the contents of a cache group with committed updates on the cached Oracle Database tables after the cache group has been initially populated.

    For an explicitly loaded cache group, a refresh operation is equivalent to issuing an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement on the cache group. In effect, all committed inserts, updates and deletes on the cached Oracle Database tables are refreshed into the cache tables. New cache instances may be loaded into the cache tables. Cache instances that already exist in the cache tables are updated or deleted if the corresponding rows in the cached Oracle Database tables have been updated or deleted. See "Unloading a cache group" for more information about the UNLOAD CACHE GROUP statement.

    For a dynamic cache group, a refresh operation only refreshes committed updates and deletes on the cached Oracle Database tables into the cache tables because only existing cache instances in the cache tables are refreshed. New cache instances are not loaded into the cache tables so after the refresh operation completes, the cache tables contain either the same or fewer number of cache instances. To load new cache instances into the cache tables of a dynamic cache group, use a LOAD CACHE GROUP statement or perform a dynamic load operation. See "Dynamically loading a cache instance" for more information about a dynamic load operation.

For most cache group types, you can use a WHERE clause in a LOAD CACHE GROUP or REFRESH CACHE GROUP statement to restrict the rows to be loaded or refreshed into the cache tables.

If the cache table definitions use a WHERE clause, only rows that satisfy the WHERE clause are loaded or refreshed into the cache tables even if the LOAD CACHE GROUP or REFRESH CACHE GROUP statement does not use a WHERE clause.

A REFRESH CACHE GROUP statement can be issued on a global cache group only if it contains a WITH ID clause.

If the cache group has a time-based aging policy defined, only cache instances where the timestamp in the root table's row is within the aging policy's lifetime are loaded or refreshed into the cache tables.

To prevent a load or refresh operation from processing a large number of cache instances within a single transaction, which can greatly reduce concurrency and throughput, use the COMMIT EVERY n ROWS clause to specify a commit frequency unless you are using the WITH ID clause. If you specify COMMIT EVERY 0 ROWS, the load or refresh operation is processed in a single transaction.

A LOAD CACHE GROUP or REFRESH CACHE GROUP statement that uses the COMMIT EVERY n ROWS clause must be performed in its own transaction without any other operations within the same transaction.

Example 5-1 Loading a cache group

The following statement loads new cache instances into the TimesTen cache tables in the customer_orders cache group from the cached Oracle Database tables:

LOAD CACHE GROUP customer_orders COMMIT EVERY 256 ROWS;

Example 5-2 Loading a cache group using a WHERE clause

The following statement loads into the TimesTen cache tables in the new_customers cache group from the cached Oracle Database tables, new cache instances for customers whose customer number is greater than or equal to 5000:

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

Example 5-3 Refreshing a cache group

The following statement refreshes cache instances in the TimesTen cache tables within the top_products cache group from the cached Oracle Database tables:

REFRESH CACHE GROUP top_products COMMIT EVERY 256 ROWS;

Example 5-4 Refreshing a cache group using a WHERE clause

The following statement refreshes in the TimesTen cache tables within the update_anywhere_customers cache group from the cached Oracle Database tables, cache instances of customers located in zip code 60610:

REFRESH CACHE GROUP update_anywhere_customers
  WHERE (oratt.customer.zip = '60610') COMMIT EVERY 256 ROWS;

For more information, see the "LOAD CACHE GROUP" and "REFRESH CACHE GROUP" statements in Oracle TimesTen In-Memory Database SQL Reference.

The rest of this section includes these topics:

Loading and refreshing an explicitly loaded cache group with autorefresh

If the autorefresh state of an explicitly loaded cache group is PAUSED, the autorefresh state is changed to ON after a LOAD CACHE GROUP or REFRESH CACHE GROUP statement issued on the cache group completes.

The following restrictions apply when manually loading or refreshing an explicitly loaded cache group with autorefresh:

  • A LOAD CACHE GROUP statement can only be issued if the cache tables are empty.

  • The autorefresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement.

  • The autorefresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement.

  • A LOAD CACHE GROUP statement cannot contain a WHERE clause.

  • A LOAD CACHE GROUP or REFRESH CACHE GROUP statement cannot contain a WITH ID clause.

  • A REFRESH CACHE GROUP statement cannot contain a WHERE clause.

  • All tables and columns referenced in a WHERE clause when loading the cache group must be fully qualified. For example:

    user_name.table_name and user_name.table_name.column_name

When an autorefresh operation occurs on an explicitly loaded cache group, all committed inserts, updates and deletes on the cached Oracle Database tables since the last autorefresh cycle are refreshed into the cache tables. New cache instances may be loaded into the cache tables. Cache instances that already exist in the cache tables are updated or deleted if the corresponding rows in the cached Oracle Database tables have been updated or deleted.

Loading and refreshing a dynamic cache group with autorefresh

If the autorefresh state of a dynamic cache group is PAUSED, the autorefresh state is changed to ON after any of the following events occur:

  • Its cache tables are initially empty, and then a dynamic load, a LOAD CACHE GROUP or an unconditional REFRESH CACHE GROUP statement issued on the cache group completes.

  • Its cache tables are not empty, and then an unconditional REFRESH CACHE GROUP statement issued on the cache group completes.

If the autorefresh state of a dynamic cache group is PAUSED, the autorefresh state remains at PAUSED after any of the following events occur:

  • Its cache tables are initially empty, and then a REFRESH CACHE GROUP ... WITH ID statement issued on the cache group completes.

  • Its cache tables are not empty, and then a dynamic load, a REFRESH CACHE GROUP ... WITH ID, or a LOAD CACHE GROUP statement issued on the cache group completes.

For a dynamic cache group, an autorefresh operation only refreshes committed updates and deletes on the cached Oracle Database tables since the last autorefresh cycle into the cache tables because only existing cache instances in the cache tables are refreshed. New cache instances are not loaded into the cache tables. To load new cache instances into the cache tables of a dynamic cache group, use a LOAD CACHE GROUP statement or perform a dynamic load operation. See "Dynamically loading a cache instance" for more information about a dynamic load operation.

The following restrictions apply when manually loading or refreshing a dynamic cache group with automatic refresh:

  • The autorefresh state must be PAUSED or ON before you can issue a LOAD CACHE GROUP statement.

  • The autorefresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement.

  • A LOAD CACHE GROUP statement that contains a WHERE clause must include a COMMIT EVERY n ROWS clause after the WHERE clause.

  • A REFRESH CACHE GROUP statement cannot contain a WHERE clause.

  • All tables and columns referenced in a WHERE clause when loading the cache group must be fully qualified. For example:

    user_name.table_name and user_name.table_name.column_name

Loading and refreshing a cache group using a WITH ID clause

The WITH ID clause of the LOAD CACHE GROUP or REFRESH CACHE GROUP statement enables you to load or refresh a cache group based on values of the primary key columns without having to use a WHERE clause. The WITH ID clause is more convenient than the equivalent WHERE clause if the primary key contains more than one column. Using the WITH ID clause allows you to load one cache instance at a time. It also enables you to roll back the transaction containing the load or refresh operation, if necessary, unlike the equivalent statement that uses a WHERE clause because using a WHERE clause also requires specifying a COMMIT EVERY n ROWS clause.

Example 5-5 Loading a cache group using a WITH ID clause

A cache group recent_orders contains a single cache table oratt.orderdetails with a primary key of (orderid, itemid). If a customer calls about an item within a particular order, the information can be obtained by loading the cache instance for the specified order number and item number.

Load the oratt.orderdetails cache table in the recent_orders cache group with the row whose value in the orderid column of the oratt.orderdetails cached Oracle Database table is 1756 and its value in the itemid column is 573:

LOAD CACHE GROUP recent_orders WITH ID (1756,573);

The following is an equivalent LOAD CACHE GROUP statement that uses a WHERE clause:

LOAD CACHE GROUP recent_orders WHERE orderid = 1756 and itemid = 573
  COMMIT EVERY 256 ROWS;

A LOAD CACHE GROUP or REFRESH CACHE GROUP statement issued on an autorefresh cache group cannot contain a WITH ID clause unless the cache group is dynamic.

You cannot use the COMMIT EVERY n ROWS clause with the WITH ID clause.

Initiating an immediate autorefresh

If the Oracle Database tables have been updated with data that needs to be applied to cache tables without waiting for the next autorefresh operation, you can call the ttCacheAutorefresh built-in procedure. The ttCacheAutorefresh built-in procedure initiates an immediate refresh operation and resets the autorefresh cycle to start at the moment you invoke ttCacheAutorefresh. The refresh operation is full or incremental depending on how the cache group is configured. The autorefresh state must be ON when ttCacheAutorefresh is called.

The autorefresh operation normally refreshes all cache groups sharing the same refresh interval in one transaction in order to preserve transactional consistency across these cache groups. Therefore, although you specify a specific cache group when you call ttCacheAutorefresh, the autorefresh operation occurs in one transaction for all cache groups that share the autorefresh interval with the specified cache group. If there is an existing transaction with table locks on objects that belong to the affected cache groups, ttCacheAutofresh returns an error without taking any action.

You can choose to run ttCacheAutorefresh asynchronously (the default) or synchronously. In synchronous mode, ttCacheAutorefresh returns an error if the refresh operation fails.

After calling ttCacheAutorefresh, you must commit or roll back the transaction before subsequent work can be performed.

Example 5-6 Calling ttCacheAutorefresh

This example calls ttCacheAutorefresh for the ttuser.western_customers cache group, using asynchronous mode.

Command> call ttCacheAutorefresh('ttuser', 'western_customers');

Loading and refreshing a multiple-table cache group

If you are loading or refreshing a multiple-table cache group while the cached Oracle Database tables are concurrently being updated, set the isolation level in the TimesTen database to serializable before issuing the LOAD CACHE GROUP or REFRESH CACHE GROUP statement. This causes TimesTen to query the cached Oracle Database tables in a serializable fashion during the load or refresh operation so that the loaded or refreshed cache instances in the cache tables are guaranteed to be transactionally consistent with the corresponding rows in the cached Oracle Database tables. After you have loaded or refreshed the cache group, set the isolation level back to read committed for better concurrency when accessing elements in the TimesTen database.

Improving the performance of loading or refreshing a large number of cache instances

You can improve the performance of loading or refreshing a large number of cache instances into a cache group by using the PARALLEL clause of the LOAD CACHE GROUP or REFRESH CACHE GROUP statement. Specify the number of threads to use when processing the load or refresh operation. You can specify 1 to 10 threads. One thread fetches rows from the cached Oracle Database tables, while the other threads insert the rows into the TimesTen cache tables. Do not specify more threads than the number of CPUs available on your system or you may encounter decreased performance than if you had not used the PARALLEL clause.

Note:

You cannot use the WITH ID clause with the PARALLEL clause. You can use the COMMIT EVERY n ROWS clause with the PARALLEL clause as long as n is greater than 0. In addition, you cannot use the PARALLEL clause for read-only dynamic cache groups or when database level locking is enabled. For more details, see "REFRESH CACHE GROUP" in the Oracle TimesTen In-Memory Database SQL Reference.

Example 5-7 Refreshing a cache group using a PARALLEL clause

The following statement refreshes cache instances in the TimesTen cache tables within the western_customers cache group from the cached Oracle Database tables using one thread to fetch rows from the cached Oracle Database tables and three threads to insert the rows into the cache tables:

REFRESH CACHE GROUP western_customers COMMIT EVERY 256 ROWS PARALLEL 4;

Example of manually loading and refreshing an explicitly loaded cache group

The following is the definition of the Oracle Database table that is to be cached in an explicitly loaded AWT cache group. The Oracle Database table is owned by the schema user oratt.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

The following is the data in the oratt.customer cached Oracle Database table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Wilkins    356 Olive St. Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr. Chicago IL

The following statement creates an explicitly loaded AWT cache group new_customers that caches the oratt.customer table:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num));

The oratt.customer TimesTen cache table is initially empty.

Command> SELECT * FROM oratt.customer;
0 rows found.

The following LOAD CACHE GROUP statement loads the three cache instances from the cached Oracle Database table into the TimesTen cache table:

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instances affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >

Update the cached Oracle Database table by inserting a new row, updating an existing row, and deleting an existing row:

SQL> INSERT INTO customer
  2  VALUES (4, 'East', 'Roberta Simon', '3667 Park Ave. New York NY');
SQL> UPDATE customer SET name = 'Angela Peterson' WHERE cust_num = 2;
SQL> DELETE FROM customer WHERE cust_num = 3;
SQL> COMMIT;
SQL> SELECT * FROM customer;
CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Peterson   356 Olive St. Boston MA
       4   East      Roberta Simon     3667 Park Ave. New York NY

A REFRESH CACHE GROUP statement issued on an explicitly loaded cache group is processed by unloading and then reloading the cache group. As a result, the cache instances in the cache table matches the rows in the cached Oracle Database table.

Command> REFRESH CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instance affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >

Example of manually loading and refreshing a dynamic cache group

The following is the definition of the Oracle Database table that is to be cached in a dynamic AWT cache group. The Oracle Database table is owned by the schema user oratt.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

The following is the data in the oratt.customer cached Oracle Database table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Wilkins    356 Olive St. Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr. Chicago IL

The following statement creates a dynamic AWT cache group new_customers that caches the oratt.customer table:

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num));

The oratt.customer TimesTen cache table is initially empty:

Command> SELECT * FROM oratt.customer;
0 rows found.

The following LOAD CACHE GROUP statement loads the three cache instances from the cached Oracle Database table into the TimesTen cache table:

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instances affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >

Update the cached Oracle Database table by inserting a new row, updating an existing row, and deleting an existing row:

SQL> INSERT INTO customer
  2  VALUES (4, 'East', 'Roberta Simon', '3667 Park Ave. New York NY');
SQL> UPDATE customer SET name = 'Angela Peterson' WHERE cust_num = 2;
SQL> DELETE FROM customer WHERE cust_num = 3;
SQL> COMMIT;
SQL> SELECT * FROM customer;
CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Peterson   356 Olive St. Boston MA
       4   East      Roberta Simon     3667 Park Ave. New York NY

A REFRESH CACHE GROUP statement issued on a dynamic cache group only refreshes committed updates and deletes on the cached Oracle Database tables into the cache tables. New cache instances are not loaded into the cache tables. Therefore, only existing cache instances are refreshed. As a result, the number of cache instances in the cache tables are either fewer than or the same as the number of rows in the cached Oracle Database tables.

Command> REFRESH CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
2 cache instances affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >

A subsequent LOAD CACHE GROUP statement loads one cache instance from the cached Oracle Database table into the TimesTen cache table because only committed inserts are loaded into the cache table. Therefore, only new cache instances are loaded. Cache instances that already exist in the cache tables are not changed because of a LOAD CACHE GROUP statement, even if the corresponding rows in the cached Oracle Database tables were updated or deleted.

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
1 cache instance affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >

Dynamically loading a cache instance

In a dynamic cache group, data is automatically loaded into the TimesTen cache tables from the cached Oracle Database tables when a qualifying SELECT, INSERT, UPDATE, or DELETE statement is issued on one of the cache tables and the data does not exist in the cache table but does exist in the cached Oracle Database table.

Note:

If the Oracle database is down, the following error is returned:
5219: Temporary Oracle connection failure error in OCISessionBegin():
 ORA-01034: ORACLE not available

Note:

Dynamic load can only be performed for dynamic cache groups if the DynamicLoadEnable connection attribute is enabled. See "Dynamic load configuration" for more details.

A dynamic load retrieves a single cache instance that is either automatically loaded from the Oracle database to the TimesTen database or, for dynamic global cache groups, transferred from the grid member that owns the instance to the requesting grid member. A cache instance consists of row from the root table of any cache group (that is uniquely identified by either a primary key or a unique index on the root table) and all related rows in the child tables associated by foreign key relationships.

If a row in the cached Oracle Database table satisfies the WHERE clause, the entire associated cache instance is loaded in order to maintain the defined relationships between primary keys and foreign keys of the parent and child tables. A dynamic load operation cannot load more than one row into the root table of any cache group. Only cache instances whose rows satisfy the WHERE clause of the cache table definitions are loaded.

The WHERE clause must specify one of the following for a dynamic load to occur:

  • An equality condition with constants and/or parameters on all columns of a primary key or a foreign key of any table of the cache group. If more than one table of a cache group is referenced, each must be connected by an equality condition on the primary or foreign key relationship.

  • A mixture of equality or IS NULL conditions on all columns of a unique index, provided that you use at least one equality condition. That is, you can perform a dynamic load where some columns of the unique index are NULL. The unique index must be created on the root table of the cache group.

Note:

Dynamic loading based on a primary key search of the root table performs faster than primary key searches on a child table or foreign key searches on a child table.

The dynamic load is executed in a different transaction than the user transaction that triggers the dynamic load. The dynamic load transaction is committed before the SQL statement that triggers the dynamic load has finished execution. Thus, if the user transaction is rolled back, the dynamically loaded data remains in the cache group.

With global cache groups, the TimesTen database must be attached to a cache grid before dynamic load is allowed with these cache groups. See "Global cache groups" for more information about global cache groups and attaching a TimesTen database to a cache grid.

The following sections describes dynamic load for cache groups:

Dynamic load configuration

Dynamic load can be configured with the DynamicLoadEnable connection attribute as follows:

  • 0 - Disables dynamic load of Oracle Database data to TimesTen dynamic cache groups for the current connection.

  • 1 (default) - Enables dynamic load of Oracle Database data to a single TimesTen dynamic cache group per statement for the current connection. The statement must reference tables of only one dynamic cache group and only in the main query. The statement can also reference non-cache tables. Only one cache instance can be loaded.

  • 2 - Enables dynamic load of Oracle Database data to one or multiple TimesTen dynamic cache groups per statement for the current connection. The referenced tables can exist within multiple cache groups. All cache groups referenced in the main query are to be dynamically loaded; any cache groups referenced solely in a subquery are to be ignored for dynamic load. The statement can also reference non-cache tables. Only one cache instance can be loaded.

Set the appropriate value in the DynamicLoadEnable connection attribute to configure the type of dynamic loading for all cache tables in dynamic cache groups that are accessed within a particular connection.

To enable or disable dynamic loading for a particular transaction, you can set the DynamicLoadEnable optimizer hint. However, the DynamicLoadEnable connection attribute is the only method for configuring what type of dynamic load is enabled.

You can set the DynamicLoadEnable optimizer hint with one of the following methods:

  • Use the ttIsql utility set dynamicloadenable command.

  • Call the ttOptSetFlag built-in procedure with the DynamicLoadEnable flag set to the desired value. The following example sets dynamic loading to 1.

    call ttOptSetFlag('DynamicLoadEnable', 1)
    

Note:

For more details, see "DynamicLoadEnable", "ttIsql" or "ttOptSetFlag" in the Oracle TimesTen In-Memory Database Reference.

You can also set connection attributes with the SQLSetConnectOption ODBC function. See "Option support for SQLSetConnectOption and SQLGetConnectOption" in the Oracle TimesTen In-Memory Database C Developer's Guide for more details.

Dynamic load guidelines

Dynamic load retrieves at most one cache instance for each cache group referenced in the main query. This section details the guidelines under which dynamic load occurs.

Note:

Examples for these guidelines are provided in "Examples of dynamically loading a cache instance".

Dynamic load is available only for the following types of statements issued on a cache table in a dynamic cache group:

  • When an INSERT statement inserts values into any of the child tables of a cache instance that does not currently exist in the TimesTen tables, the cache instance to which the new row belongs dynamically loads. The insert operation for the new child row is propagated to the cached Oracle Database table.

  • SELECT, UPDATE, or DELETE statements require that the WHERE clause have the conditions as stated in "Dynamically loading a cache instance".

The SELECT, UPDATE, or DELETE statements for which dynamic load is available must satisfy the following conditions:

  • If the statement contains a subquery, only the cache group with tables referenced in the main query are considered for a dynamic load.

  • If the statement references multiple tables of the cache group, the statement must include an equality join condition between the primary keys and foreign keys for all parent and child relationships.

  • The statement cannot contain the UNION, INTERSECT, or MINUS set operators.

  • The statement can reference non-cache tables.

  • By default, the statement can reference cache tables from only one dynamic cache group. This behavior is enabled when DynamicLoadEnable is set to 1. However, if DynamicLoadEnable is set to 2, the statement can reference cache tables from multiple dynamic cache groups. See "Dynamic load configuration" for more information.

Dynamic load behavior depends on the setting of DynamicLoadEnable. The following describes the rules that are evaluated to determine if a dynamic load occurs. These rules are followed when DynamicLoadEnable is set to either 1 or 2.

  • Dynamic load does not occur for a cache group if any table of the cache group is specified more than once in any FROM clause.

  • Only the conditions explicitly specified in the query are considered for dynamic load, which excludes any derived conditions.

  • If any cache group is referenced only in a subquery, it is not considered for a dynamic load.

  • If the cache group has a time-based aging policy defined, the timestamp in the root table's row must be within the aging policy's lifetime in order for the cache instance to be loaded. See "Implementing aging in a cache group" for information about defining an aging policy on a cache group.

  • When using an active standby pair replication scheme, dynamic load cannot occur in any subscriber.

When dynamic load is enabled with setting DynamicLoadEnable to 2, you can include multiple dynamic cache groups in the statement. The rules for this situation that determines if a dynamic load occurs are as follows:

  • If multiple cache groups exist within in a query, dynamic load is considered for the cache groups that meet the required conditions for a dynamic load. Dynamic load is not considered for any cache groups that do not meet dynamic load conditions.

  • If tables of any dynamic cache group are referenced in the main query, they are considered for dynamic load, even if other tables in any cache group are referenced in the subquery.

The following considerations can affect dynamic load:

  • If tables within multiple cache groups or non-cache group tables are specified in the main query, the join order influences if the cache instance is loaded. If during the execution of the query, a dynamic load is possible and necessary to produce the query results, the dynamic load occurs. However, if no rows are returned, then some or all of the cache instances are not dynamically loaded.

  • If a statement specifies more than the dynamic load condition on tables of a cache group, the cache instance may be dynamically loaded even though the additional conditions are not qualified for the statement.

Examples of dynamically loading a cache instance

The following is the definition of the Oracle Database tables that are to be cached in a dynamic AWT cache group. The Oracle Database table is owned by the schema user oratt.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

CREATE TABLE orders
(ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
 cust_num     NUMBER(6) NOT NULL,
 when_placed  DATE NOT NULL,
 when_shipped DATE NOT NULL);

CREATE TABLE orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY (orderid, itemid));

For example, the following data is in the oratt.customer cached Oracle Database table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St., Portland OR
       2   East      Angela Wilkins    356 Olive St., Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr., Chicago IL

The following statement creates a dynamic AWT cache group new_customers that caches the oratt.customer, oratt.orders, and oratt.orderdetails tables:

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num)),
oratt.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num)),
oratt.orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY(orderid, itemid),
  FOREIGN KEY(orderid) REFERENCES oratt.orders(order_num));

The following examples can be used when DynamicLoadEnable is set to 1:

The oratt.customer TimesTen cache table is initially empty:

Command> SELECT * FROM oratt.customer;
0 rows found.

The following SELECT statement with an equality condition on the primary key for the oratt.customer table results in a dynamic load:

Command> SELECT * FROM oratt.customer WHERE cust_num = 1;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >

However, if you do not use an equality condition on the primary key, no dynamic load occurs:

Command> SELECT * FROM oratt.customer WHERE cust_num IN (1,2);

The following example contains equality expressions on all of the primary key columns for a primary key composite. The orderdetails table has a composite primary key of orderid and itemid.

UPDATE oratt.orderdetails SET quantity = 5 WHERE orderid=2280 AND itemid=663;

The following example shows an INSERT into the orders child table, which initiates a dynamic load. However, if you tried to insert into the customer table, which is the parent, no dynamic load occurs.

INSERT INTO orders VALUES(1,1, DATE '2012-01-25', DATE '2012-01-30');

The following UPDATE statement dynamically loads one cache instance from the cached Oracle Database table into the TimesTen cache table, updates the instance in the cache table, and then automatically propagates the update to the cached Oracle Database table:

Command> UPDATE oratt.customer SET name = 'Angela Peterson' WHERE cust_num = 2;
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >
< 2, East, Angela Peterson, 356 Olive St., Boston MA >

The following is the updated data in the oratt.customer cached Oracle Database table:

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St., Portland OR
       2   East      Angela Peterson   356 Olive St., Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr., Chicago IL

The following DELETE statement dynamically loads one cache instance from the cached Oracle Database table into the TimesTen cache table, deletes the instance from the cache table, and then automatically propagates the delete to the cached Oracle Database table:

Command> DELETE FROM oratt.customer WHERE cust_num = 3;
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >
< 2, East, Angela Peterson, 356 Olive St., Boston MA >

The following is the updated data in the oratt.customer cached Oracle Database table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St., Portland OR
       2   East      Angela Peterson   356 Olive St., Boston MA

The following is an example of a dynamic load performed using all columns of a unique index on the root table. The departments table is defined in a dynamic AWT cache group. A unique index is created on this cache group consisting of the manager_id and location_id.

The following creates the departments table on the Oracle database.

Command> CREATE TABLE departments(
       > department_id INT NOT NULL PRIMARY KEY,
       > department_name VARCHAR(10) NOT NULL,
       > technical_lead INT NOT NULL,
       > manager_id INT,
       > location_id INT NOT NULL);

The following creates the dynamic AWT cache group and a unique index on the dept_cg root table:

Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP dept_cg
       > FROM departments
       > (department_id INT NOT NULL PRIMARY KEY, 
       >  department_name VARCHAR(10) NOT NULL, 
       >  technical_lead INT NOT NULL, 
       >  manager_id INT, location_id INT NOT NULL);

Command> CREATE UNIQUE INDEX dept_idx ON departments(manager_id, location_id);

The following inserts three records into the departments table on the Oracle database:

Command> insert into departments values (1, 'acct', 1, 1, 100);
1 row inserted.
Command> insert into departments values (2, 'legal', 2, 2, 200);
1 row inserted.
Command> insert into departments values (3, 'owner', 3, NULL, 300);
1 row inserted.
Command> commit;

On TimesTen, dynamically load a cache instance based on the unique index:

Command> SELECT * FROM departments;
0 rows found.
Command> SELECT * FROM departments WHERE manager_id IS NULL AND location_id=300;
< 3, owner, 3, <NULL>, 300 >
1 row found.
Command> SELECT * FROM departments;
< 3, owner, 3, <NULL>, 300 >
1 row found.
Command> SELECT * FROM departments WHERE manager_id=2 AND location_id=200;
< 2, legal, 2, 2, 200 >
1 row found.
Command> SELECT * FROM departments;
< 2, legal, 2, 2, 200 >
< 3, owner, 3, <NULL>, 300 >
2 rows found.

The following examples demonstrate how to use dynamic load when referencing tables across multiple cache groups, which is enabled when DynamicLoadEnable is set to 2.

The following statements create multiple dynamic AWT cache groups, each with one or more tables that are cached from the Oracle database.

CREATE DYNAMIC CACHE GROUP cachegrp 
 FROM table1(x1 INT PRIMARY KEY, y1 INT);

CREATE DYNAMIC CACHE GROUP cachegrp2 
 FROM table2(x2 INT PRIMARY KEY, y2 INT), 
 table3(x3 INT PRIMARY KEY, y3 INT, 
 FOREIGN KEY(y3) REFERENCES table2(x2);

CREATE DYNAMIC CACHE GROUP cachegrp3 
FROM table4(x4 INT PRIMARY KEY, y4 INT);

CREATE TABLE table5
 (x5 INT PRIMAY KEY,y5 INT);

The following example shows that no dynamic load occurs, even though the optimizer may derive that x1 should be equated to 1:

SELECT * FROM table1, table5 WHERE x5=1 AND x5=x1;

Dynamic load is considered for the cache instance from table2 within cachegrp2 since table2 is referenced in the main query.

SELECT * FROM table5, table2 
 WHERE x5 IN (SELECT y2 FROM table2, table3 where x2=1 and x2=y3);

The cache instance in the cachegrp2 cache group is not considered for a dynamic load because all of its tables are referenced in the subquery.

SELECT * FROM table5 
 WHERE x5 IN 
 (SELECT y3 FROM table2, table3 WHERE x2=1 AND x2=y3);

In the following example, if the row that would be retrieved from table1 where x1=1 is not already in the cache, whether the cache instance from table1 is loaded depends on the join order. If the join order is 'table5 table1,' the cache instance from table1 is loaded if and only if there is a row in table5 for which x5=1. If the join order is 'table1 table5', then the cache instance from table1 is always loaded.

SELECT * FROM table1, table5 WHERE x1=1 AND x5=1;  

A row (1,1) may be loaded for table1, but the SELECT does not return any rows.

SELECT * FROM table1 WHERE x1=1 AND y1>1;

Return dynamic load errors

You can configure TimesTen to return an error if a SELECT, UPDATE or DELETE statement does not meet the requirements stated in "Dynamic load guidelines". The DynamicLoadErrorMode connection attribute controls what happens when an application executes a SQL operation against a dynamic cache group and the SQL operation cannot use dynamic load in a particular connection.

  • When DynamicLoadErrorMode is set to a value of 0, dynamic load happens to any cache group referenced in the query that is qualified for dynamic load. Cache groups that do not qualify are not dynamically loaded and no errors are returned. When DynamicLoadEnable=1, no dynamic load occurs if the query references more than one cache group.

  • When DynamicLoadErrorMode is set to a value of 1, a query fails with an error if any dynamic cache group referenced in the query is not qualified for dynamic load. The error indicates the reason why the dynamic load cannot occur.

To set the connection attribute solely for a particular transaction, use one of the following:

  • Use the ttIsql utility set dynamicloaderrormode 1 command.

  • Call the ttOptSetFlag built-in procedure with the DynamicLoadErrorMode flag and the optimizer value set to 1.

    call ttOptSetFlag('DynamicLoadErrorMode', 1)
    

    Call the ttOptSetFlag built-in procedure with the DynamicLoadErrorMode flag and the optimizer value set to 0 to suppress error reporting when a statement does not comply with dynamic load requirements.

Flushing a user managed cache group

The FLUSH CACHE GROUP statement manually propagates committed inserts and updates on TimesTen cache tables in a user managed cache group to the cached Oracle Database tables. Deletes are not flushed or manually propagated. Committed inserts and updates on cache tables that use the PROPAGATE cache table attribute cannot be flushed to the cached Oracle Database tables because these operations are already automatically propagated to the Oracle database.

With automatic propagation, committed inserts, updates and deletes are propagated to the Oracle database in the order they were committed in TimesTen. A flush operation can manually propagate multiple committed transactions on cache tables to the cached Oracle Database tables.

You cannot flush a user managed cache group that uses the AUTOREFRESH cache group attribute.

You can flush a user managed cache group if at least one of its cache tables uses neither the PROPAGATE nor the READONLY cache table attribute.

You can use a WHERE clause or WITH ID clause in a FLUSH CACHE GROUP statement to restrict the rows to be flushed to the cached Oracle Database tables. See the "FLUSH CACHE GROUP" statement in Oracle TimesTen In-Memory Database SQL Reference for more information.

Example 5-8 Flushing a cache group

The following statement manually propagates committed insert and update operations on the TimesTen cache tables in the western_customers cache group to the cached Oracle Database tables:

FLUSH CACHE GROUP western_customers;

Unloading a cache group

You can delete some or all cache instances from the cache tables in a cache group with the UNLOAD CACHE GROUP statement. Unlike the DROP CACHE GROUP statement, the cache tables themselves are not dropped when a cache group is unloaded.

Use caution when using the UNLOAD CACHE GROUP statement with autorefresh cache groups. An unloaded row can reappear in the cache table as the result of an autorefresh operation if the row, or its related parent or child rows, are updated in the cached Oracle Database table.

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.

To prevent an unload operation from processing a large number of cache instances within a single transaction, which could reduce concurrency and throughput, use the COMMIT EVERY n ROWS clause to specify a commit frequency.

Note:

For more information, see "UNLOAD CACHE GROUP" in the Oracle TimesTen In-Memory Database SQL Reference.

Example 5-9 Unloading cache groups

The following statement unloads all cache instances from all cache tables in the customer_orders cache group. A commit frequency is specified, so the operations is performed over several transactions by committing every 256 rows:

UNLOAD CACHE GROUP customer_orders COMMIT EVERY 256 ROWS;

The following statement unloads all cache instances from all cache tables in the customer_orders cache group in a single transaction. A single transaction should only be used if the data within customer_orders is small:

UNLOAD CACHE GROUP customer_orders;

The following equivalent statements delete the cache instance for customer number 227 from the cache tables in the new_customers cache group:

UNLOAD CACHE GROUP new_customers WITH ID (227);
UNLOAD CACHE GROUP new_customers WHERE (oratt.customer.cust_num = 227);

Unloading a cache group across all grid members

You can unload a cache group in all members of a cache grid by setting an optimizer flag. Before executing the UNLOAD CACHE GROUP statement, call the ttOptSetFlag built-in procedure and set the GlobalProcessing optimizer flag to 1:

Note:

The unload operation does not execute across multiple grid nodes when using the COMMIT EVERY n ROWS clause, regardless of the state of the GlobalProcessing optimizer flag.
CALL ttOptSetFlag('GlobalProcessing', 1);

Consider this statement:

UNLOAD CACHE GROUP customer WHERE customer_id=54321;

A local unload operation removes the customer record only if the record exists on the node where the statement is executed. A global unload operation removes the customer record regardless of which node contains the record.

Determining the number of cache instances affected by an operation

You can use the following mechanisms to determine how many cache instances were loaded by a LOAD CACHE GROUP statement, refreshed by a REFRESH CACHE GROUP statement, flushed by a FLUSH CACHE GROUP statement, or unloaded by an UNLOAD CACHE GROUP statement:

  • Call the SQLRowCount() ODBC function.

  • Invoke the Statement.getUpdateCount() JDBC method.

  • Call the OCIAttrGet() OCI function with the OCI_ATTR_ROW_COUNT option.

Setting a passthrough level

When an application issues statements on a TimesTen connection, the statement can be executed in the TimesTen database or passed through to the Oracle database for execution. Whether the statement is executed in the TimesTen or Oracle database depends on the composition of the statement and the setting of the PassThrough connection attribute. You can set the PassThrough connection attribute to define which statements are to be executed locally in TimesTen and which are to be redirected to the Oracle database for execution.

When appropriate within passthrough levels 1 through 5, TimesTen connects to the Oracle database using the current user's credentials as the user name and the OraclePwd connection attribute as the Oracle password.

Note:

A transaction that contains operations that are replicated with RETURN TWOSAFE cannot have a PassThrough setting greater than 0. If PassThrough is greater than 0, an error is returned and the transaction must be rolled back.

When PassThrough is set to 0, 1, or 2, the following behavior occurs when a dynamic load condition exists:

  • A dynamic load can occur for a SELECT operation on cache tables in any dynamic cache group type.

  • A dynamic load for an INSERT, UPDATE, or DELETE operation can only occur on cached tables with dynamic AWT or SWT cache groups.

See "Dynamically loading a cache instance" for more details on dynamic load.

PassThrough=0

PassThrough=0 is the default setting and specifies that all statements are to be executed in the TimesTen database. Figure 5-1 shows that Table A is updated on the TimesTen database. Table F cannot be updated because it does not exist in TimesTen.

PassThrough=1

Set PassThrough=1 to specify that a statement that references a table that does not exist in the TimesTen database is passed through to the Oracle database for execution. No DDL statements are passed through to the Oracle database.

If TimesTen cannot parse a SELECT statement because it includes keywords that do not exist in TimesTen SQL or because it includes syntax errors, it passes the statement to the Oracle database. If TimesTen cannot parse INSERT, UPDATE or DELETE statements, TimesTen returns an error and the statement is not passed through to the Oracle database.

Figure 5-2 shows that Table A is updated in the TimesTen database, while Table G is updated in the Oracle database because Table G does not exist in the TimesTen database.

PassThrough=2

PassThrough=2 specifies that INSERT, UPDATE and DELETE statements are passed through to the Oracle database for read-only cache groups and user managed cache groups that use the READONLY cache table attribute. Otherwise, Passthrough=1 behavior applies.

Note:

You are responsible in preventing conflicts that may occur if you update the same row in a TimesTen cache table as another user updates the cached Oracle Database table concurrently.

Figure 5-3 shows that updates to Table A and Table G in a read-only cache group are passed through to the Oracle database.

PassThrough=3

PassThrough=3 specifies that all statements are passed through to the Oracle database for execution, except that INSERT, UPDATE and DELETE statements issued on cache tables in a dynamic AWT global cache group result in a TimesTen error.

Figure 5-4 shows that Table A is updated on the Oracle database for a read-only or updatable cache group. A SELECT statement that references Table G is also passed through to the Oracle database. A SELECT statement that references Table C in a dynamic AWT global cache group is passed through to the Oracle database.

PassThrough=4

PassThrough=4 specifies that SELECT statements issued on cache tables in a dynamic AWT global cache group that do not satisfy the criteria for a dynamic load query are passed through to the Oracle database for execution. Otherwise, statements are executed in the TimesTen database. See "Dynamic load guidelines" for the criteria for a dynamic load SELECT statement.

Figure 5-5 shows that Table A in an updatable cache group is updated in the TimesTen database. The figure also shows a SELECT statement issued on a dynamic AWT global cache group that does not satisfy the criteria for a dynamic load SELECT statement and is passed through to the Oracle database for execution.

PassThrough=5

PassThrough=5 specifies that SELECT statements issued on cache tables in a dynamic AWT global cache group that do not satisfy the criteria for a dynamic load query are passed through to the Oracle database for execution when all committed updates on cache tables in dynamic AWT global cache groups by previous transactions within the connection have been propagated to the Oracle database. Otherwise statements are executed in the TimesTen database. See "Dynamic load guidelines" for the criteria for a dynamic load SELECT statement.

Figure 5-6 shows that Table A in an updatable cache group is updated in the TimesTen database. The figure also shows a SELECT statement issued on a dynamic AWT global cache group that does not satisfy the criteria for a dynamic load SELECT statement and is passed through to the Oracle database for execution after all committed updates on cache tables in dynamic AWT global cache groups by previous transactions within the connection have been propagated to the Oracle database.

Considerations for using passthrough

Passing through update operations to the Oracle database for execution is not recommended when issued on cache tables in an AWT or SWT cache group.

  • Committed updates on cache tables in an AWT cache group are automatically propagated to the cached Oracle Database tables in asynchronous fashion. However, passing through an update operation to the Oracle database for execution within the same transaction as the update on the cache table in the AWT cache group renders the propagate of the cache table update synchronous, which may have undesired results.

  • Committed updates on cache tables in an SWT cache group can result in self-deadlocks if, within the same transaction, updates on the same tables are passed through to the Oracle database for execution.

A PL/SQL block cannot be passed through to the Oracle database for execution. Also, you cannot pass through to Oracle Database for execution a reference to a stored procedure or function that is defined in the Oracle database but not in the TimesTen database.

For more information about how the PassThrough connection attribute setting determines which statements are executed in the TimesTen database and which are passed through to the Oracle database for execution and under what circumstances, see "PassThrough" in Oracle TimesTen In-Memory Database Reference.

Note:

The passthrough feature uses OCI to communicate with the Oracle database. The OCI diagnostic framework installs signal handlers that may impact signal handling that you use in your application. You can disable OCI signal handling by setting DIAG_SIGHANDLER_ENABLED=FALSE in the sqlnet.ora file. Refer to "Fault Diagnosability in OCI" in Oracle Call Interface Programmer's Guide for information.

Changing the passthrough level for a connection or transaction

You can override the current passthrough level using the ttIsql utility's set passthrough command which applies to the current transaction.

You can also override the setting for a specific transaction by calling the ttOptSetFlag built-in procedure with the PassThrough flag. The following procedure call sets the passthrough level to 3:

CALL ttOptSetFlag('PassThrough', 3);

The PassThrough flag setting takes effect when a statement is prepared and it is the setting that is used when the statement is executed even if the setting has changed from the time the statement was prepared to when the statement is executed. After the transaction has been committed or rolled back, the original connection setting takes effect for all subsequently prepared statements.