1. Cache Guide
  2. Cache Performance
  3. Improving AWT Throughput
  4. Improving AWT Throughput with Parallel Propagation to the Oracle Database
  5. Table Constraint Restrictions When Using Parallel Propagation for AWT Cache Groups

Table Constraint Restrictions When Using Parallel Propagation for AWT Cache Groups

When you use parallel propagation for AWT cache groups, you must manually enforce data consistency.

Any unique index, unique constraint, or foreign key constraint that exists on columns in the Oracle Database tables that are to be cached should also be created on the AWT cache tables within TimesTen. If you cannot create these constraints on the AWT cache tables and you have configured for parallel propagation, then TimesTen serializes any transactions with DML operations to any table with missing constraints. For example, if a unique index created on a table in the Oracle database cannot be created on the corresponding cached table in TimesTen, all transactions for this table are serialized.

TimesTen automatically checks for missing constraints on the Oracle database that are not cached on TimesTen when you issue any of the following SQL statements:

  • When you create an AWT cache group with the CREATE ASYNCHRONOUS CACHE GROUP statement

  • When you create a unique index on an AWT cache table with the CREATE UNIQUE INDEX statement

  • When you drop a unique index on an AWT cache table with the DROP INDEX statement

Note:

You can manually initiate a check for missing constraints with the ttCacheCheck built-in procedure. For example, TimesTen does not automatically check for missing constraints after a schema change on cached Oracle Database tables. After any schema change on the Oracle database, you should perform an manual check for missing constraints by running ttCacheCheck on the TimesTen database.

See Manually Initiate Check for Missing Constraints for an AWT Cache Group for other conditions where you should manually check for missing constraints.

If the check notes missing constraints on the cached tables, TimesTen issues warnings about each missing constraint.

For the following scenarios, the cached table is marked so that transactions that include DML operations are serialized when propagated to the Oracle database.

  • Transactions that apply DML operations to AWT cache tables that are missing unique indexes or unique constraints.

  • Missing foreign key constraints for tables within a single AWT cache group.

    • If both the referencing table and the referenced table for the foreign key relationship are in the same AWT cache group and the foreign key relationship is not defined, both tables are marked for transaction serialization.

    • If the referencing table is in an AWT cache group and the referenced table is not in an AWT cache group, the table inside the cache group is not marked for transaction serialization. Only a warning is issued to notify the user of the missing constraint.

    • If the referenced table is in an AWT cache group and the referencing table is not in an AWT cache group, the table inside the cache group is not marked for transaction serialization. Only a warning is issued to notify the user of the missing constraint.

  • Missing foreign key constraints between cache groups. When you have tables defined in separate AWT cache groups that are missing a foreign key constraint, both tables are marked for serialized transactions.

  • If a missing foreign key constraint causes a chain of foreign key constraints to be broken between two AWT cache groups, transactions for all tables within both AWT cache groups are serialized.

Note:

An Oracle Database trigger may introduce an operational dependency of which TimesTen may not be aware. In this case, you should either disable parallel propagation for the AWT cache group or do not cache the table in an AWT cache group on which the trigger is created.

The following is an example of missing constraints when creating an AWT cache group. This example creates two tables in the sales schema in the Oracle database. There is a foreign key relationship between active_customer and the ordertab tables. Because the examples use these tables for parallel propagation, an index is created on the foreign key in the ordertab table. 

SQL> 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');
Table created.
 
SQL> CREATE TABLE ordertab
       (orderid NUMBER(10) NOT NULL PRIMARY KEY,
        custid NUMBER(6) NOT NULL);
Table created.
 
SQL> ALTER TABLE ordertab 
      ADD CONSTRAINT cust_fk 
       FOREIGN KEY (custid) REFERENCES active_customer(custid);
Table altered.

SQL> CREATE INDEX order_idx on ordertab (custid);

TimesTen automatically checks for missing constraints when each CREATE CACHE GROUP is issued. In the following example, a single cache group is created that includes the active_customer table. Only a warning is issued since the active_customer is the referenced table and the referencing table, ordertab, is not in any AWT cache group. The active_customer table is not marked for serialized transactions. 

CREATE WRITETHROUGH CACHE GROUP update_cust
 FROM sales.active_customer
 (custid NUMBER(6) NOT NULL PRIMARY KEY,
 name VARCHAR2(50),
 addr VARCHAR2(100),
 zip VARCHAR2(12));
Warning  5297: The following Oracle foreign key constraints on AWT cache table 
SALES.ACTIVE_CUSTOMER contain cached columns that do not have corresponding 
foreign key constraints on TimesTen: SALES.CUST_FK [Outside of CG].

The following example creates two AWT cache groups on TimesTen, one that includes the active_customer table and the other includes the ordertab table. There is a missing foreign key constraint between the cache groups. Thus, a warning is issued for both tables, but only the ordertab table is marked for serial transactions since it is the referencing table that should contain the foreign key. 

CREATE WRITETHROUGH CACHE GROUP update_cust
 FROM sales.active_customer
 (custid NUMBER(6) NOT NULL PRIMARY KEY,
 name VARCHAR2(50),
 addr VARCHAR2(100),
 zip VARCHAR2(12);
Warning  5297: The following Oracle foreign key constraints on AWT cache table 
sales.update_customer contain cached columns that do not have corresponding 
foreign key constraints on TimesTen: ordertab.cust_fk [Outside of CG].

CREATE WRITETHROUGH CACHE GROUP update_orders
 FROM sales.ordertab
 (orderid NUMBER(10) NOT NULL PRIMARY KEY,
  custid NUMBER(6) NOT NULL);
Warning  5295: Propagation will be serialized on AWT cache table 
SALES.ORDERTAB because the following Oracle foreign key constraints on this 
table contain cached columns that do not have corresponding foreign key 
constraints on TimesTen: ORDERTAB.CUST_FK [Across AWT cache groups].