| Oracle® In-Memory Database Cache User's Guide Release 11.2.1 Part Number E13073-02 |
|
|
View PDF |
| Oracle® In-Memory Database Cache User's Guide Release 11.2.1 Part Number E13073-02 |
|
|
View PDF |
This chapter describes the different types of cache groups and how to define them. It includes the following topics:
A cache group defines the Oracle data to cache in the TimesTen database. When you create a cache group, cache tables are created in the TimesTen database that correspond to the Oracle tables being cached.
A separate table definition must be specified in the cache group definition for each Oracle table that is being cached. The owner, table name and cached column names of a TimesTen cache table must match the owner, table name and column names of the corresponding cached Oracle table. The cache table can contain all or a subset of the columns and rows of the cached Oracle table.
Each Oracle table to be cached in TimesTen must have a primary key or a unique index defined on non-nullable columns. Also, the primary key or unique index that is defined on each TimesTen cache table must match that of the cached Oracle table. For example, if the cached Oracle table has a composite primary key on columns C1, C2 and C3, the TimesTen cache table must also have a composite primary key on columns C1, C2 and C3. Therefore, a cache table must have a primary key or a unique index defined on non-nullable columns just like the cached Oracle table.
You can also create non-unique indexes on a TimesTen cache table. Creating indexes on a cache table can help speed up particular queries issued on the table in the same fashion as on a TimesTen regular table.
Do not create unique indexes on a cache table that do not match any unique index on the cached Oracle table. Otherwise, it can cause unique constraint failures in the cache table that do not occur in the cached Oracle table, and result in these tables in the two databases being no longer synchronized with each other when automatic refresh operations are performed.
A TimesTen database can contain multiple cache groups. A cache group can contain one or more cache tables. An Oracle table cannot be cached in more than one cache group within the same TimesTen database.
The simplest cache group is one that caches a single Oracle table. In a single-table cache group, there is a root table but no child tables.
Figure 4-1 shows a single-table cache group target_customers that caches the customer table.
Figure 4-1 Cache group with a single table
A multiple-table cache group is one that defines a root table and one or more child tables. A cache group can only contain one root table. Each child table must reference the primary key or a unique index of the root table or of another child table in the cache group using a foreign key constraint. Although tables in a multiple-table cache group must be related to each other in the TimesTen database through foreign key constraints, it is not required that the tables be related to each other in the Oracle database. The root table does not reference any table in the cache group with a foreign key constraint.
Figure 4-2 shows a multiple-table cache group customer_orders that caches the customer, orders and order_item tables. Each parent table in the customer_orders cache group has a primary key that is referenced by a child table through a foreign key constraint. The customer table is the root table of the cache group because it does not reference any table in the cache group with a foreign key constraint. The primary key of the root table is considered the primary key of the cache group. The orders table is a child table of the customer root table. The order_item table is a child table of the orders child table.
Figure 4-2 Cache group with multiple tables
The table hierarchy in a multiple-table cache group can designate child tables to be parents of other child tables. A child table cannot reference more than one parent table. However, a parent table can be referenced by more than one child table.
Figure 4-3 shows an improper cache table hierarchy. Neither the customer nor the product table references a table in the cache group with a foreign key constraint. This results in the cache group having two root tables which is invalid.
Figure 4-3 Problem: Cache group contains two root tables
To resolve this problem and cache all the tables, create a cache group which contains the customer, orders, and order_item tables, and a second cache group which contains the product and the inventory tables as shown in Figure 4-4.
Figure 4-4 Solution: Create two cache groups
Cache groups are identified as either system managed or user managed. System managed cache groups enforce specific behaviors, while the behavior of a user managed cache group can be customized. System managed cache group types include:
See "User managed cache group" for information about user managed cache groups.
The following topics also apply to creating a cache group:
Cache groups must be created and are owned by the cache manager user.
A read-only cache group enforces a caching behavior where the TimesTen cache tables cannot be updated directly, and committed updates on the cached Oracle tables are automatically refreshed to the cache tables as shown in Figure 4-5.
If the TimesTen database is unavailable for whatever reason, you can still update the Oracle tables that are cached in a read-only cache group. When the TimesTen database returns to operation, updates that were committed on the cached Oracle tables while the TimesTen database was unavailable will be automatically refreshed to the TimesTen cache tables.
The following are the definitions of the Oracle tables that will be cached in the read-only cache groups that are defined in Example 4-1, Example 4-10, Example 4-11, Example 4-17 and Example 4-18. The Oracle tables are owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.
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)
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.customer and oratt.orders tables in order for the cache manager user to create a read-only cache group that caches these tables, and for automatic refresh operations to occur from the cached Oracle tables to the TimesTen cache tables.
Use the CREATE READONLY CACHE GROUP statement to create a read-only cache group.
Example 4-1 Creating a read-only cache group
The following statement creates a read-only cache group customer_orders that caches the tables oratt.customer (root table) and oratt.orders (child table):
CREATE READONLY CACHE GROUP customer_orders 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))
The cache tables in a read-only cache group cannot be updated directly. However, you can set the passthrough level to 2 to allow committed update operations issued on a TimesTen cache table to be passed through and processed on the cached Oracle table, and then have the updates be automatically refreshed into the cache table.
See "Setting a passthrough level" for information about how to set a passthrough level.
The effects of a passed through statement on cache tables in a read-only cache group do not occur in the transaction in which the update operation was issued. Instead, they are seen after the passed through update operation has been committed on the Oracle database and the next automatic refresh of the cache group has occurred. The Oracle user with the same name as the TimesTen cache manager user must be granted the INSERT, UPDATE and DELETE privileges on the Oracle tables that are cached in the read-only cache group in order for the passed through update operations to be processed on the cached Oracle tables.
If you manually created the Oracle objects used to enforce the predefined behaviors of an automatic refresh cache group as described in "Manually create Oracle objects used to manage caching of Oracle data", you need to set the automatic refresh state to OFF when creating the cache group.
Then you need to run the ttIsql utility's cachesqlget command to generate a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle table that is cached in the read-only cache group.
See "Manually creating Oracle objects for automatic refresh cache groups" for information about how to create these objects.
The following restrictions apply when using a read-only cache group:
The cache tables cannot be updated directly.
Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.
See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE cache table attribute.
See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON cache table attribute.
A FLUSH CACHE GROUP statement cannot be issued on the cache group.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.
A TRUNCATE TABLE statement issued on a cached Oracle table is not automatically refreshed to the TimesTen cache table.
A LOAD CACHE GROUP statement can only be issued on the cache group if the cache tables are empty, unless the cache group is dynamic.
See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP statement.
See "Dynamic cache groups" for more information about dynamic cache groups.
The automatic refresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement on the cache group, unless the cache group is dynamic, in which case the automatic refresh state must be PAUSED or ON. The LOAD CACHE GROUP statement cannot contain a WHERE clause, unless the cache group is dynamic, in which case the WHERE clause must be followed by a COMMIT EVERY n ROWS clause.
See "AUTOREFRESH cache group attribute" for more information about automatic refresh states.
See "Using a WHERE clause" for more information about WHERE clauses in cache group definitions and operations.
The automatic refresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement on the cache group. The REFRESH CACHE GROUP statement cannot contain a WHERE clause.
See "Loading and refreshing a cache group" for more information about the REFRESH CACHE GROUP statement.
All tables and columns referenced in WHERE clauses when creating, loading or unloading the cache group must be fully qualified. For example: user_name.table_name and user_name.table_name.column_name.
Least recently used (LRU) aging cannot be specified on the cache group, unless the cache group is dynamic where LRU aging is defined by default.
See "LRU aging" for more information about LRU aging.
An asynchronous writethrough (AWT) cache group enforces a caching behavior where committed updates on the TimesTen cache tables are automatically and asynchronously propagated to the cached Oracle tables as shown in Figure 4-6.
Figure 4-6 Asynchronous writethrough cache group
The transaction commit on the TimesTen database occurs asynchronously from the commit on the Oracle database. This allows an application to continue issuing transactions on the TimesTen database without having to wait for the Oracle transaction to complete. However, your application cannot ensure when the transactions are completed on the Oracle database.
You can update cache tables in an AWT cache group even if the Oracle database is unavailable. When the Oracle database returns to operation, updates that were committed on the cache tables while the Oracle database was unavailable will be automatically propagated to the cached Oracle tables.
The following is the definition of the Oracle table that will be cached in the AWT cache groups that are defined in Example 4-2, Example 4-12 and Example 4-13. The Oracle table is owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.
CREATE TABLE customer (cust_num NUMBER(6) NOT NULL PRIMARY KEY, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100))
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.customer table in order for the cache manager user to create an AWT cache group that caches this table. The Oracle cache administration user must be granted the INSERT, UPDATE and DELETE privileges on the oratt.customer table for asynchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.
Use the CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement to create an AWT cache group.
Example 4-2 Creating an AWT cache group
The following statement creates an asynchronous writethrough 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))
Performing asynchronous writethrough operations requires that the replication agent be running on the TimesTen database that contains AWT cache groups. Executing a CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement creates a replication scheme that enables committed updates on the TimesTen cache tables to be asynchronously propagated to the cached Oracle tables.
After you have created AWT cache groups, start the replication agent on the TimesTen database.
Example 4-3 Starting the replication agent
The replication agent can be manually started programmatically by calling the ttRepStart built-in procedure as the cache manager user:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> call ttRepStart;
It can also be started from a command line by running a ttAdmin -repStart utility command as a TimesTen external user with the CACHE_MANAGER privilege:
% ttAdmin -repStart cachealone1
The replication agent will not start unless there is at least one AWT cache group or replication scheme in the TimesTen database.
If the replication agent is running, it must be stopped before you can issue another CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement or a DROP CACHE GROUP statement on an AWT cache group.
Example 4-4 Stopping the replication agent
The replication agent can be manually stopped programmatically by calling the ttRepStop built-in procedure as the cache manager user:
Command> call ttRepStop;
It can also be stopped from a command line by running a ttAdmin -repStop utility command as a TimesTen external user with the CACHE_MANAGER privilege:
% ttAdmin -repStop cachealone1
You can set a replication agent start policy to determine how and when the replication agent process starts on a TimesTen database.
The default start policy is manual which means the replication agent must be started manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command. To manually stop a running replication agent process, call the ttRepStop built-in procedure or run a ttAdmin -repStop utility command.
The start policy can be set to always so that the replication agent starts automatically when the TimesTen main daemon process starts. With the always start policy, the replication agent cannot be stopped when the main daemon is running unless the start policy is changed to either manual or norestart and then a manual stop is issued by calling the ttRepStop built-in procedure or running a ttAdmin -repStop utility command.
With the manual and always start policies, the replication agent automatically restarts after a failure such as a database invalidation.
The start policy can be set to norestart which means the replication agent must be started manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command, and stopped manually by calling the ttRepStop built-in procedure or running a ttAdmin -repStop utility command.
With the norestart start policy, the replication agent does not automatically restart after a failure such as a database invalidation. You must restart the replication agent manually by calling the ttRepStart built-in procedure or running a ttAdmin -repStart utility command.
Example 4-5 Setting a replication agent start policy
As the instance administrator, grant the ADMIN privilege to the cache manager user:
% ttIsql cachealone1 Command> GRANT ADMIN TO cacheuser; Command> exit
The replication agent start policy can be set programmatically by calling the ttRepPolicySet built-in procedure as the cache manager user:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> call ttRepPolicySet('manual'); Command> exit
It can also be set from a command line by running a ttAdmin -repPolicy utility command as a TimesTen external user with the ADMIN privilege:
% ttAdmin -repPolicy always cachealone1
An AWT cache group can guarantee:
No transactions will be lost because of communication failures between the TimesTen and Oracle databases.
If the replication agent is not running or loses its connection to the Oracle database, automatic propagation of committed updates on the TimesTen cache tables to the cached Oracle tables will resume after the agent is restarted or is able to reconnect to the Oracle database.
Transactions are committed in the Oracle database in the same order they were committed in the TimesTen database.
An AWT cache group cannot guarantee:
All transactions committed successfully in the TimesTen database will be successfully propagated to and committed in the Oracle database. Execution errors on Oracle cause the transaction in the Oracle database to be rolled back. For example, an update on Oracle may fail because of a unique constraint violation. Transactions that contain execution errors are not retried.
Execution errors are reported to the TimesTenDatabaseFileName.awterrs file that resides in the same directory as the TimesTen database's checkpoint files. See "Permanent Oracle errors reported by TimesTen" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide for information about the contents of this file.
The absolute order of Oracle updates is preserved because TimesTen does not resolve update conflicts. The following are some examples:
An update is committed on a cache table in an AWT cache group. The same update is committed on the cached Oracle table using a passthrough operation. The cache table update, which is automatically and asynchronously propagated to Oracle, may overwrite the passed through update that was processed directly on the cached Oracle table depending on when the propagated update and the passed through update is processed on Oracle.
In two separate TimesTen databases (DB1 and DB2), different AWT cache groups cache the same Oracle table. An update is committed on the cache table in DB1. An update is then committed on the cache table in DB2. The two cache tables reside in different TimesTen databases and cache the same Oracle table. Because the writethrough operations are asynchronous, the update from DB2 may get propagated to the Oracle database before the update from DB1, resulting in the update from DB1 overwriting the update from DB2.
Using a dynamic AWT global cache group resolves this write inconsistency. See "Global cache groups" for more information about global cache groups
The following restrictions apply when using an AWT cache group:
Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.
See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE cache table attribute.
See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON cache table attribute.
A FLUSH CACHE GROUP statement cannot be issued on the cache group.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement
The cache table definitions cannot contain a WHERE clause.
See "Using a WHERE clause" for more information about WHERE clauses in cache group definitions and operations.
A TRUNCATE TABLE statement cannot be issued on the cache tables.
The replication agent must be stopped before creating or dropping an AWT cache group.
See "Managing the replication agent" for information about how to stop and start the replication agent.
Committed updates on the TimesTen cache tables are not propagated to the cached Oracle tables unless the replication agent is running.
To create an AWT cache group, the length of the TimesTen database's absolute path name cannot exceed 248 characters.
VARCHAR2, NVARCHAR2 and VARBINARY columns defined in cache tables of an AWT cache group cannot exceed 4 MB (4,194,304 bytes).
TimesTen does not detect or resolve update conflicts that occur on Oracle. Committed updates made directly on a cached Oracle table may be overwritten by a committed update made on the TimesTen cache table when the cache table update is propagated to Oracle.
TimesTen performs deferred checking when determining whether a single SQL statement causes a constraint violation with a unique index.
For example, suppose there is a unique index on a cached Oracle table's NUMBER column, and a unique index on the same NUMBER column on the TimesTen cache table. There are five rows in the cached Oracle table and the same five rows in the cache table. The values in the NUMBER column range from 1 to 5.
An UPDATE statement is issued on the cache table to increment the value in the NUMBER column by 1 for all rows. The operation succeeds on the cache table but fails when it is propagated to the cached Oracle table.
That's because TimesTen performs the unique index constraint check at the end of the statement's execution after all the rows have been updated. Oracle, however, performs the constraint check each time after a row has been updated.
Therefore, when the row in the cache table with value 1 in the NUMBER column is changed to 2 and the update is propagated to Oracle, it will cause a unique constraint violation with the row that has the value 2 in the NUMBER column of the cached Oracle table.
A synchronous writethrough (SWT) cache group enforces a caching behavior where committed updates on the TimesTen cache tables are automatically and synchronously propagated to the cached Oracle tables as shown in Figure 4-7.
Figure 4-7 Synchronous writethrough cache group
The transaction commit on the TimesTen database occurs synchronously with the commit on the Oracle database. When an application commits a transaction in the TimesTen database, the transaction is processed in the Oracle database before it is processed in TimesTen. The application is blocked until the transaction has completed in both the Oracle and TimesTen databases.
If the transaction fails to commit in Oracle, the application must roll back the transaction in TimesTen. If the Oracle transaction commits successfully but the TimesTen transaction fails to commit, the cache tables in the SWT cache group are no longer synchronized with the cached Oracle tables. To manually resynchronize the cache tables with the cached Oracle tables, call the ttCachePropagateFlagSet built-in procedure to disable update propagation, and then reissue the transaction in the TimesTen database after correcting the problem that caused the transaction commit to fail in TimesTen. You can also resynchronize the cache tables with the cached Oracle tables by reloading the accompanying cache groups.
The following is the definition of the Oracle table that will be cached in the SWT cache group that is defined in Example 4-6. The Oracle table is owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.
CREATE TABLE product (prod_num VARCHAR2(6) NOT NULL PRIMARY KEY, name VARCHAR2(30), price NUMBER(8,2), ship_weight NUMBER(4,1))
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.product table in order for the cache manager user to create an SWT cache group that caches this table. This Oracle user must also be granted the INSERT, UPDATE and DELETE privileges on the oratt.product table for synchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.
Use the CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP statement to create an SWT cache group.
Example 4-6 Creating a SWT cache group
The following statement creates a synchronous writethrough cache group top_products that caches the oratt.product table:
CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP top_products FROM oratt.product (prod_num VARCHAR2(6) NOT NULL, name VARCHAR2(30), price NUMBER(8,2), ship_weight NUMBER(4,1), PRIMARY KEY(prod_num))
The following restrictions apply when using an SWT cache group:
Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.
See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE cache table attribute.
See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON cache table attribute.
A FLUSH CACHE GROUP statement cannot be issued on the cache group.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement
The cache table definitions cannot contain a WHERE clause.
See "Using a WHERE clause" for more information about WHERE clauses in cache group definitions and operations.
A TRUNCATE TABLE statement cannot be issued on the cache tables.
If the system managed cache groups (read-only, AWT, SWT) do not satisfy your application's requirements, you can create a user managed cache group that defines customized caching behavior. For example:
You can define a user managed cache group to automatically refresh and propagate committed updates between the Oracle and TimesTen databases by using the AUTOREFRESH cache group attribute and the PROPAGATE cache table attribute. Using both attributes enables bidirectional transmit, so that committed updates on the TimesTen cache tables or the cached Oracle tables are propagated or refreshed to each other.
You can use the LOAD CACHE GROUP, REFRESH CACHE GROUP, and FLUSH CACHE GROUP statements to manually control the transmit of committed updates between the Oracle and TimesTen databases.
You can specify the READONLY or the PROPAGATE cache table attribute on individual cache tables in a user managed cache group to define read-only or synchronous writethrough behavior at the table level.
The following are the definitions of the Oracle tables that will be cached in the user managed cache groups that are defined in Example 4-7 and Example 4-8. The Oracle tables are owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.
CREATE TABLE active_customer (custid NUMBER(6) NOT NULL PRIMARY KEY, name VARCHAR2(50), addr VARCHAR2(100), zip VARCHAR2(12), region VARCHAR2(12) DEFAULT 'Unknown') CREATE TABLE ordertab (orderid NUMBER(10) NOT NULL PRIMARY KEY, custid NUMBER(6) NOT NULL) CREATE TABLE cust_interests (custid NUMBER(6) NOT NULL, interest VARCHAR2(10) NOT NULL, PRIMARY KEY (custid, interest)) CREATE TABLE orderdetails (orderid NUMBER(10) NOT NULL, itemid NUMBER(8) NOT NULL, quantity NUMBER(4) NOT NULL, PRIMARY KEY (orderid, itemid))
Use the CREATE USERMANAGED CACHE GROUP statement to create a user managed cache group.
Example 4-7 Creating a single-table user managed cache group
The following statement creates a user managed cache group update_anywhere_customers that caches the oratt.active_customer table as shown in Figure 4-8:
CREATE USERMANAGED CACHE GROUP update_anywhere_customers AUTOREFRESH MODE INCREMENTAL INTERVAL 30 SECONDS FROM oratt.active_customer (custid NUMBER(6) NOT NULL, name VARCHAR2(50), addr VARCHAR2(100), zip VARCHAR2(12), PRIMARY KEY(custid), PROPAGATE)
Figure 4-8 Single-table user managed cache group
All columns except region are cached. Only customers whose customer ID is greater than or equal to 1001 are cached. Committed updates on the oratt.active_customer cache table or the oratt.active_customer cached Oracle table are transmitted to the corresponding table.
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.active_customer table in order for the cache manager user to create a user managed cache group that caches this table, and for automatic refresh operations to occur from the cached Oracle table to the TimesTen cache table. This Oracle user must also be granted the INSERT, UPDATE and DELETE privileges on the oratt.active_customer table for synchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.
In this example, the AUTOREFRESH cache group attribute specifies that committed updates on the oratt.active_customer cached Oracle table are automatically refreshed to the TimesTen oratt.active_customer cache table every 30 seconds. The PROPAGATE cache table attribute specifies that committed updates on the cache table are automatically and synchronously propagated to the cached Oracle table.
See "AUTOREFRESH cache group attribute" for more information about defining an automatic refresh mode, interval and state.
If you manually created the Oracle objects used to enforce the predefined behaviors of a user managed cache group that uses the AUTOREFRESH MODE INCREMENTAL cache group attribute as described in "Manually create Oracle objects used to manage caching of Oracle data", you need to set the automatic refresh state to OFF when creating the cache group.
Then you need to run the ttIsql utility's cachesqlget command to generate a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle table that is cached in the user managed cache group.
See "Manually creating Oracle objects for automatic refresh cache groups" for more information.
Example 4-8 Creating a multiple-table user managed cache group
The following statement creates a user managed cache group western_customers that caches the oratt.active_customer, oratt.ordertab, oratt.cust_interests, and oratt.orderdetails tables as shown in Figure 4-9:
CREATE USERMANAGED CACHE GROUP western_customers FROM oratt.active_customer (custid NUMBER(6) NOT NULL, name VARCHAR2(50), addr VARCHAR2(100), zip VARCHAR2(12), region VARCHAR2(12), PRIMARY KEY(custid), PROPAGATE) WHERE (oratt.active_customer.region = 'West'), oratt.ordertab (orderid NUMBER(10) NOT NULL, custid NUMBER(6) NOT NULL, PRIMARY KEY(orderid), FOREIGN KEY(custid) REFERENCES oratt.active_customer(custid), PROPAGATE), oratt.cust_interests (custid NUMBER(6) NOT NULL, interest VARCHAR2(10) NOT NULL, PRIMARY KEY(custid, interest), FOREIGN KEY(custid) REFERENCES oratt.active_customer(custid), READONLY), 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.ordertab(orderid)) WHERE (oratt.orderdetails.quantity >= 5)
Figure 4-9 Multiple-table user managed cache group
Only customers in the West region who ordered at least 5 of the same item are cached.
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.active_customer, oratt.ordertab, oratt.cust_interests, and oratt.orderdetails tables in order for the cache manager user to create a user managed cache group that caches all of these tables. This Oracle user must also be granted the INSERT, UPDATE and DELETE privileges on the oratt.active_customer and oratt.ordertab tables for synchronous writethrough operations to occur from these TimesTen cache tables to the cached Oracle tables.
Each cache table in the western_customers cache group contains a primary key. Each child table references a parent table with a foreign key constraint. The oratt.active_customer root table and the oratt.orderdetails child table each contain a WHERE clause to restrict the rows to be cached. The oratt.active_customer root table and the oratt.ordertab child table both use the PROPAGATE cache table attribute so that committed updates on these cache tables are automatically propagated to the cached Oracle tables. The oratt.cust_interests child table uses the READONLY cache table attribute so that it cannot be updated directly.
The PROPAGATE cache table attribute can be specified only for cache tables in a user managed cache group. PROPAGATE specifies that committed updates on the TimesTen cache table are automatically and synchronously propagated to the cached Oracle table such that:
The commit is first attempted in the Oracle database. If the commit fails in Oracle, the commit is not attempted in the TimesTen database and the application must roll back the TimesTen transaction. As a result, the Oracle database never misses updates committed in TimesTen.
If the commit succeeds in the Oracle database, it is then attempted in the TimesTen database. If the commit fails in TimesTen, an error message is returned from TimesTen indicating the cause of the failure. You then need to manually resynchronize the cache tables with the Oracle tables.
See "Synchronous writethrough (SWT) cache group" for information on how to resynchronize the cache tables with the Oracle tables.
By default, a cache table in a user managed cache group is created with the NOT PROPAGATE cache table attribute such that committed updates on the cache table are not propagated to the cached Oracle table.
When a cache table uses the PROPAGATE cache table attribute, you may occasionally need to commit updates on the cache table that you do not want propagated to the cached Oracle table. Use the ttCachePropagateFlagSet built-in procedure to disable automatic propagation so that committed updates on a cache table is not propagated to the cached Oracle table.
When automatic propagation is disabled, you can use a FLUSH CACHE GROUP statement to manually propagate committed inserts and updates on cache tables in a user managed cache group to the cached Oracle tables, as described in "Flushing a user managed cache group".
The following restrictions apply when using the PROPAGATE cache table attribute:
If the cache group uses the AUTOREFRESH cache group attribute, the PROPAGATE cache table attribute must be specified on all or none of its cache tables.
See "AUTOREFRESH cache group attribute" for more information about using the AUTOREFRESH cache group attribute.
If the cache group uses the AUTOREFRESH cache group attribute, the NOT PROPAGATE cache table attribute cannot be explicitly specified on any of its cache tables.
You cannot use both the PROPAGATE and READONLY cache table attributes on the same cache table.
See "READONLY cache table attribute" for more information about using the READONLY cache table attribute.
A FLUSH CACHE GROUP statement cannot be issued on the cache group unless one or more of its cache tables use neither the PROPAGATE nor the READONLY cache table attribute.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.
After the PROPAGATE cache table attribute has been specified on a cache table, you cannot change this attribute unless you drop the cache group and re-create it.
TimesTen does not perform a conflict check to prevent a propagate operation from overwriting data that was updated directly on a cached Oracle table. Therefore, updates should only be performed directly on the TimesTen cache tables or the cached Oracle tables, but not both.
In Example 4-7, the oratt.active_customer cache table uses the PROPAGATE cache table attribute.
The READONLY cache table attribute can be specified only for cache tables in a user managed cache group. READONLY specifies that the cache table cannot be updated directly. By default, a cache table in a user managed cache group is updatable.
Unlike a read-only cache group where all of its cache tables are read-only, in a user managed cache group individual cache tables can be specified as read-only using the READONLY cache table attribute.
The following restrictions apply when using the READONLY cache table attribute:
If the cache group uses the AUTOREFRESH cache group attribute, the READONLY cache table attribute must be specified on all or none of its cache tables.
See "AUTOREFRESH cache group attribute" for more information about using the AUTOREFRESH cache group attribute.
You cannot use both the READONLY and PROPAGATE cache table attributes on the same cache table.
See "PROPAGATE cache table attribute" for more information about using the PROPAGATE cache table attribute.
A FLUSH CACHE GROUP statement cannot be issued on the cache group unless one or more of its cache tables use neither the READONLY nor the PROPAGATE cache table attribute.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.
After the READONLY cache table attribute has been specified on a cache table, you cannot change this attribute unless you drop the cache group and re-create it.
In Example 4-8, the oratt.cust_interests cache table uses the READONLY cache table attribute.
The AUTOREFRESH cache group attribute can be specified when creating a read-only cache group or a user managed cache group using a CREATE CACHE GROUP statement. AUTOREFRESH specifies that committed updates on cached Oracle tables are automatically refreshed to the TimesTen cache tables. Automatic refresh is defined by default on read-only cache groups.
The following are the default settings of the automatic refresh attributes:
The automatic refresh mode is incremental.
The automatic refresh interval is 5 minutes.
The automatic refresh state is PAUSED.
TimesTen supports two automatic refresh modes:
INCREMENTAL: Committed updates on cached Oracle tables are automatically refreshed to the TimesTen cache tables based on the cache group's automatic refresh interval. Incremental automatic refresh mode uses Oracle objects to track committed updates on cached Oracle tables. See "Oracle objects used to manage a caching environment" for information on these objects.
FULL: All cache tables are automatically refreshed, based on the cache group's automatic refresh interval, by unloading all their rows and then reloading from the cached Oracle tables.
Incremental automatic refresh mode incurs some overhead to refresh the cache group for each committed update on the cached Oracle tables. There is no overhead when using full automatic refresh mode.
When using incremental automatic refresh mode, committed updates on cached Oracle tables are tracked in change log tables in the Oracle database. Under certain circumstances, it is possible for some of the change log records to be deleted from the change log table before they are automatically refreshed to the TimesTen cache tables. If this occurs, TimesTen initiates a full automatic refresh on the cache group. See "Monitoring the cache administration user's tablespace" for information on how to configure an action to take when the tablespace that the change log tables reside in becomes full.
The automatic refresh interval determines how often automatic refresh operations occur in minutes, seconds or milliseconds. Cache groups with the same automatic refresh interval are refreshed within the same transaction.
The automatic refresh state can be set to ON, PAUSED or OFF. Automatic refresh operations are scheduled by TimesTen when the cache group's automatic refresh state is ON.
When the cache group's automatic refresh state is OFF, committed updates on the cached Oracle tables are not tracked.
When the cache group's automatic refresh state is PAUSED, committed updates on the cached Oracle tables are tracked in the Oracle database, but are not automatically refreshed to the TimesTen cache tables until the state is changed to ON.
The following restrictions apply when using the AUTOREFRESH cache group attribute:
A FLUSH CACHE GROUP statement cannot be issued on the cache group.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.
A TRUNCATE TABLE statement issued on a cached Oracle table is not automatically refreshed to the TimesTen cache table. Before issuing a TRUNCATE TABLE statement on a cached Oracle table, use an ALTER CACHE GROUP statement to change the automatic refresh state of the cache group that contains the cache table to PAUSED.
See "Altering a cache group" for more information about the ALTER CACHE GROUP statement.
Then after issuing the TRUNCATE TABLE statement on the cached Oracle table, use a REFRESH CACHE GROUP statement to manually refresh the cache group.
A LOAD CACHE GROUP statement can only be issued if the cache tables are empty, unless the cache group is dynamic.
See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP and REFRESH CACHE GROUP statements.
See "Dynamic cache groups" for more information about dynamic cache groups.
The automatic refresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement on the cache group, unless the cache group is dynamic, in which case the automatic refresh state must be PAUSED or ON. The LOAD CACHE GROUP statement cannot contain a WHERE clause, unless the cache group is dynamic, in which case the WHERE clause must be followed by a COMMIT EVERY n ROWS clause.
See "Using a WHERE clause" for more information about WHERE clauses in cache group definitions and operations.
The automatic refresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement on the cache group. The REFRESH CACHE GROUP statement cannot contain a WHERE clause.
All tables and columns referenced in WHERE clauses when creating, loading or unloading the cache group must be fully qualified. For example: user_name.table_name and user_name.table_name.column_name.
To use the AUTOREFRESH cache group attribute in a user managed cache group, all of the cache tables must be specified with the PROPAGATE cache table attribute or all of the cache tables must be specified the READONLY cache table attribute.
You cannot specify the AUTOREFRESH cache group attribute in a user managed cache group that contains cache tables that explicitly use the NOT PROPAGATE cache table attribute.
LRU aging cannot be specified on the cache group, unless the cache group is dynamic where LRU aging is defined by default.
See "LRU aging" for more information about LRU aging.
In Example 4-7, the update_anywhere_customers cache group uses the AUTOREFRESH cache group attribute.
After creating an automatic refresh cache group, you can use an ALTER CACHE GROUP statement to change the cache group's automatic refresh mode, interval or state. You cannot use ALTER CACHE GROUP to instantiate automatic refresh for a cache group that was originally created without automatic refresh defined.
If you change a cache group's automatic refresh state to OFF or drop a cache group that has an automatic refresh operation in progress:
The automatic refresh operation stops if the setting of the LockWait DSN attribute is greater than 0. The ALTER CACHE GROUP or DROP CACHE GROUP statement preempts the automatic refresh operation.
The automatic refresh operation continues if the LockWait attribute is set to 0. The ALTER CACHE GROUP or DROP CACHE GROUP statement is blocked until the automatic refresh operation completes or the statement fails with a lock timeout error.
Example 4-9 Altering the automatic refresh attributes of a cache group
The following statements change the automatic refresh mode, interval and state of the customer_orders cache group:
ALTER CACHE GROUP customer_orders SET AUTOREFRESH MODE FULL ALTER CACHE GROUP customer_orders SET AUTOREFRESH INTERVAL 30 SECONDS ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE ON
If you manually created the Oracle objects used to enforce the predefined behaviors of an automatic refresh cache group as described in "Manually create Oracle objects used to manage caching of Oracle data", you need to set the automatic refresh state to OFF when creating the cache group.
Then you need to run the ttIsql utility's cachesqlget command with the INCREMENTAL_AUTOREFRESH option and the INSTALL flag as the cache manager user. This command generates a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle table that is cached in the automatic refresh cache group. These Oracle objects are used to track updates on the cached Oracle tables so that the updates can be automatically refreshed to the cache tables.
Next use SQL*Plus to run the script generated by the ttIsql utility's cachesqlget command as the sys user. Then use an ALTER CACHE GROUP statement to change the automatic refresh state of the cache group to PAUSED.
Example 4-10 Creating a read-only cache group when Oracle objects were manually created
The first statement creates a read-only cache group customer_orders with the automatic refresh state set to OFF. The SQL*Plus script generated by the ttIsql utility's cachesqlget command is saved to the /tmp/obj.sql file. The last statement changes the automatic refresh state of the cache group to PAUSED.
CREATE READONLY CACHE GROUP customer_orders
AUTOREFRESH STATE OFF
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))
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> cachesqlget INCREMENTAL_AUTOREFRESH customer_orders INSTALL /tmp/obj.sql;
Command> exit
% sqlplus sys as sysdba
Enter password: password
SQL> @/tmp/obj
SQL> exit
ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE PAUSED
A cache table definition in a CREATE CACHE GROUP statement can contain a WHERE clause to restrict the rows to cache in the TimesTen database for particular cache group types.
You can also specify a WHERE clause in a LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP or FLUSH CACHE GROUP statement for particular cache group types. Some statements, such as LOAD CACHE GROUP and REFRESH CACHE GROUP, may result in concatenated WHERE clauses in which the WHERE clause for the cache table definition is evaluated before the WHERE clause in the LOAD CACHE GROUP or REFRESH CACHE GROUP statement.
The following restrictions apply to WHERE clauses used in cache table definitions and cache group operations:
WHERE clauses can only be specified in the cache table definitions of a CREATE CACHE GROUP statement for read-only and user managed cache groups.
A WHERE clause can be specified in a LOAD CACHE GROUP statement except on an explicitly loaded automatic refresh cache group.
See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP statement.
A WHERE clause can be specified in a REFRESH CACHE GROUP statement except on an automatic refresh cache group.
See "Loading and refreshing a cache group" for more information about the REFRESH CACHE GROUP statement.
A WHERE clause can be specified in a FLUSH CACHE GROUP statement on a user managed cache group that allows committed updates on the TimesTen cache tables to be flushed to the cached Oracle tables.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP statement.
WHERE clauses in a CREATE CACHE GROUP statement cannot contain a subquery. Therefore, each WHERE clause cannot reference any table other than the one in its cache table definition. However, a WHERE clause in a LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP or FLUSH CACHE GROUP statement may contain a subquery.
A WHERE clause in a LOAD CACHE GROUP, REFRESH CACHE GROUP or FLUSH CACHE GROUP statement can reference only the root table of the cache group, unless the WHERE clause contains a subquery.
WHERE clauses in the cache table definitions are only enforced when the cache group is manually loaded or refreshed, or the cache tables are dynamically loaded. If a cache table is updatable, you can insert or update a row such that the WHERE clause in the cache table definition for that row is not satisfied.
All tables and columns referenced in WHERE clauses when creating, loading, refreshing, unloading or flushing the cache group must be fully qualified. For example: user_name.table_name and user_name.table_name.column_name.
In Example 4-8, both the oratt.active_customer and oratt.orderdetails tables contain a WHERE clause.
In a multiple-table cache group, a WHERE clause in a particular table definition should not reference any table in the cache group other than the table itself. For example, the following CREATE CACHE GROUP statements are valid:
CREATE READONLY CACHE GROUP customer_orders FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)) WHERE (oratt.customer.cust_num < 100), 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))
CREATE READONLY CACHE GROUP customer_orders 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)) WHERE (oratt.orders.cust_num < 100)
The following statement is not valid because the WHERE clause in the child table's definition references its parent table:
CREATE READONLY CACHE GROUP customer_orders 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)) WHERE (oratt.customer.cust_num < 100)
Similarly, the following statement is not valid because the WHERE clause in the parent table's definition references its child table:
CREATE READONLY CACHE GROUP customer_orders FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)) WHERE (oratt.orders.cust_num < 100), 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))
A user-defined PL/SQL function in the Oracle database can be invoked indirectly in a WHERE clause within a CREATE CACHE GROUP, LOAD CACHE GROUP, or REFRESH CACHE GROUP (for dynamic cache groups only) statement. After creating the function, create a public synonym for the function. Then grant the EXECUTE privilege on the function to PUBLIC.
For example, in the Oracle database:
CREATE OR REPLACE FUNCTION get_customer_name (c_num oratt.customer.cust_num%TYPE) RETURN VARCHAR2 IS c_name oratt.customer.name%TYPE; BEGIN SELECT name INTO c_name FROM oratt.customer WHERE cust_num = c_num; RETURN c_name; END get_customer_name CREATE PUBLIC SYNONYM retname FOR get_customer_name GRANT EXECUTE ON get_customer_name TO PUBLIC
Then in the TimesTen database, for example, you can create a cache group with a WHERE clause that references the public synonym that was created for the function:
CREATE READONLY CACHE GROUP top_customer FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)) WHERE name = retname(100)
For cache group types that allow a WHERE clause on a LOAD CACHE GROUP or REFRESH CACHE GROUP statement, you can invoke the function indirectly by referencing the public synonym that was created for the function. For example, you can use the following LOAD CACHE GROUP statement to load the AWT cache group new_customers:
LOAD CACHE GROUP new_customers WHERE name = retname(101) COMMIT EVERY 0 ROWS;
The ON DELETE CASCADE cache table attribute can be specified for cache tables in any cache group type. ON DELETE CASCADE specifies that when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign keys are also deleted.
Example 4-11 Using the ON DELETE CASCADE cache table attribute
The following statement uses the ON DELETE CASCADE cache table attribute on the child table's foreign key definition:
CREATE READONLY CACHE GROUP customer_orders 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) ON DELETE CASCADE)
All paths from a parent table to a child table must be either "delete" paths or "do not delete" paths. There cannot be some "delete" paths and some "do not delete" paths from a parent table to a child table. Specify the ON DELETE CASCADE cache table attribute for child tables on a "delete" path.
The following restrictions apply when using the ON DELETE CASCADE cache table attribute:
For AWT and SWT cache groups, and for TimesTen cache tables in user managed cache groups that use the PROPAGATE cache table attribute, foreign keys in cache tables that use the ON DELETE CASCADE cache table attribute must be a proper subset of the foreign keys in the cached Oracle tables that use the ON DELETE CASCADE attribute. ON DELETE CASCADE actions on the cached Oracle tables are applied to the TimesTen cache tables as individual deletes. ON DELETE CASCADE actions on the cache tables are applied to the cached Oracle tables as a cascaded operation.
Matching of foreign keys between the TimesTen cache tables and the cached Oracle tables is enforced only when the cache group is being created. A cascade delete operation may not work if the foreign keys on the cached Oracle tables are altered after the cache group is created.
See "CREATE CACHE GROUP" in Oracle TimesTen In-Memory Database SQL Reference for more information about the ON DELETE CASCADE cache table attribute.
The UNIQUE HASH ON cache table attribute can be specified for cache tables in any cache group type. UNIQUE HASH ON specifies that a hash index rather than a range index is created on the primary key columns of the cache table. The columns specified in the hash index must be identical to the columns in the primary key. The UNIQUE HASH ON cache table attribute is also used to specify the size of the hash index.
Example 4-12 Using the UNIQUE HASH ON cache table attribute
The following statement uses the UNIQUE HASH ON cache table attribute on the cache table's definition.
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)) UNIQUE HASH ON (cust_num) PAGES = 100
See "CREATE CACHE GROUP" in Oracle TimesTen In-Memory Database SQL Reference Guide for more information about the UNIQUE HASH ON cache table attribute.
You can cache a private synonym in an AWT, SWT or user managed cache group that does not use the AUTOREFRESH cache group attribute. The private synonym can reference a public or private synonym, but it must eventually reference a table because it is the table that is actually being cached.
The table that is directly or indirectly referenced by the cached synonym can be owned by a user other than the Oracle user with the same name as the owner of the cache group that caches the synonym. The table must reside in the same Oracle database as the synonym. The cached synonym itself must be owned by the Oracle user with the same name as the owner of the cache group that caches the synonym.
You can define an aging policy on a cache group which specifies the aging type, the aging attributes, and the aging state. TimesTen supports two aging types, least recently used (LRU) aging and time-based aging.
LRU aging deletes the least recently used or referenced data based on a specified database usage range. Time-based aging deletes data based on a specified data lifetime and frequency of the aging process. You can use both LRU and time-based aging in the same TimesTen database, but you can define only one aging policy on a particular cache group.
An aging policy is specified in the cache table definition of the root table in a CREATE CACHE GROUP statement and applies to all cache tables in the cache group because aging is performed at the cache instance level. When rows are aged out or deleted from the cache tables, the rows in the cached Oracle table are not deleted.
You can add an aging policy to a cache group by using an ALTER TABLE statement on the root table. You can change the aging policy of a cache group by using ALTER TABLE statements on the root table to drop the existing aging policy and then add a new aging policy.
This section describes cache group definitions that contain an aging policy. The topics include:
LRU aging enables you to maintain the amount of memory used in a TimesTen database within a specified threshold by deleting the least recently used data. LRU aging can be defined for all cache group types except explicitly loaded automatic refresh cache groups. LRU aging is defined by default on dynamic cache groups.
Define an LRU aging policy for a cache group by using the AGING LRU clause in the cache table definition of the CREATE CACHE GROUP statement. Aging occurs automatically if the aging state is set to its default of ON.
Example 4-13 Defining an LRU aging policy on a cache group
The following statement defines an LRU aging policy on the AWT cache group new_customers:
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)) AGING LRU ON
Use the ttAgingLRUConfig built-in procedure to set the LRU aging attributes as a user with the ADMIN privilege. The attribute settings apply to all tables in the TimesTen database that have an LRU aging policy defined and an aging state of ON.
The following are the LRU aging attributes:
LowUsageThreshold: The TimesTen database's space usage (the ratio of the permanent partition's in-use size over the partition's allocated size) at or below which LRU aging is deactivated. The default low usage threshold is .8 (80 percent).
HighUsageThreshold: The TimesTen database's space usage above which LRU aging is activated. The default high usage threshold is .9 (90 percent).
AgingCycle: The frequency in which aging occurs, in minutes. The default aging cycle is 1 minute.
Example 4-14 Setting the LRU aging attributes
The following procedure call specifies that the aging process checks every 5 minutes to see if the TimesTen database's permanent partition space usage is above 95 percent. If it is, the least recently used data is automatically aged out or deleted until the space usage is at or below 75 percent.
CALL ttAgingLRUConfig(.75, .95, 5)
If you set a new value for AgingCycle after an LRU aging policy has been defined on a cache group, the next time aging occurs is based on the current system time and the new aging cycle. For example, if the original aging cycle was 15 minutes and LRU aging occurred 10 minutes ago, aging is expected to occur again in 5 minutes. However, if you change the aging cycle to 30 minutes, aging will next occur 30 minutes from the time you call ttAgingLRUConfig with the new aging cycle setting.
If a row has been accessed or referenced since the last aging cycle, it is not eligible for LRU aging in the current aging cycle. A row is considered to be accessed or referenced if at least one of the following is true:
The row is used to build the result set of a SELECT or an INSERT .. SELECT statement.
The row has been marked to be updated or deleted in a pending transaction.
In a multiple-table cache group, if a row in a child table has been accessed or referenced since the last aging cycle, then neither the related row in the parent table nor the row in the child table is eligible for LRU aging in the current aging cycle.
The ALTER TABLE statement can be used to perform the following tasks associated with changing or defining an LRU aging policy on a cache group:
Change the aging state of a cache group by specifying the root table and using the SET AGING clause.
Add an LRU aging policy to a cache group that has no aging policy defined by specifying the root table and using the ADD AGING LRU clause.
Drop the LRU aging policy on a cache group by specifying the root table and using the DROP AGING clause.
To change the aging policy of a cache group from LRU to time-based, use an ALTER TABLE statement on the root table with the DROP AGING clause to drop the LRU aging policy. Then use an ALTER TABLE statement on the root table with the ADD AGING USE clause to add a time-based aging policy.
You must stop the cache agent before you add, alter or drop an aging policy on an automatic refresh cache group.
Time-based aging deletes data from a cache group based on the aging policy's specified data lifetime and frequency. Time-based aging can be defined for all cache group types.
Define a time-based aging policy for a cache group by using the AGING USE clause in the cache table definition of the CREATE CACHE GROUP statement. Aging occurs automatically if the aging state is set to its default of ON.
The following are the definitions of the Oracle tables that will be cached in the AWT cache group that is defined in Example 4-15. The Oracle tables are owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.
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 order_item (orditem_id NUMBER(12) NOT NULL PRIMARY KEY, ord_num NUMBER(10), prod_num VARCHAR2(6), quantity NUMBER(3))
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.orders and oratt.order_item tables in order for the cache manager user to create an AWT cache group that caches these tables. The Oracle cache administration user must be granted the INSERT, UPDATE and DELETE privileges on the oratt.orders and oratt.order_item tables for asynchronous writethrough operations to occur from the TimesTen cache tables to the cached Oracle tables.
Example 4-15 Defining a time-based aging policy on a cache group
The following statement defines a time-based aging policy on the AWT cache group ordered_items:
CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP ordered_items FROM 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)) AGING USE when_placed LIFETIME 45 DAYS CYCLE 60 MINUTES ON, oratt.order_item (orditem_id NUMBER(12) NOT NULL, ord_num NUMBER(10), prod_num VARCHAR2(6), quantity NUMBER(3), PRIMARY KEY(orditem_id), FOREIGN KEY(ord_num) REFERENCES oratt.orders(ord_num))
Cache instances that are greater than 45 days old based on the difference between the current system timestamp and the timestamp in the when_placed column of the oratt.orders table are candidates for aging. The aging process checks every 60 minutes to see if there are cache instances that can be automatically aged out or deleted from the cache tables.
The AGING USE clause requires the name of a non-nullable TIMESTAMP or DATE column used for time-based aging. We refer to this column as the timestamp column.
For each row, the value in the timestamp column stores the date and time when the row was most recently inserted or updated. The values in the timestamp column is maintained by your application. If the value of this column is unknown for particular rows and you do not want those rows to be aged out of the table, define the timestamp column with a large default value.
You can create an index on the timestamp column to optimize performance of the aging process.
You cannot add a column to an existing table and then use that column as the timestamp column because added columns cannot be defined as non-nullable. You cannot drop the timestamp column from a table that has a time-based aging policy defined.
Specify the lifetime in days, hours or minutes after the LIFETIME keyword in the AGING USE clause.
The value in the timestamp column is subtracted from the current system timestamp. The result is then truncated to the specified lifetime unit (day, hour, minute) and compared with the specified lifetime value. If the result is greater than the lifetime value, the row is a candidate for aging.
After the CYCLE keyword, specify the frequency in which aging occurs in days, hours or minutes. The default aging cycle is 5 minutes. If you specify an aging cycle of 0, aging is continuous.
The ALTER TABLE statement can be used to perform the following tasks associated with changing or defining a time-based aging policy on a cache group:
Change the aging state of a cache group by specifying the root table and using the SET AGING clause.
Change the lifetime by specifying the root table and using the SET AGING LIFETIME clause.
Change the aging cycle by specifying the root table and using the SET AGING CYCLE clause.
Add a time-based aging policy to a cache group that has no aging policy defined by specifying the root table and using the ADD AGING USE clause.
Drop the time-based aging policy on a cache group by specifying the root table and using the DROP AGING clause.
To change the aging policy of a cache group from time-based to LRU, use an ALTER TABLE statement on the root table with the DROP AGING clause to drop the time-based aging policy. Then use an ALTER TABLE statement on the root table with the ADD AGING LRU clause to add an LRU aging policy.
You must stop the cache agent before you add, alter or drop an aging policy on an automatic refresh cache group.
Use the ttAgingScheduleNow built-in procedure to manually start a one-time aging process on a specified table or on all tables that have an aging policy defined. The aging process starts as soon as you call the procedure unless there is already an aging process in progress. Otherwise the manually started aging process will begin when the aging process that is in progress has completed. After the manually started aging process has completed, the start of the table's next aging cycle is set to the time when ttAgingScheduleNow was called if the table's aging state is ON.
Example 4-16 Starting a one-time aging process
The following procedure call starts a one-time aging process on the oratt.orders table based on the time ttAgingScheduleNow is called:
CALL ttAgingScheduleNow('oratt.orders')
Rows in the oratt.orders root table that are candidates for aging will be deleted as well as related rows in the oratt.order_item child table.
When you call ttAgingScheduleNow, the aging process starts regardless of whether the table's aging state is ON or OFF. If you want to start an aging process on a particular cache group, specify the name of the cache group's root table when you call the procedure. If ttAgingScheduleNow is called with no parameters, it will start an aging process and then reset the start of the next aging cycle on all tables in the TimesTen database that have an aging policy defined.
Calling ttAgingScheduleNow does not change the aging state of any table. If a table's aging state is OFF when you call the procedure, the aging process starts, but it is not scheduled to run again after the process has completed. To continue aging a table whose aging state is OFF, you must call ttAgingScheduleNow again or change the table's aging state to ON.
To manually control aging on a cache group, disable aging on the root table by using an ALTER TABLE statement with the SET AGING OFF clause. Then call ttAgingScheduleNow to start an aging process on the cache group.
You can use time-based aging to implement a sliding window for a cache group. In a sliding window configuration, new rows are inserted into and old rows are deleted from the cache tables on a regular schedule so that the tables contain only the data that satisfies a specific time interval.
You can configure a sliding window for a cache group by using incremental automatic refresh mode and defining a time-based aging policy. The automatic refresh operation checks the timestamp of the rows in the cached Oracle tables to determine whether new data should be refreshed into the TimesTen cache tables. The system time and the time zone must be identical on the Oracle and TimesTen systems.
If the cache group does not use incremental automatic refresh mode, you can configure a sliding window by using a LOAD CACHE GROUP, REFRESH CACHE GROUP, or INSERT statement, or a dynamic load operation to bring new data into the cache tables.
Example 4-17 Defining a cache group with sliding window properties
The following statement configures a sliding window on the read-only cache group recent_shipped_orders:
CREATE READONLY CACHE GROUP recent_shipped_orders AUTOREFRESH MODE INCREMENTAL INTERVAL 1440 MINUTES STATE ON FROM 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)) AGING USE when_shipped LIFETIME 30 DAYS CYCLE 24 HOURS ON
New data in the oratt.orders cached Oracle table are automatically refreshed into the oratt.orders TimesTen cache table every 1440 minutes. Cache instances that are greater than 30 days old based on the difference between the current system timestamp and the timestamp in the when_shipped column are candidates for aging. The aging process checks every 24 hours to see if there are cache instances that can be aged out of the cache tables. Therefore, this cache group stores orders that have been shipped within the last 30 days.
The automatic refresh interval and the lifetime used for aging determine the duration that particular rows remain in the cache tables. It is possible for data to be aged out of the cache tables before it has been in the cache tables for its lifetime. For example, for a read-only cache group if the automatic refresh interval is 3 days and the lifetime is 30 days, data that is already 3 days old when it is refreshed into the cache tables will be deleted after 27 days because aging is based on the timestamp stored in the rows of the cached Oracle tables that gets loaded into the TimesTen cache tables, not when the data is refreshed into the cache tables.
The data in a dynamic cache group is loaded on demand. For example, a call center application may not want to preload all of its customers' information into TimesTen as it may be very large. Instead it can use a dynamic cache group so that a specific customer's information is loaded only when needed such as when the customer calls or logs onto the system.
Any system managed cache group type (read-only, AWT, SWT) can be defined as a dynamic cache group. A user managed cache group can be defined as a dynamic cache group unless it uses both the AUTOREFRESH cache group attribute and the PROPAGATE cache table attribute.
Use the CREATE DYNAMIC CACHE GROUP statement to create a dynamic cache group.
Example 4-18 Dynamic read-only cache group
This following statement creates a dynamic read-only cache group online_customers that caches the oratt.customer table:
CREATE DYNAMIC READONLY CACHE GROUP online_customers FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num))
With an explicitly loaded cache group, data is initially loaded into the cache tables from the cached Oracle tables using a LOAD CACHE GROUP statement. With a dynamic cache group, data may also be loaded into the cache tables using a LOAD CACHE GROUP statement. However, with a dynamic cache group, data is typically loaded automatically when its cache tables are referenced by a SELECT, INSERT, or UPDATE statement and the data is not found in the tables resulting in a cache miss. See "Dynamically loading a cache group" for more information.
With both explicitly loaded and dynamic cache groups, a LOAD CACHE GROUP statement loads into their cache tables qualified data that exists in the cached Oracle tables but not in the TimesTen cache tables. However, if a row exists in a cache table but a newer version exists in the cached Oracle table, a LOAD CACHE GROUP statement will not load that row into the cache table even if it satisfies the predicate of the statement.
By contrast, a REFRESH CACHE GROUP statement reloads qualifying rows that exists in the cache tables, effectively refreshing the content of the cache. For an explicitly loaded cache group, the rows that are refreshed are all the rows that satisfy the predicate of the REFRESH CACHE GROUP statement. However, for a dynamic cache group, the rows that are refreshed are the ones that satisfy the predicate and already exist in the cache tables. In other words, rows that end up being refreshed are the ones that have been updated or deleted in the cached Oracle table, but not the ones that have been inserted. Therefore, a refresh operation processes only the rows that are already in the cache tables. No new rows are loaded into the cache tables of a dynamic cache group as a result of a refresh.
The data in the cache instance of a dynamic read-only cache group is consistent with the data in the corresponding rows of the Oracle tables. At any instant in time, the data in a cache instance of an explicitly loaded cache group is consistent with the data in the corresponding rows of the Oracle tables, taking into consideration the state and the interval settings for autorefresh.
The data in a dynamic cache group is subject to aging as LRU aging is defined by default. You can use the ttAgingLRUConfig built-in procedure to override the default or current LRU aging attribute settings for the aging cycle and TimesTen database space usage thresholds. Alternatively, you can define time-based aging on a dynamic cache group to override LRU aging.
An Oracle table cannot be cached in more than one cache group within the same TimesTen database. However, the table can be cached in separate cache groups in different TimesTen databases. If the table is cached in separate AWT cache groups and the same cache instance is updated simultaneously on multiple TimesTen databases, there is no guarantee as to the order in which the updates will be propagated to the cached Oracle table. Also, the contents of the updated cache table will be inconsistent between the TimesTen databases.
A TimesTen cache grid prevents this problem by providing users with Oracle databases a means to horizontally scale out cache groups across multiple systems with read/write data consistency across the TimesTen databases. A cache grid is a set of TimesTen databases that collectively manage the application data.
Tables that are cached in separate cache groups within different TimesTen databases must be cached in global cache groups in order for the cache grid to manage consistency of the cache instances across the grid members when updates are committed on the cache tables of the cache group.
Only a dynamic AWT cache group can be defined as global cache group.
The following is the definition of the Oracle table that will be cached in the dynamic AWT global cache group that is defined in Example 4-19. The Oracle table is owned by the schema user oratt. The oratt user must be granted the CREATE SESSION and RESOURCE privileges before it can create tables.
CREATE TABLE subscriber (subscriberid NUMBER(10) NOT NULL PRIMARY KEY, name VARCHAR2(100) NOT NULL, minutes_balance NUMBER(5) NOT NULL, last_call_duration NUMBER(4) NOT NULL)
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT privilege on the oratt.subscriber table in order for the cache manager user to create an AWT cache group that caches this table. The Oracle cache administration user must be granted the INSERT, UPDATE and DELETE privileges on the oratt.subscriber table for asynchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.
Use the CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP statement to create a dynamic AWT global cache group.
Example 4-19 Dynamic AWT global cache group
The following statement creates a dynamic AWT global cache group subscriber_accounts that caches the oratt.subscriber table:
CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP subscriber_accounts FROM oratt.subscriber (subscriberid NUMBER(10) NOT NULL PRIMARY KEY, name VARCHAR2(100) NOT NULL, minutes_balance NUMBER(5) NOT NULL, last_call_duration NUMBER(4) NOT NULL)
When a subscriber to a prepaid telephone account makes a call, the cache instance that contains his or her account balance is loaded into the oratt.subscriber cache table of the subscriber_accounts global cache group within one of the cache grid members. The grid member that the cache instance is loaded into becomes the owner of the cache instance. Other grid members cannot access the cache instance until the owner has updated the balance of minutes and the duration of the last call, and the committed update has been propagated to the cached Oracle table.
To ensure consistency among the grid members, an Oracle table that is cached in a global cache group in a TimesTen database should not also be cached in a local cache group in another TimesTen database within the same cache grid. In addition, the Oracle table should not be cached in a global cache group in another TimesTen database within a different cache grid.
For cache tables in a dynamic AWT global cache group, a particular cache instance can only be read or updated by one grid member at a time, which we refer to as the owner of the cache instance. When the owner no longer has a pending transaction on any row of the cache instance, another grid member can take ownership by reading or updating that instance. The owner relinquishes ownership of a cache instance when that instance has been deleted from that grid member as a result of:
Aging
A DELETE statement issued on the cache table
An UNLOAD CACHE GROUP statement issued on the cache group
A request from another grid member to take ownership of that instance
The owner relinquishes ownership of all its cache instances if that grid member detaches from its cache grid.
Read data consistency between nodes of a cache grid is only guaranteed when using SERIALIZABLE isolation level on the node where cache instances are being read. When using the default READ COMMITTED isolation level, a connection on a grid node that is reading a cache instance may see a data value that has been subsequently updated to a new value by another connection in the same or a different node.
The cache tables in a dynamic AWT global cache group can be populated using any of the following operations:
Dynamic load operation
INSERT statement on the cache tables (but not an INSERT INTO .. SELECT FROM statement)
LOAD CACHE GROUP .. COMMIT EVERY n ROWS statement (can only be used if all the other grid members do not own any of the cache instances to be loaded)
See "Dynamically loading a cache group" for information about a dynamic load operation.
A grid member can take ownership of a cache instance that is currently owned by another grid member using any of the following operations:
Dynamic load operation
LOAD CACHE GROUP .. WITH ID statement
A REFRESH CACHE GROUP statement can be issued on a global cache group only if it contains a WITH ID clause.
You can set the CacheGridMsgWait DSN attribute to the maximum number of seconds that a grid member performing a dynamic load operation to acquire ownership of a cache instance will wait for the owner to relinquish the instance. The owner cannot relinquish ownership of a cache instance if it has a pending transaction on any row of the instance. The default maximum wait time is 60 seconds.
An INSERT statement issued on a cache table in a dynamic AWT global cache group will fail if the unique key value in the inserted row already exists in the cached Oracle table.
When using a LOAD CACHE GROUP .. COMMIT EVERY n ROWS statement, if any of the cache instances to be loaded within a transaction are owned by another grid member, an error is returned. The transaction is then rolled back and no cache instances are loaded within the failed transaction.
To prevent conflicts that can occur if you update the same row in a TimesTen cache table and the cached Oracle table concurrently, update only the cache table. The cached Oracle table should not be updated directly.
A TimesTen database that is a member of a cache grid can contain local and global cache groups. Only cache tables in global cache groups are guaranteed to be consistent among the grid members.
After you have created the dynamic AWT global cache group, start the replication agent on the TimesTen database as the cache manager user, if it is not already running:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> call ttRepStart; Command> exit
All standalone TimesTen databases, and the active and standby master databases of an active standby pair that contain global cache groups must attach to the cache grid that they are associated with in order to update the cache tables of the global cache groups. Attaching the databases to the grid allow the databases to become members of the grid so that cache instances in the cache tables of the global cache groups can maintain consistency among the databases within the grid.
Example 4-20 Attaching a TimesTen database to a cache grid
Attach the first standalone database to the ttGrid cache grid that it is associated with by calling the ttGridAttach built-in procedure as the cache manager user. The node number for a standalone TimesTen database is 1.
In this example, alone1 is a name that is used to uniquely identify the grid member, sys1 is the host name of the TimesTen system where the first standalone database resides, and 5001 is the TCP/IP port for the first standalone database's cache agent process:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> call ttGridAttach(1,'alone1','sys1',5001); Command> exit
Specify a unique port for the cache agent on each TimesTen database that attaches to the grid. Calling the ttGridAttach built-in procedure automatically starts the cache agent on the TimesTen database if it is not already running.
If a grid member did not detach from its grid before its cache agent process was stopped, the member will automatically reattach to its grid when its cache agent is automatically or manually restarted if its cache agent start policy is manual or always.
See "Configuring a cache grid" for more information about a cache grid.
|
 Copyright © 1996, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|