Creating a Static Read-Only Cache Group

Create the Oracle Database Tables to be Cached

You can create cache groups that will cache data from specified Oracle Database tables.

This example uses the fictional sales schema that has two tables in it: readtab and writetab.

Start SQL*Plus and connect to the Oracle database as the sales schema user:

% sqlplus sales/orapwd

Use SQL*Plus to create a table readtab as shown in Figure 12-2:

SQL> CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));
Table created.

Figure 12-2 Creating an Oracle Database Table to be Cached in a Read-Only Cache Group

Description of "Figure 12-2 Creating an Oracle Database Table to be Cached in a Read-Only Cache Group"

Then, use SQL*Plus to insert some rows into the readtab table, and commit the changes:

SQL> INSERT INTO readtab VALUES (1, 'Hello');
1 row created.
SQL> INSERT INTO readtab VALUES (2, 'World');
1 row created.
SQL> COMMIT;
Commit complete.

Next, use SQL*Plus to grant the SELECT privilege on the readtab table to the cacheadmin Oracle cache administration user:

SQL> GRANT SELECT ON readtab TO cacheadmin;
Grant succeeded.

The SELECT privilege on the readtab table is required to create a read-only cache group that caches this table and to perform autorefresh operations from the cached Oracle database table to the TimesTen cache table.

See Grant Privileges to the Oracle Database Users in Oracle TimesTen In-Memory Database Cache Guide.

Start a Cache Agent for TimesTen Scaleout

The cache agent performs cache operations, such as loading a cache group and managing autorefresh. TimesTen distributes cache tasks across different cache agents (each running on different data instances), where all work for a specific autorefresh interval is assigned to a single cache agent. A cache agent can manage multiple autorefresh intervals.

On the active management instance, use the ttGridAdmin dbCacheStart command to start cache agents on all data instances in the database. After which, use the ttGridAdmin dbStatus command to show when cache agents on all data instances are started.

% ttGridAdmin dbCacheStart database1
Database database1 : Starting cache agents.

% ttGridAdmin dbStatus -element
Database database1 element level status as of Thu Dec 24 09:59:14 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened started 2020-11-23 08:37:35         
host4 instance1    2 opened started 2020-11-23 08:37:35         
host5 instance1    3 opened started 2020-11-23 08:37:35         
host6 instance1    4 opened started 2020-11-23 08:37:35         
host7 instance1    5 opened started 2020-11-23 08:37:35         
host8 instance1    6 opened started 2020-11-23 08:37:35

You can start the cache agent for a specific data instance if you specify the -instance option.

% ttGridAdmin dbCacheStart database1 -instance host4.instance1
Database database1 : Starting cache agents. 

% ttGridadmin dbStatus database1 -element
 
Database database1 element level status as of Mon Dec  7 14:52:51 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened stopped 2020-11-23 08:37:35         
host4 instance1    2 opened started 2020-11-23 08:37:35         
host5 instance1    3 opened stopped 2020-11-23 08:37:35         
host6 instance1    4 opened stopped 2020-11-23 08:37:35         
host7 instance1    5 opened stopped 2020-11-23 08:37:35         
host8 instance1    6 opened stopped 2020-11-23 08:37:35

See Stopping the Cache Agents for TimesTen Scaleout for how to stop the cache agent on all data instances or a single data instance. See Limiting Cache Agent Connections to the Oracle Database for performance considerations.

Create the Cache Groups

In TimesTen Scaleout, you can create static read-only cache groups with incremental autorefresh. Read-only cache groups provide for committed changes on tables in the Oracle database to be automatically refreshed to the cache tables in the TimesTen database.

You specify incremental autorefresh with AUTOREFRESH INTERVAL when you create the cache group. See Automatically Refresh Updates on the Cached Oracle Database Table.

The main difference for creating cache groups on TimesTen Scaleout is that you specify how the data is distributed across the elements of the database. The distribution scheme is specified in the DISTRIBUTE BY clause of the CREATE CACHE GROUP statement. See Distribution Schemes for Cache Groups in TimesTen Scaleout.

As the TimesTen cache administration user, the following example creates a static read-only cache group readcache that caches the Oracle database sales.readtab table using an incremental autorefresh with an autorefresh interval of 5 seconds. The distribution scheme is specified as the HASH distribution scheme. The HASH distribution scheme is the default (and so would not normally be necessary to include in the SQL statement).

To connect as the TimesTen cache administration user, start the ttIsql utility and connect to the database1 TimesTen database including the cache administration user and the wallet containing the credentials of both cache administration users. See Providing Cache Administration User Names and Passwords in an Oracle Wallet in the Oracle TimesTen In-Memory Database Security Guide.

ttIsql "DSN=database1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
 
Copyright (c) 1996, 2021, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=database1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet;
Connection successful: DSN=database1;UID=cacheadmin;
DataStore=/timesten/datastores/database1;DatabaseCharacterSet=WE8ISO8859P1;
ConnectionCharacterSet=AL32UTF8;PermSize=512;TempSize=512;Connections=100;
OracleNetServiceName=inst1;CacheAdminWallet=1;EpochInterval=1;
(Default setting AutoCommit=1)

Command> CREATE READONLY CACHE GROUP readcache
 AUTOREFRESH INTERVAL 5 SECONDS
 FROM sales.readtab
 (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32))
 DISTRIBUTE BY HASH;
Command>

The readcache cache group and its respective sales.readtab cache table, whose owners and names are identical to the cached Oracle database tables, are created in the TimesTen database.

Figure 12-3 shows that the readcache cache group caches the sales.readtab table.

Figure 12-3 Creating a Read-Only Cache Group

Description of "Figure 12-3 Creating a Read-Only Cache Group"

Use the ttIsql cachegroups command to view the definition of the readcache cache group:

Command> cachegroups;

Cache Group CACHEADMIN.READCACHE:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined

  Root Table: SALES.READTAB
  Table Type: Read Only

1 cache group found.

See Read-Only Cache Group in the Oracle TimesTen In-Memory Database Cache Guide.

Distribution Schemes for Cache Groups in TimesTen Scaleout

Distribution schemes for cache groups are specified with the DISTRIBUTE BY clause. A best practice is for all child tables to be distributed by reference.

The following are the default distribution schemes for cache groups defined within TimesTen Scaleout:

Single table cache groups default to using a HASH distribution scheme.
If a column is not specified in the DISTRIBUTE BY HASH clause, TimesTen Scaleout selects the primary key columns as the key columns of the distribution scheme. If a primary key is not defined, TimesTen Scaleout creates a hidden column as the hash key.
In multiple table cache groups, the parent cache group table can only use the HASH or DUPLICATE distribution schemes. If you define the parent cache table to use a DUPLICATE distribution scheme, the child table can only use HASH or DUPLICATE.
When you have multiple table cache groups, the parent cache table defaults to using the HASH distribution scheme and all child cache tables default to using the REFERENCE distribution scheme. When you have multiple table cache groups and you set the parent cache table to using the DUPLICATE distribution scheme and do not set the distribution scheme of the child tables, then all child cache tables in this case default to using the HASH distribution scheme.
For child tables, if the foreign key is identical to the parent table primary key, the distribution scheme is changed to the HASH distribution scheme as an optimization.

The following example shows a multiple table cache group where the parent table is distributed using a hash distribution scheme and the child table uses a reference distribution scheme.

Define the customers and accounts tables on the Oracle database as follows:

SQL> CREATE TABLE customers
(
    cust_id            NUMBER(10,0) NOT NULL,
    first_name         VARCHAR2(30) NOT NULL,
    last_name          VARCHAR2(30) NOT NULL,
    addr1              VARCHAR2(64),
    addr2              VARCHAR2(64),
    zipcode            VARCHAR2(5),
    member_since       DATE NOT NULL,
 PRIMARY KEY (cust_id));

Table created.

SQL> CREATE TABLE accounts
(
    account_id         NUMBER(10,0) NOT NULL PRIMARY KEY,
    phone              VARCHAR2(16) NOT NULL,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2,0) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL,
    CONSTRAINT fk_customer
        FOREIGN KEY (cust_id)
            REFERENCES customers(cust_id)); 
 
Table created.

The following defines a multiple table cache group with the sales parent table and the accounts child table.

Connect> CREATE READONLY CACHE GROUP customer_orders
FROM sales.customer
 ( cust_id            NUMBER(10,0) NOT NULL,
   first_name         VARCHAR2(30) NOT NULL,
   last_name          VARCHAR2(30) NOT NULL,
   addr1              VARCHAR2(64),
   addr2              VARCHAR2(64),
   zipcode            VARCHAR2(5),
   member_since       DATE NOT NULL,
  PRIMARY KEY(cust_id))
 DISTRIBUTE BY HASH
  WHERE (sales.customer.cust_id < 100),
sales.accounts
   (account_id         NUMBER(10,0) NOT NULL,
    phone              VARCHAR2(16) NOT NULL,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2,0) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL,
  PRIMARY KEY(account_id),
  FOREIGN KEY(cust_id) REFERENCES oratt.customer(cust_id))
 DISTRIBUTE BY REFERENCE;

Once created, you cannot alter the distribution scheme of any cache table. Instead, you must drop and recreate the cache group to change the distribution scheme of a cache table.

See Defining Table Distribution Schemes for details on distribution schemes in TimesTen Scaleout. See CREATE CACHE GROUP in Oracle TimesTen In-Memory Database SQL Reference for full syntax.

Create the Oracle Database Tables to be Cached

Start a Cache Agent for TimesTen Scaleout

Create the Cache Groups

Distribution Schemes for Cache Groups in TimesTen Scaleout

Creating an Index on a Cache Table