Teiid - Scalable Information Integration

Teiid provides several capabilities for caching data including: materialized views, result set caching, and code table caching.  These techniques can be used to significantly improve performance in many situations.

With the exception of external materialized views, the cached data is accessed through the BufferManager. For better performance the BufferManager setting should be adjusted to the memory constraints of your installation. See the Admin Guide for more on parameter tuning.

Teiid provides the capability to cache the results of specific user queries.  When the exact same user query is submitted to Teiid, the cached results will be returned.  This caching technique can yield significant performance gains if users of the system submit the same queries often.

Result set caching will cache result sets based on an exact match of the incoming SQL string and PreparedStatement parameter values if present. Caching only applies to SELECT, set query, and stored procedure execution statements; it does not apply to SELECT INTO statements, or INSERT, UPDATE, or DELETE statements.

connectAsAdmin()
clearCache("QUERY_SERVICE_RESULT_SET_CACHE")
...

Teiid provides a short cut to creating an internal materialized view table via the lookup function.

The lookup function provides a way to get a value out of a table when a key value is provided.  The function automatically caches all the values in the referenced table for the specified key/value pairs.  The cache is created the first time it is used in a particular Teiid process.  Subsequent lookups against the same table using the same key and value columns will use the cached information.

This caching solution is appropriate for integration of "reference data" with transactional or operational data.  Reference data are static data sets – typically small – which are used very frequently in most enterprise applications.  Examples are ISO country codes, state codes, and different types of financial instrument identifiers.

lookup(‘ISOCountryCodes, ‘CountryName’, ‘CountryCode’, ‘US’)

Teiid supports materialized views.  Materialized views are just like other views, but their transformations are pre-computed and stored just like a regular table. When queries are issued against the views through the Teiid Server, the cached results are used.  This saves the cost of accessing all the underlying data sources and re-computing the view transforms each time a query is executed.

Materialized views are appropriate when the underlying data does not change rapidly, or when it is acceptable to retrieve data that is "stale" within some period of time, or when it is preferred for end-user queries to access staged data rather than placing additional query load on operational sources.

SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE

SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE vg1, vg3

CALL SYS.refreshMatView(viewname=>'schema.matview', invalidate=>true)

/*+ cache(ttl:3600000) */ select t.col, t1.col from t, t1 where t.id = t1.id

/*+ cache(updatable) */ select t.col, t1.col from t, t1 where t.id = t1.id

CALL SYS.updateMatViewRow(viewname=>'schema.matview', key=>5)

A cache hint can be used to:

/*+ cache[([pref_mem] [ttl:n] [updatable])] */

...
PreparedStatement ps = connection.prepareStatement("/*+ cache */ select col from t where col2 = ?");
ps.setInt(1, 5);
ps.execute();
...

/*+ cache(pref_mem ttl:60000 */ select col from t