Oracle® In-Memory Database Cache User's Guide Release 11.2.1 Part Number E13073-11 |
|
|
View PDF |
This chapter describes operations that can be performed on cache groups. It includes the following topics:
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 TimesTen In-Memory Database Support User's Guide.
You can use the following SQL statements to manually transmit committed updates between the TimesTen cache tables and the cached Oracle tables:
SQL statement | Description |
---|---|
LOAD CACHE GROUP |
Load cache instances that are not in the TimesTen cache tables from the cached Oracle tables |
REFRESH CACHE GROUP |
Replace cache instances in the TimesTen cache tables with current data from the cached Oracle tables |
FLUSH CACHE GROUP |
Propagate committed updates on the TimesTen cache tables to the cached Oracle 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 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 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 tables when a particular statement does not find the data in the cache tables.
See "Dynamically loading a cache group" 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.
You can manually insert or update cache instances in the TimesTen cache tables from the cached Oracle 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 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 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 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 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 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 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 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 will 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 group" 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, you must 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 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 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 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 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 automatic refresh
Loading and refreshing a dynamic cache group with automatic refresh
Improving the performance of loading or refreshing a large number of cache instances
Example of manually loading and refreshing an explicitly loaded cache group
Example of manually loading and refreshing a dynamic cache group
If the automatic refresh state of an explicitly loaded cache group is PAUSED
, the automatic refresh 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 automatic refresh:
A LOAD CACHE GROUP
statement can only be issued if the cache tables are empty.
The automatic refresh state must be PAUSED
before you can issue a LOAD CACHE GROUP
statement.
The automatic refresh 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 automatic refresh operation occurs on an explicitly loaded cache group, all committed inserts, updates and deletes on the cached Oracle tables since the last automatic refresh 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 tables have been updated or deleted.
If the automatic refresh state of a dynamic cache group is PAUSED
, the automatic refresh 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 automatic refresh state of a dynamic cache group is PAUSED
, the automatic refresh 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 automatic refresh operation only refreshes committed updates and deletes on the cached Oracle tables since the last automatic refresh 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 group" 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 automatic refresh state must be PAUSED
or ON
before you can issue a LOAD CACHE GROUP
statement.
The automatic refresh 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
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 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 automatic refresh 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.
If the Oracle tables have been updated with data that needs to be applied to cache tables without waiting for the next automatic refresh operation, you can call the ttCacheAutorefresh
built-in procedure. The ttCacheAutorefresh
built-in procedure initiates an immediate refresh operation and resets the automatic refresh 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 automatic refresh state must be ON
when ttCacheAutorefresh
is called.
The automatic refresh 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 automatic refresh operation occurs in one transaction for all cache groups that share the automatic refresh 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.
If you are loading or refreshing a multiple-table cache group while the cached Oracle 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 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 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.
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 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.
You cannot use the WITH ID
clause or the COMMIT EVERY 0 ROWS
clause with the PARALLEL
clause.
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 tables using one thread to fetch rows from the cached Oracle tables and three threads to insert the rows into the cache tables:
REFRESH CACHE GROUP western_customers COMMIT EVERY 256 ROWS PARALLEL 4
The following is the definition of the Oracle table that will be cached in an explicitly loaded AWT cache group. The Oracle 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 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 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 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 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 >
The following is the definition of the Oracle table that will be cached in a dynamic AWT cache group. The Oracle 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 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 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 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 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 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 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 as a result of a LOAD CACHE GROUP
statement, even if the corresponding rows in the cached Oracle 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 >
In a dynamic cache group, data is automatically loaded into the TimesTen cache tables from the cached Oracle tables when a SELECT
, UPDATE
, DELETE
or INSERT
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 table.
If a row in the cached Oracle table satisfies the statement's WHERE
clause, the entire 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 the cache group.
Only cache instances whose rows satisfy the WHERE
clause of the cache table definitions are loaded. 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.
Dynamic load can be used to reload a cache instance that was aged out or deleted. See "Implementing aging on a cache group" for information about defining an aging policy on a cache group.
To ensure that data in the cache tables within a dynamic cache group is consistent with the cached Oracle tables, update operations on the cache tables are processed differently in a dynamic cache group than in an explicitly loaded cache group.
When an UPDATE
or DELETE
statement is issued on a cache table in a dynamic AWT, SWT, or user managed cache group that does not use the READONLY
cache table attribute, and no rows in the cache table satisfy the statement's WHERE
clause, the matching row in the cached Oracle table, if it exists, is dynamically loaded into the cache table. The loaded row is then updated or deleted in the cache table. The update or delete operation is then propagated to the cached Oracle table if the cache table is in a dynamic AWT, SWT, or user managed cache group that uses the PROPAGATE
cache table attribute.
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.
A SELECT
statement that results in a cache instance being dynamically loaded from the cached Oracle tables into the TimesTen cache tables can be issued on a cache table in a dynamic cache group of any supported type.
If there is no row in a child table that satisfies an equality expression on a foreign key column in a SELECT
statement, then no cache instance is loaded.
You cannot dynamically load a cache instance into a cache table within a dynamic global cache group unless the accompanying TimesTen database is attached to a cache grid. See "Global cache groups" for more information about global cache groups and attaching a TimesTen database to a cache grid.
Dynamic load is available only for the following types of statements issued on a cache table in a dynamic cache group:
SELECT
, UPDATE
or DELETE
with an equality expression on the primary key column. The equality expression can only contain constants or parameters. For example:
SELECT * FROM oratt.customer WHERE cust_num=50
If the primary key is composite, the SELECT
, UPDATE
or DELETE
statement must contain equality expressions on all of the primary key columns. For example:
UPDATE oratt.orderdetails SET quantity = 5 WHERE orderid=2280 AND itemid=663
SELECT
, UPDATE
or DELETE
with an equality expression on the foreign key column. The equality expression can only contain constants or parameters. For example:
DELETE FROM oratt.cust_interests WHERE custid=364
If the foreign key is composite, the SELECT
, UPDATE
or DELETE
statement must contain equality expressions on all of the foreign key columns. For example:
SELECT * FROM oratt.orders WHERE ord_num=4955 AND cust_num=716
Dynamic loading based on a primary key search of the root table has faster performance than primary key searches on a child table or foreign key searches on a child table.
The SELECT
, UPDATE
or DELETE
statements for which dynamic load is available must satisfy the following conditions:
If the statement contains a subquery, the equality expression must be specified in the outermost query of the statement. The statement can only reference cache tables from one cache group but it can also reference non-cache tables.
The statement cannot contain the UNION
, INTERSECT
or MINUS
set operators.
The following is the definition of the Oracle table that will be cached in a dynamic AWT cache group. The Oracle 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 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 SELECT
statement dynamically loads one cache instance from the cached Oracle table into the TimesTen cache table:
Command> SELECT * FROM oratt.customer WHERE cust_num = 1; < 1, West, Frank Edwards, 100 Pine St., Portland OR >
The following UPDATE
statement dynamically loads one cache instance from the cached Oracle table into the TimesTen cache table, updates the instance in the cache table, and then automatically propagates the update to the cached Oracle 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 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 table into the TimesTen cache table, deletes the instance from the cache table, and then automatically propagates the delete to the cached Oracle 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 table.
CUST_NUM REGION NAME ADDRESS -------- ------- --------------- --------------------------- 1 West Frank Edwards 100 Pine St., Portland OR 2 East Angela Peterson 356 Olive St., Boston MA
You can use the following mechanisms to disable dynamic loading on all cache tables in dynamic cache groups that are accessed within a particular connection:
Call the SQLSetConnectOption()
ODBC function with the TT_DYNAMIC_LOAD_ENABLE
connection option and the value parameter set to 0. (There is no equivalent JDBC or OCI function to disable dynamic loading.)
rc = SQLSetConnectOption(hDbc, TT_DYNAMIC_LOAD_ENABLE, 0)
Call the SQLSetConnectOption()
ODBC function with the TT_DYNAMIC_LOAD_ENABLE
connection option and the value parameter set to 1 to re-enable dynamic loading.
You can use the following mechanisms to disable dynamic loading on all cache tables in dynamic cache groups that are accessed within a particular transaction:
Call the ttOptSetFlag
built-in procedure with the DynamicLoadEnable
flag and the optimizer value set to 0.
call ttOptSetFlag('DynamicLoadEnable', 0)
Call the ttOptSetFlag
built-in procedure with the DynamicLoadEnable
flag and the optimizer value set to 1 to re-enable dynamic loading.
The DynamicLoadEnable
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.
You can configure TimesTen to return an error if a SELECT
, UPDATE
or DELETE
statement does not meet the requirements stated in "Types of SQL statements for which dynamic load is available". 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. With a value of 0, the SQL operation executes against whatever data is already in the TimesTen cache tables and returns a result based on that data with no error indicated. With a value of 1, any statement that cannot use dynamic load (even if it does not need dynamic load) fails with an error indicating that it does not comply with dynamic load requirements.
You can use the following mechanisms to return an error if a statement within a particular connection does not comply with dynamic load requirements:
Call the SQLSetConnectOption()
ODBC function with the TT_DYNAMIC_LOAD_ERROR_MODE
connection option and the value parameter set to 1. (There is no equivalent JDBC or OCI function to display dynamic load errors.)
rc = SQLSetConnectOption(hDbc, TT_DYNAMIC_LOAD_ERROR_MODE, 1)
Call the SQLSetConnectOption()
ODBC function with the TT_DYNAMIC_LOAD_ERROR_MODE
connection option and the value parameter set to 0 to suppress error reporting when a statement does not comply with dynamic load requirements.
You can use the following mechanisms to return an error if a statement within a particular transaction does not comply with dynamic load requirements:
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.
The DynamicLoadErrorMode
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.
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 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 tables because these operations are already automatically propagated to Oracle.
With automatic propagation, committed inserts, updates and deletes are propagated to Oracle in the order they were committed in TimesTen. A flush operation can manually propagate multiple committed transactions on cache tables to the cached Oracle 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 tables. See the FLUSH CACHE GROUP
statement in Oracle TimesTen In-Memory Database SQL Reference for more information.
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 automatic refresh cache groups. An unloaded row can reappear in the cache table as the result of an automatic refresh operation if the row, or its related parent or child rows, are updated in the cached Oracle table.
Example 5-9 Unloading cache groups
The following statement deletes all cache instances from all cache tables in the customer_orders
cache group:
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)
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:
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.
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:
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 Oracle for execution.
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.
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.
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 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.
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 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 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 "Types of SQL statements for which dynamic load is available" 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 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 "Types of SQL statements for which dynamic load is available" 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.
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 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 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 settingDIAG_SIGHANDLER_ENABLED=FALSE
in the sqlnet.ora
file. Refer to "Fault Diagnosability in OCI" in Oracle Call Interface Programmer's Guide for information.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.
This section contains information about cache performance.
See Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide for extensive information about monitoring autorefresh operations and improving autorefresh performance. See "Monitoring autorefresh cache groups" and "Poor autorefresh performance".
Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide also has information about AWT cache group performance. See "Monitoring AWT performance" and "Possible causes of poor AWT performance".
Dynamic loading based on a primary key search of the root table has faster performance than primary key searches on a child table or foreign key searches on a child table.
By default, an AWT cache group uses SQL array execution to apply changes within TimesTen to the Oracle database. This method works well when the same type of operation is repeated. For example, array execution is very efficient when a user does an update that affects several rows of the table. Updates are grouped together and sent to the Oracle server in one batch.
Use the cacheAWTMethod
connection attribute to specify the PL/SQL execution method. When you specify the PL/SQL execution method, AWT bundles all pending operations into a single PL/SQL collection that is sent to the Oracle server to be executed. This method can improve AWT throughput when there are mixed transactions and network latency between TimesTen and the Oracle server.
PL/SQL execution method transparently falls back to SQL array execution mode temporarily when it encounters one of the following:
A statement that is over 32761 bytes in length.
A statement that references a column of type BINARY FLOAT
, BINARY DOUBLE
and VARCHAR/VARBINARY
of length greater than 4000 bytes.
For more information, see "cacheAWTMethod" in Oracle TimesTen In-Memory Database Reference.