Creating a Static Read-Only Cache Group
You can create a read-only cache group.
This section creates a read-only cache group (as shown in Figure 12-1).
Figure 12-1 Single-Table Read-Only Cache Group
Description of "Figure 12-1 Single-Table Read-Only Cache Group"
Complete the following tasks to create a 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
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
orDUPLICATE
distribution schemes. If you define the parent cache table to use aDUPLICATE
distribution scheme, the child table can only useHASH
orDUPLICATE
. -
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 theREFERENCE
distribution scheme. When you have multiple table cache groups and you set the parent cache table to using theDUPLICATE
distribution scheme and do not set the distribution scheme of the child tables, then all child cache tables in this case default to using theHASH
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.