Examples of User Managed Cache Groups

Examples are provided for the definition of the Oracle Database tables that are to be cached in the user managed cache groups.

On the Oracle Database:

These Oracle database tables are owned by the schema user sales.

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

The Oracle cache administration user must be granted the SELECT privilege on any cached tables. In this example, the table is the sales.active_customer table.

On the Oracle database as an administrator, grant the following privileges:

SQL> GRANT SELECT ON sales.active_customer TO cacheadmin;

On the TimesTen database:

Connect as the TimesTen cache administration user. Use the CREATE USERMANAGED CACHE GROUP statement to create a user managed cache group.

The following statement creates a user managed cache group update_anywhere_customers that caches the sales.active_customer table as shown in Figure 4-8:

CREATE USERMANAGED CACHE GROUP update_anywhere_customers
AUTOREFRESH MODE INCREMENTAL INTERVAL 30 SECONDS
FROM sales.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

Description of "Figure 4-8 Single-Table User Managed Cache Group"

In this example, all columns except region from the sales.active_customer table are cached in TimesTen. Since this is defined with the PROPAGATE cache table attribute, updates committed on the sales.active_customer cache table on TimesTen are transmitted to the sales.active_customer cached Oracle Database table. Since the user managed cache table is also defined with the AUTOREFRESH cache attribute, any committed changes on the sales.active_customer Oracle Database table are transmitted to the update_anywhere_customers cached table.

In this example, the AUTOREFRESH cache group attribute specifies that committed changes on the sales.active_customer cached Oracle Database table are automatically refreshed to the TimesTen sales.active_customer cache table every 30 seconds.

If you manually created the Oracle Database 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 The initCacheAdminSchema.sql Script, you need to set the autorefresh 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 Database table that is cached in the user managed cache group.

See Manually Creating Oracle Database Objects for Cache Groups with Autorefresh.

The following statement creates a multiple-table user managed cache group western_customers that caches the sales.active_customer, sales.ordertab, sales.cust_interests, and sales.orderdetails tables as shown in Figure 4-9:

CREATE USERMANAGED CACHE GROUP western_customers
FROM sales.active_customer
 (custid NUMBER(6) NOT NULL,
  name   VARCHAR2(50),
  addr   VARCHAR2(100),
  zip    VARCHAR2(12),
  region VARCHAR2(12),
  PRIMARY KEY(custid),
  PROPAGATE)
  WHERE (sales.active_customer.region = 'West'),
sales.ordertab
 (orderid NUMBER(10) NOT NULL,
  custid  NUMBER(6) NOT NULL,
  PRIMARY KEY(orderid),
  FOREIGN KEY(custid) REFERENCES sales.active_customer(custid),
  PROPAGATE),
sales.cust_interests
 (custid   NUMBER(6) NOT NULL,
  interest VARCHAR2(10) NOT NULL,
  PRIMARY KEY(custid, interest),
  FOREIGN KEY(custid) REFERENCES sales.active_customer(custid),
  READONLY),
sales.orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY(orderid, itemid),
  FOREIGN KEY(orderid) REFERENCES sales.ordertab(orderid))
  WHERE (sales.orderdetails.quantity >= 5);

Figure 4-9 Multiple-Table User Managed Cache Group

Description of "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.

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 sales.active_customer root table and the sales.orderdetails child table each contain a WHERE clause to restrict the rows to be cached. The sales.active_customer root table and the sales.ordertab child table both use the PROPAGATE Cache Table Attribute so that committed changes on these cache tables are automatically propagated to the cached Oracle Database tables. The sales.cust_interests child table uses the READONLY Cache Table Attribute so that it cannot be updated directly.