1. Getting Started with Cache Guide
  2. Overview of Cache

2 Overview of Cache

Caching in TimesTen provides the ability to transfer data between an Oracle database and a TimesTen database. You can cache Oracle Database data and reduce the workload on the Oracle database. You can configure for read-only or read-write caching for Oracle database tables.

Use caching to improve the performance for your applications access to data. Caching offloads computing cycles from Oracle databases and enables responsive and scalable applications.

A cache in TimesTen:

  • Loads a subset of the Oracle database tables into a TimesTen database.

  • Can automatically maintain data synchronization between the cached data in the TimesTen database and the source data in the Oracle database.

The following sections describe the basics of cache in TimesTen:

What is Caching?

In an ideal world, direct access to authoritative data would always be fast and scalable. Most applications access data as a client where the centralized data source is in another location, which can lead to decreased performance, scalability, and availability. Alternatively, you could place an independent cache in an optimal location with a subset of relevant data cached from the centralized data source to increase your performance, scalability, and availability.

An independent cache can be added in front of a centralized data source (in our case, an Oracle database).


Description of overcache.eps follows
Description of the illustration overcache.eps

  • You can store copies of data derived from a centralized data source into one or more independent caches, which can be located closer to where data consumption occurs. This improves data access latency when used on read-only data that is accessed frequently. Lower latency means that there is a minimal delay in the transporting of data over a network connection resulting in smaller overall delay times.

  • An independent cache uses high-speed RAM for temporarily storing subsets of data. Future requests for that same data are served up faster than is possible by accessing the data from a centralized data source. The data on a centralized data source is typically stored on disk, which is also impacted by high rates of access.

  • You can store cached data for long periods of time or just temporarily.

  • You can keep the amount of data on the independent cache as small as possible while the data is still relevant to what is needed.

Since the data stored in the independent cache are copies of the data on the centralized data source, there are mechanisms to ensure that the cached data remains synchronized with the centralized data source.

There are two ways to use an independent cache:

  • As a read-only cache: Data is modified only on the centralized data source and these changes are propagated from the Oracle database to the independent cache as specified.

  • As a read-write cache: Data can be modified on either the independent cache or on the centralized data source. The data is propagated to either the independent cache or to the centralized data source as specified.

The specific actions for read-only and read-write operations within the independent cache are:

Actions for caching The specific actions for read-only operations within the independent cache The specific actions for read-write operations within the independent cache

What can be changed?

Data can be changed at any time on the centralized data source. Data is not changed on the independent cache.

Data can be changed at any time on the independent cache. While it is possible, data should not be changed on the centralized data source.

How is the data in the independent cache and the centralized data source synchronized?

Any changes made to the data on the centralized data source are propagated up from the centralized data source to the independent cache.

Any changes made to the data on the independent cache can be manually or dynamically flushed down to the centralized data source.

You use SQL statements to define what data is to be cached and how changes are propagated.

  • Read-Only operations use the SELECT SQL statement. For example: 

    SELECT * FROM sales.customers;
< 342, West, Jane Stone > 
< 663, East, Pat Reed > 
2 rows found.

  • Read-Write operations use SELECT, INSERT, UPDATE, and/or DELETE SQL statements. For example: 

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

Using TimesTen as a Cache

The TimesTen database acts as an independent cache for an Oracle database (that acts as the centralized data source). The TimesTen database caches data from an Oracle database. The TimesTen database provides support for read-only or read-write transactions in its operation as an independent cache.

Learn About Cache Objects

A TimesTen database acts as an independent cache to cache data from an Oracle database. A cache group is a TimesTen database object that enables caching of Oracle database tables.

About Cache Groups and Cache Tables

You use cache groups to define which data is to be cached in a TimesTen database from an Oracle database. A cache group can be defined to cache the whole of or just a part of a single Oracle database table or a set of related Oracle database tables. A single TimesTen database can contain multiple cache groups.

A cache group is a SQL object that encapsulates a set of one or more tables that are related through primary key and foreign key relationships. The single top-level table is called the root table and the other tables are below it in a hierarchical parent/child arrangement.

When you create a cache group, you specify one cache table for each Oracle table represented in the cache group. Each Oracle table must have a unique key (either a primary key or a unique index defined over one or more NOT NULL columns). However, you must include all columns of the unique key. You can define all or a subset of the columns of the Oracle table within its cache table when creating a cache group (subsets specified with a WHERE clause).

Decide on the following when creating a cache group that specifies one or more cache tables on the Oracle database.

  1. Identify the schema or schemas and tables on the Oracle database that you want to cache in the TimesTen database. You are not required to cache all of the tables in each schema.

  2. Create a cache group on the TimesTen database that specifies the Oracle tables that you want cached in the TimesTen database.

The figure below shows a fictional cache group called customer_orders that was created on the TimesTen database to cache a subset of the data that exists within the customers table located on the Oracle database. The customers table is a single table with no children. A subset of data in the customers table on the Oracle database is cached in the customer_orders cache group on the TimesTen database.


Description of qcachegroup1table.eps follows
Description of the illustration qcachegroup1table.eps

When caching Oracle database tables:

  • You can have a cache group that caches only a single Oracle database table. In a single-table cache group, there is a root table but no child tables.

  • You can cache multiple related Oracle database tables in the same cache group by defining a root table and one or more child tables. A cache group can contain only one root table. The root table does not reference any table with a foreign key constraint.

In a cache group with multiple tables, each child table must reference the root table or another child table in the same cache group using a foreign key constraint. Cache tables defined in a multiple-table cache group must be related to each other in a TimesTen database through foreign key constraints. However, the corresponding tables in the Oracle database do not necessarily need to be related to each other.

The tables on the Oracle database can be related:

  • Through an explicit foreign key constraint.

  • Without an explicit foreign key constraint. You may have tables on the Oracle database that are not related through a foreign key constraint. However, you want to cache the data within these separate tables on the TimesTen database. The user application could maintain a relationship between tables that is not enforced by foreign key constraints on the Oracle database.

About Cache Instances

A cache instance is defined as a single row in the cache group's root table together with the set of related rows in the child tables.

A cache instance is the smallest unit of data transferred when data is loaded from an Oracle database into a cache group in a TimesTen database.

Cache operations act on cache groups (not on individual tables) and on cache instances (not individual rows). Normal SQL operations, such as SELECT, INSERT, UPDATE and DELETE, operate directly on the cache tables and their rows.

The following graphic shows three cache tables in the customer_orders cache group. This cache group caches data from the customers, orders, and order_items tables in the Oracle database. It shows multiple related Oracle database tables in the same cache group with a root table and one or more child tables. Cache tables defined in a multiple-table cache group must be related to each other in a TimesTen database through foreign key constraints.

  • The root cache table is customers.

  • The orders and order_item cache tables are child tables. The orders cache table has a foreign key of cust_num to show the relationship with the customer cache table. The order_item cache table has a foreign key of ord_num to show the relationship with the orders cache table.

Because of the relationships between the cache tables, the cache instance identified by the row with the value 122 in the cust_num primary key column of the customers table includes:

  • The two rows with the value 122 in the cust_num column of the orders cache table (whose value in the ord_num primary key column is 44325 or 65432), and

  • The three rows with the value 44325 or 65432 in the ord_num column of the order_item cache table


Description of qcachegroupover.eps follows
Description of the illustration qcachegroupover.eps

Synchronizing Data Between TimesTen and Oracle Databases

Data is sychronized between the TimesTen database and the Oracle database through load, refresh, or propagate operations. The load and refresh operations can be performed manually or configured to occur automatically.

  • Load operations:

    • Static load: A static load operation manually loads new cache instances into cache tables on a TimesTen database from the Oracle database tables using a LOAD CACHE GROUP SQL statement. The LOAD CACHE GROUP SQL statement loads committed inserts on the Oracle database tables into the cache tables on a TimesTen database.

      A load operation is primarily used to initially populate a cache group. When you first set up a cache within a TimesTen database, some cache group types require performing a manual load of the subset of data that you want to be available in the cache. This creates a copy of the data that exists on the Oracle database. This is true for both read-only and read-write cache groups except for cache groups that use dynamic load. (You will learn about dynamic load later.)


      Description of qoverstaticload.eps follows
      Description of the illustration qoverstaticload.eps

    • Dynamic load: When the application requests data that does not exist in the TimesTen database, the TimesTen database has mechanisms to dynamically retrieve and load the new data from the Oracle database into the TimesTen database. The application dynamically requests data with a SELECT SQL statement with a WHERE clause specifying the data.


      Description of qoverdynamic.eps follows
      Description of the illustration qoverdynamic.eps

  • Refresh operations: These operations are only for read-only caching, data can be modified at any time on the Oracle database with INSERT, UPDATE or DELETE SQL statements. A refresh operation synchronizes changed data from an Oracle database to cache table in a TimesTen database. Thus, cache instances in the cache tables on a TimesTen database contain the latest copy of the data from the Oracle database tables. A refresh operation is primarily used to apply committed changes on the Oracle database tables to existing cache tables after the cache group has been initially populated. You can request a refresh manually or automatically at a specified time interval.

    • Manual refresh: For a manual refresh operation, run the REFRESH CACHE GROUP SQL statement.


      Description of qrefresh.eps follows
      Description of the illustration qrefresh.eps

    • Automatic refresh (autorefresh) operations: TimesTen provides mechanisms so that you can set an interval of time after which TimesTen automatically refreshes (known as autorefresh) all changes from the Oracle database to the TimesTen database. If you have specified autorefresh for your read-only caching, then the cached data is automatically synchronized at a specified interval of time. Normally, if you have autorefresh specified, you will not use manual refresh unless you want to refresh in between the autorefresh interval.


      Description of qoverstaticrefresh.eps follows
      Description of the illustration qoverstaticrefresh.eps

  • Automatic propagation of changes from the TimesTen database to the Oracle database: This operation is only available for read-write caching. When you change data on the cache tables on TimesTen, committed changes are automatically propagated to the cached Oracle Database tables.


    Description of qasyncwritethroughcg.eps follows
    Description of the illustration qasyncwritethroughcg.eps

Types of Cache Groups You Can Create

TimesTen provides multiple types of cache groups that cache Oracle database tables with automatic data synchronization. Select the cache group type based on your application needs. This guide covers the three most popular cache group types.

The cache group type identifies if you want to only read from or be able to read and write to the cache tables.

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

    • Static read-only cache group: With a static read-only cache group, you use manual load requests to load data. You can use manual refresh requests. However, most read-only cache groups use autorefresh operations to refresh modified data at specified time intervals. When using autorefresh, the manual refresh requests are not necessary.

    • Dynamic read-only cache group: With a dynamic read-only cache group, the application relies on data dynamically loading when data is requested with a qualified SELECT... WHERE SQL statement. Most read-only cache groups use autorefresh operations to refresh modified data at specified time intervals.

  • Read-write cache group: This guide describes the most popular read-write cache group that TimesTen offers with the static Asynchronous WriteThrough (AWT) cache group. A static AWT cache group enforces a caching behavior in which committed changes on cache tables in the TimesTen database are automatically propagated and committed to the cached tables in the Oracle database in asynchronous fashion. Using an AWT cache group is suitable for high speed data capture and online transaction processing.