Create Read-Only Cache Groups

7 Create Read-Only Cache Groups

Use read-only cache groups when you want to cache read-only data. You create and manage cache groups through standard SQL language in TimesTen.

What is a Read-Only Cache Group?

A read-only cache group enforces a caching behavior in which committed changes on tables in the Oracle database are automatically refreshed to the cache tables in a cache group on the TimesTen database. Using a read-only cache group is suitable for reference data that is heavily accessed by applications.

If a TimesTen database is unavailable for whatever reason, you can still change data within the Oracle database tables that are cached in a read-only cache group. When a TimesTen database returns to operation, changes that were committed on the cached Oracle database tables while the TimesTen database was unavailable are automatically refreshed to the cache tables.

Description of the illustration qreadonlycg.eps

Learn About Read-Only Cache Groups

When you create your cache group, you decide whether your read-only cache group will manually or dynamically load desired cache instances or manually or automatically refresh changes from the Oracle database.

Both static and dynamic read-only cache groups have rules and recommendations for load and refresh operations.

Read-Only Cache Group Load Operations Refresh Operations

Read-Only Cache Group	Load Operations	Refresh Operations
Static Read-Only cache group	You must perform an initial load with a manual `LOAD CACHE GROUP` SQL statement. A manual load is only used when initially populating the static read-only cache group.	After the initial population with a manual load, all changes to data on the Oracle database are loaded by incremental autorefresh operations, which run at a specified timed interval. The default interval time is 5 minutes. If there is a situation where you want to run a refresh operation in between autorefresh intervals, you can use a manual `REFRESH` SQL statement to synchronize data.
Dynamic Read-Only cache group	If data does not exist in the cache, then a dynamic load is attempted (for qualifying SQL statements). Once the cache instance is in the cache, any changes are automatically refreshed as normal. A dynamic load request only loads new cache instances that have been inserted into the cache tables on Oracle database. Thus, a dynamic load operation does not load any data if the cache instance currently exists in the cache group. Nornally, there is no manual load performed as the cache group is not initially populated with data.	When using autorefresh (the recommendation) any modifications made to the data on the Oracle database that also exists in the cache group on TimesTen is automatically refreshed (at timed intervals) to the cache group. That is, cache instances that already exist in the cache tables are only changed or deleted with refresh operations. If there is a situation where you want to run a refresh operation in between autorefresh intervals, you can use a manual `REFRESH` SQL statement to synchronize data.

Static Read-Only cache group

You must perform an initial load with a manual LOAD CACHE GROUP SQL statement.

A manual load is only used when initially populating the static read-only cache group.

After the initial population with a manual load, all changes to data on the Oracle database are loaded by incremental autorefresh operations, which run at a specified timed interval. The default interval time is 5 minutes.

If there is a situation where you want to run a refresh operation in between autorefresh intervals, you can use a manual REFRESH SQL statement to synchronize data.

Dynamic Read-Only cache group

If data does not exist in the cache, then a dynamic load is attempted (for qualifying SQL statements). Once the cache instance is in the cache, any changes are automatically refreshed as normal.

A dynamic load request only loads new cache instances that have been inserted into the cache tables on Oracle database. Thus, a dynamic load operation does not load any data if the cache instance currently exists in the cache group.

Nornally, there is no manual load performed as the cache group is not initially populated with data.

When using autorefresh (the recommendation) any modifications made to the data on the Oracle database that also exists in the cache group on TimesTen is automatically refreshed (at timed intervals) to the cache group.

That is, cache instances that already exist in the cache tables are only changed or deleted with refresh operations.

If there is a situation where you want to run a refresh operation in between autorefresh intervals, you can use a manual REFRESH SQL statement to synchronize data.

When to use static or dynamic read-only cache groups:

Static Read-Only Cache Groups with Autorefresh: Use static read-only cache groups when you want to reduce the latency for read operations using SELECT statements when caching entire tables or static subsets of these tables.
See Create a Static Read-Only Cache Group with Autorefresh
Dynamic Read-Only Cache Groups with Autorefresh: Use dynamic read-only cache groups when you are not sure whether you can identify the set of data to be cached in advance and/or whether the potential set of data to be cached can fit in the available memory.

See Create a Dynamic Read-Only Cache Group with Autorefresh

Create a Static Read-Only Cache Group with Autorefresh

Use static read-only cache groups when you want to cache read-only data where the set of data that needs to be cached can easily be identified and fits within the memory that you allocate to the cache.

To create a static read-only cache group:

Description of the illustration createcg.eps

These steps are covered in the following sections:

After creating the static read-only cache group, you can perform optional tasks to verify that the cache group is performing as expected or to drop the cache groups to start over.

Optional Task	Description
Verify Autorefresh of Static Read-Only Cache Group	Once the static read-only cache group is created and loaded, you can verify the cache group and its data.
Drop the Cache Groups in the TimesTen and Oracle Databases	If you decide that you want to create another cache group in this guide, you can drop the cache group used in this section as it is used in the other sections.

Task 1: Identify the Schema on the Oracle Database

On the Oracle database, use SQL*Plus to connect to the Oracle database as a database administrator.

Identify tables to cache on the Oracle Database.

Since the cache tables are based on the tables you want to cache in the Oracle Database, identify the Oracle Database tables that you want to be cached in the TimesTen database.

Each table should be either:
- An Oracle Database table with a primary key on non-nullable columns. The TimesTen cache table primary key must be defined on the full Oracle Database table primary key.
- An Oracle Database table with non-nullable columns upon which a unique index is defined on one or more of the non-nullable columns in the table. The TimesTen cache table primary key must be defined on all of the columns in the unique index.
For example, you decide to cache the sales.customers, and sales.orders tables on the Oracle database. Then, note that the definition of the customers and orders tables are:
```
CREATE TABLE sales.customers
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

CREATE TABLE sales.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)
 FOREIGN KEY(cust_num) REFERENCES sales.customers(cust_num));
```
Since these tables are going to be cached in a read-only cache group, grant the SELECT privilege on the customers and orders tables to the Oracle cache administration user:
```
SQL> GRANT SELECT ON sales.customers TO cacheadmin;
SQL> GRANT SELECT ON sales.orders TO cacheadmin;
```

Task 2: Start the Cache Agent

Start the ttIsql utility and connect to the cache1 DSN as the TimesTen cache administration user, including the TimesTen cache administration user and its credentials in an Oracle Wallet.

One of the most frequently used TimesTen utilities is the ttIsql utility. This is an interactive SQL utility that serves the same purpose for TimesTen as SQL*Plus does for Oracle Database.

```
% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
```
See Connect Using an Oracle Wallet with Credentials for directions on how to create an Oracle Wallet.
Start the cache agent. The cache agent is a TimesTen daemon process that manages many of the cache-related functions for a TimesTen database.
```
call ttCacheStart;
```

Task 3: Create a Static Read-Only Cache Group on the TimesTen Database

Create a static read-only cache group with autorefresh on the TimesTen database.

As the TimesTen cache administration user, create a static read-only cache group on a TimesTen database with the CREATE READONLY CACHE GROUP SQL statement. Use the unique index columns as the primary key definition.

The following example shows how the sales customers and orders tables on the Oracle database will be cached in a cache group called customer_orders.
```
% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CREATE READONLY CACHE GROUP customer_orders
AUTOREFRESH
FROM sales.customers
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  PRIMARY KEY(cust_num)),
sales.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 sales.customers(cust_num));
```
Note:

This SQL statement creates the cache group and the cache tables on the TimesTen database. Autorefresh is added by default for read-only cache groups. Autorefresh defaults to incremental autorefresh running every 5 minutes.

When you choose data types for columns in the TimesTen cache tables, consider the data types of the columns in the Oracle Database tables and choose an equivalent or compatible data type for the columns in the cache tables. See Mappings Between Oracle Database and TimesTen Data Types
Exit the ttIsql utility that is connected as the TimesTen cache administration user. Then, restart the ttIsql utility and connect to the cache1 DSN as the instance administrator.

Grant the SELECT privilege on the sales.customers and sales.orders cache tables to the TimesTen cache administration user so that this user can issue SELECT queries on these tables.
```
Command> exit;
Disconnecting...
Done.
% ttIsql cache1
Command> GRANT SELECT ON sales.customers TO cacheadmin;
Command> GRANT SELECT ON sales.orders TO cacheadmin;
Command> exit;
Disconnecting...
Done.
```

Task 4: Load Initial Data

Load the cache group.

Reconnect as the TimesTen cache administration user. Use the ttIsql cachegroups command to view the definition of the customer_orders cache group:
```
% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> cachegroups;

Cache Group CACHEADMIN.CUSTOMER_ORDERS:

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

  Root Table: SALES.CUSTOMERS
  Table Type: Read Only

  Child Table: SALES.ORDERS
  Table Type: Read Only

1 cache group found.
```
See ttIsql in the Oracle TimesTen In-Memory Database Reference.

Note that, in the output above, the state of autorefresh operations are currently Paused for all of the cache groups that you just created.
```
Autorefresh State: Paused 
```
By default, all read-only cache groups defined with incremental autorefresh start as paused (note that the default interval value is 5 minutes).
Load the cache group with a LOAD CACHE GROUP statement for the first load of the read-only cache group. The LOAD CACHE GROUP populates the cache tables (initially, the cache tables are empty) and changes the autorefresh state from PAUSED to ON.

Description of the illustration roquicks6.eps

The following example performs a LOAD CACHE GROUP statement for the first load of the customer_orders read-only cache group since the cache tables are empty. In this case, the example specifies 3 parallel threads to make the load more efficient and a commit is issued every 256 rows during the load operation.
```
LOAD CACHE GROUP customer_orders COMMIT EVERY 256 ROWS PARALLEL 3;
2 cache instances affected.
```
See LOAD CACHE GROUP in the Oracle TimesTen In-Memory Database SQL Reference.

Use the ttIsql cachegroups command again to show that the state for autorefresh state is now On for the customer_orders cache group:

Command> cachegroups;

Cache Group CACHEADMIN.CUSTOMER_ORDERS:

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

  Root Table: SALES.CUSTOMERS
  Table Type: Read Only

  Child Table: SALES.ORDERS
  Table Type: Read Only

1 cache group found.

Query the contents of sales.customers cache table.

Command> SELECT * FROM sales.customers;
122, West, Jim Johnston
663, MidWest, Pat Reed
2 rows found.

Since this is a static read-only cache group with autorefresh, all changes on the Oracle database are automatically refreshed into the cache group at the default interval. Since these are read-only cache tables in TimesTen, any DML statements run against them will fail.

Verify Autorefresh of Static Read-Only Cache Group

The following shows how changes are automatically updated to the read-only cache group.

The example in this section inserts a new row, deletes an existing row, updates an existing row in the customers cache table, and commits the changes on the Oracle database.

Description of the illustration roquicks7.eps

On the Oracle database, use SQL*Plus as the Oracle database schema user sales to insert a new row, delete an existing row, update an existing row in the Oracle database customers table, and commit the changes.
```
SQL> INSERT INTO customers VALUES (342, "West", "Jane Stone");
1 row created.
SQL> DELETE FROM customers WHERE cust_num=122;
1 row deleted.
SQL> UPDATE customers SET region="East" WHERE cust_num=663; 
1 row updated.
SQL> COMMIT;
Commit complete.
```
Since the read-only cache group was created with the default setting for autorefresh with an interval of 5 minutes, the sales.customers cache table in the customer_orders cache group is automatically refreshed after 5 minutes with the committed changes on the cached Oracle Database sales.customers table.
On the TimesTen instance as the TimesTen cache administration user, use the ttIsql utility to query the contents of the sales.customers cache table after the customer_orders cache group has been automatically refreshed with the committed changes on the cached Oracle database table:
```
Command> SELECT * FROM sales.customers;
< 342, West, Jane Stone >
< 663, East, Pat Reed >
2 rows found.
Command> exit;
Disconnecting...
Done.
```

Since this is a quick guide on how to create a static read-only cache group, see Cache Concepts and Read-Only Cache Group in the Oracle TimesTen In-Memory Database Cache Guide for a more thorough understanding of the concepts behind and the options for a static read-only cache group.

Drop the Cache Groups in the TimesTen and Oracle Databases

All of the examples in this book use the same cache groups. If you want to move on to try other cache group types in this guide, then drop the cache groups (and any cache metadata associated with those cache groups) in both the TimesTen and Oracle databases.

Use the DROP CACHE GROUP statement to drop a cache group and its cache tables. On the Oracle database, the metadata objects used to manage the caching of the associated cached tables in the Oracle database are automatically removed.

Use the ttIsql utility to connect to the cache1 DSN as the instance administrator.

Grant the DROP ANY TABLE privilege to the TimesTen cache administration user so that this user can drop the underlying cache tables when dropping cache groups.

% ttIsql cache1
Command> GRANT DROP ANY TABLE TO cacheadmin;
Command> exit;
Disconnecting...
Done.

Start the ttIsql utility and connect to the cache1 DSN as the cache administration user. Use ttIsql to drop the customer_orders read-only cache group.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> DROP CACHE GROUP customer_orders;

The customer_orders cache group and its respective cache tables sales.customers and sales.orders, are dropped from the TimesTen database. Any metadata objects created for managing the caching operations for this cache group are removed in the Oracle database.

See Dropping a Cache Group in the Oracle TimesTen In-Memory Database Cache Guide.

Create a Dynamic Read-Only Cache Group with Autorefresh

To create the dynamic read-only cache group:

The following picture shows the tasks needed when creating a cache group.

Description of the illustration createdyncg.eps

These steps are covered in the following sections:

After creating the dynamic read-only cache group, you can perform optional tasks to verify that the cache group is performing as expected or to drop the cache groups to start over.

Optional Task	Description
Verify Dynamic Load with a Dynamic Read-Only Cache Group	Once the dynamic read-only cache group is created and loaded, you can verify the cache group and its data.
Drop the Cache Groups in the TimesTen and Oracle Databases	If you decide that you want to create another cache group in this guide, you can drop the cache group used in this section as it is used in the other sections. See .

Task 1: Identify the Schema on the Oracle Database

On the Oracle database, use SQL*Plus to connect to the Oracle database as a database administrator.

Identify tables to cache on the Oracle Database.

Since the cache tables are based on the tables you want to cache in the Oracle Database, identify the Oracle Database tables that you want to be cached in the TimesTen database.

Each table should be either:
- An Oracle Database table with a primary key on non-nullable columns. The TimesTen cache table primary key must be defined on the full Oracle Database table primary key.
- An Oracle Database table with non-nullable columns upon which a unique index is defined on one or more of the non-nullable columns in the table. The TimesTen cache table primary key must be defined on all of the columns in the unique index.
For example, you decide to cache the sales customers and orders tables on the Oracle database. Then, note that the definition of the customers and orders tables are:
```
CREATE TABLE sales.customers
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

CREATE TABLE sales.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)
 FOREIGN KEY(cust_num) REFERENCES sales.customers(cust_num));
```
Since these tables are going to be cached in a read-only cache group, grant the SELECT privilege on the customers and orders tables to the Oracle cache administration user:
```
SQL> GRANT SELECT ON sales.customers TO cacheadmin;
SQL> GRANT SELECT ON sales.orders TO cacheadmin;
```

Task 2: Start the Cache Agent

Start the ttIsql utility and connect to the cache1 DSN as the TimesTen cache administration user, including the TimesTen cache administration user and its credentials in an Oracle Wallet.

One of the most frequently used TimesTen utilities is the ttIsql utility. This is an interactive SQL utility that serves the same purpose for TimesTen as SQL*Plus does for Oracle Database.

```
% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
```
See Connect Using an Oracle Wallet with Credentials for directions on how to create an Oracle Wallet.
Start the cache agent. The cache agent is a TimesTen daemon process that manages many of the cache-related functions for a TimesTen database.
```
call ttCacheStart;
```

Task 3: Create a Dynamic Read-Only Cache Group on the TimesTen Database

Create a dynamic read-only cache group with autorefresh on the TimesTen database.

Continuing as the TimesTen cache administration user, create a dynamic read-only cache group with autorefresh with the CREATE DYNAMIC READONLY CACHE GROUP SQL statement. Use the unique index columns as the primary key definition. Note that autorefresh is configured by default for read-only cache groups.
```
Command> CREATE DYNAMIC READONLY CACHE GROUP customer_orders
AUTOREFRESH
FROM sales.customers
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  PRIMARY KEY(cust_num)),
sales.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 sales.customers(cust_num));
Command> exit;
Disconnecting...
Done.
```
Note:

This SQL statement creates the cache group and the cache tables on the TimesTen database.

When you choose data types for columns in the TimesTen cache tables, consider the data types of the columns in the Oracle Database tables and choose an equivalent or compatible data type for the columns in the cache tables. See Mappings Between Oracle Database and TimesTen Data Types
Start the ttIsql utility and connect to the cache1 DSN as the instance administrator.

Grant the SELECT privilege on the sales.customers and sales.orders cache tables to the TimesTen cache administration user so that this user can issue a SELECT query on this table.
```
% ttIsql cache1
Command> GRANT SELECT ON sales.customers TO cacheadmin;
Command> GRANT SELECT ON sales.orders TO cacheadmin;
Command> exit;
Disconnecting...
Done.
```

Since this is a dynamic read-only cache group with autorefresh, all changes on the Oracle database are automatically refreshed into the cache group at the default interval. You can dynamically request data that does not exist in the cache with a qualified SQL statement. You should not run any DML statements directly against any read-only cache tables in the TimesTen database.

Verify Dynamic Load with a Dynamic Read-Only Cache Group

The following shows how changes are automatically updated to the read-only cache group.

On the Oracle database:

Use SQL*Plus as the Oracle database schema user to insert a new row, delete an existing row, update an existing row in the Oracle database customers table, and commit the changes.

SQL> INSERT INTO customers VALUES (342, "West", "Jane Stone");
1 row created.
SQL> DELETE FROM customers WHERE cust_num=122;
1 row deleted.
SQL> UPDATE customers SET region="East" WHERE cust_num=663; 
1 row updated.
SQL> COMMIT;
Commit complete.

On the TimesTen database:

Since the dynamic read-only cache group was created specifying autorefresh with the default interval of 5 minutes, the sales.customers cache table in the customer_orders cache group is automatically refreshed after 5 minutes with the committed changes on the cached Oracle Database sales.customers table.

As the TimesTen cache administration user, use the ttIsql utility to connect to the TimesTen database.
```
% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
```
Use the ttIsql cachegroups command to view the definition of the customer_orders cache group:
```
Command> cachegroups;
Cache Group CACHEADMIN.CUSTOMER_ORDERS:

  Cache Group Type: Read Only (Dynamic)
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 5 Minutes
  Autorefresh Status: ok
  Aging: LRU on

  Root Table: SALES.CUSTOMERS
  Table Type: Read Only

  Child Table: SALES.ORDERS  
  Table Type: Read Only

1 cache group found.
```
The TimesTen mechanism that captures data changes that occur in the Oracle database and uses those changes to refresh the cached data is called autorefresh. Note that, in the output above, the state of this mechanism is currently Paused for the cache group that you just created.
```
Autorefresh State: Paused 
```
By default, all read-only cache groups defined with incremental autorefresh start as paused. Perform a dynamic load request or run a LOAD CACHE GROUP statement for the first load of the read-only cache group.

A dynamic load request or a LOAD CACHE GROUP populates the cache tables appropriately (since initially, the cache tables are empty) and changes the autorefresh state from PAUSED to ON.
This example performs a dynamic load request with a qualified SQL query for customer number 342 from the sales.customers cache table. This triggers a dynamic load of that cache instance with the committed changes on the cached Oracle database table:
```
Command> SELECT * FROM sales.customers WHERE cust_num=342;
< 342, West, Jane Stone >
1 row found.
```
Note:

All changes are automatically refreshed at the autorefresh interval time.

Since this is a quick guide on how to create a dynamic read-only cache group, see Cache Concepts and Dynamic Cache Groups in the Oracle TimesTen In-Memory Database Cache Guide for a more thorough understanding of the concepts behind and the options for a dynamic read-only cache group.

Drop the Cache Groups in the TimesTen and Oracle Databases

Use the ttIsql utility to connect to the cache1 DSN as the instance administrator.

Grant the DROP ANY TABLE privilege to the TimesTen cache administration user so that this user can drop the underlying cache tables when dropping cache groups.

% ttIsql cache1
Command> GRANT DROP ANY TABLE TO cacheadmin;
Command> exit;
Disconnecting...
Done.

Start the ttIsql utility and connect to the cache1 DSN as the cache administration user. Use ttIsql to drop the customer_orders read-only cache group.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> DROP CACHE GROUP customer_orders;

See Dropping a Cache Group in the Oracle TimesTen In-Memory Database Cache Guide.