3.2. Internal Materialization

3.2.1. Use Internal Materialization

Procedure 3.1. Use Internal Materialization

  1. Build a VDB using the Teiid Designer for your usecase normally.
  2. Identify all the "Virtual Tables", that you think can use caching, then click on the table.
  3. In the Properties panel, switch the Materialized property to "true"

Important

It is important to ensure that the key/index information is defined as this will be used by the materialization process to enhance the performance of the materialized table.

3.2.2. Internal Materialization Usage Steps

The materialized option must be set for the view to be materialized. The Cache Hint, when used in the context of an internal materialized view transformation query, provides the ability to fine tune the materialized table. The caching options are also settable via extension metadata:

Table 3.1. Properties

Property Description
teiid_rel:ALLOW_MATVIEW_MANAGEMENT
Allow Teiid based management of the ttl and initial load rather than the implicit behavior
teiid_rel:MATVIEW_PREFER_MEMORY
Same as the pref_mem cache hint option
teiid_rel:MATVIEW_TTL
Same as the ttl cache hint option
teiid_rel:MATVIEW_UPDATABLE
Same as the updatable cache hint option
teiid_rel:MATVIEW_SCOPE
Same as the scope cache hint option
The pref_mem option also applies to internal materialized views. Internal table index pages already have a memory preference, so the perf_mem option indicates that the data pages should prefer memory as well.
All internal materialized view refresh and updates happen atomically. Internal materialized views support READ_COMMITTED (used also for READ_UNCOMMITED) and SERIALIZABLE (used also for REPEATABLE_READ) transaction isolation levels.
Here is a sample Dynamic VDB defining an internal materialization: 
	

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="sakila" version="1">
 
    <model name="pg">
        <source name="pg" translator-name="postgresql" connection-jndi-name="java:/sakila-ds"/>
    </model>
 
    <model name="sakila" type="VIRTUAL">
    <metadata type="DDL"><![CDATA[
        CREATE VIEW actor (
           actor_id integer,
           first_name varchar(45) NOT NULL,
           last_name varchar(45) NOT NULL,
           last_update timestamp NOT NULL
        ) OPTIONS (materialized true, "teiid_rel:MATVIEW_TTL" 120000,
               "teiid_rel:MATVIEW_PREFER_MEMORY" 'true',
               "teiid_rel:MATVIEW_UPDATABLE" 'true',
               "teiid_rel:MATVIEW_SCOPE" 'vdb')
            AS SELECT actor_id, first_name, last_name, last_update from pg."public".actor;
  
    </metadata>
    </model>
</vdb>
An internal materialized view table is initially in an invalid state (there is no data). If teiid_rel:ALLOW_MATVIEW_MANAGEMENT is not specified, the first user query will trigger an implicit loading of the data. All other queries against the materialized view will block until the load completes. In some situations administrators may wish to better control when the cache is loaded with a call to SYSADMIN.refreshMatView. The initial load may itself trigger the initial load of dependent materialized views. After the initial load user queries against the materialized view table will only block if it is in an invalid state. The valid state may also be controlled through the SYSADMIN.refreshMatView procedure.
Invalidating Refresh: CALL SYSADMIN.refreshMatView(viewname=>'schema.matview', invalidate=>true)
matview will be refreshed and user queries will block until the refresh is complete (or fails).
While the initial load may trigger a transitive loading of dependent materialized views, subsequent refreshes performed with refreshMatView will use dependent materialized view tables if they exist. Only one load may occur at a time. If a load is already in progress when the SYSADMIN.refreshMatView procedure is called, it will return -1 immediately rather than preempting the current load.
Using TTL Snapshot Refresh
The Cache Hint may be used to automatically trigger a full snapshot refresh after a specified time to live (ttl). The ttl starts from the time the table is finished loading. The refresh is equivalent to CALL SYSADMIN.refreshMatView('view name', *), where the invalidation behavior is determined by the vdb property lazy-invalidate.
By default ttl refreshes are invalidating, which will cause other user queries to block while loading. That is once the ttl has expired, the next access will be required to refresh the materialized table in a blocking manner. If you would rather that the ttl is enforced lazily, such that the refresh task is performed asynchronously with the current contents not replaced until the refresh completes, set the vdb property lazy-invalidate=true. 
Auto-refresh Transformation Query
/*+ cache(ttl:3600000) */ select t.col, t1.col from t, t1 where t.id = t1.id
The resulting materialized view will be reloaded every hour (3600000 milliseconds).
Limitations
The automatic ttl refresh may not be suitable for complex loading scenarios as nested materialized views will be used by the refresh query.
The non-managed ttl refresh is performed lazily, that is it is only trigger by using the table after the ttl has expired. For infrequently used tables with long load times, this means that data may be used well past the intended ttl.
Updatable
In advanced use-cases the cache hint may also be used to mark an internal materialized view as updatable. An updatable internal materialized view may use the SYSADMIN.refreshMatViewRow procedure to update a single row in the materialized table. If the source row exists, the materialized view table row will be updated. If the source row does not exist, the correpsonding materialized row will be deleted. To be updatable the materialized view must have a single column primary key. Composite keys are not yet supported by SYSADMIN.refreshMatViewRow.
Transformation Query: 
/*+ cache(updatable) */ select t.col, t1.col from t, t1 where t.id = t1.id
Update SQL: 
CALL SYSADMIN.refreshMatViewRow(viewname=>'schema.matview', key=>5)
Given that the schema.matview defines an integer column col as its primary key, the update will check the live source(s) for the row values.
The update query will not use dependent materialized view tables, so care should be taken to ensure that getting a single row from this transformation query performs well. See the Reference Guide for information on controlling dependent joins, which may be applicable to increasing the performance of retrieving a single row. The refresh query does use nested caches, so this refresh method should be used with caution.
When the updatable option is not specified, accessing the materialized view table is more efficient because modifications do not need to be considered. Therefore, only specify the updatable option if row based incremental updates are needed. Even when performing row updates, full snapshot refreshes may be needed to ensure consistency.
The EventDistributor also exposes the updateMatViewRow as a lower level API for Programmatic Control - care should be taken when using this update method.
Secondary Indexes

Internal materialized view tables will automatically create non-unique indexes for each unique constraint and index defined on the materialized view. These indexes are created as non-unique even for unique constraints since the materialized table is not intended as an enforcement point for data integrity and when updatable the table may not be consistent with underlying values and thus unable to satisfy constraints. The primary key (if it exists) of the view will automatically be part of the covered columns for the index.

The secondary indexes are always created as trees - bitmap or hash indexes are not supported. Teiid's metadata for indexes is currently limited. We are not currently able to capture additional information, sort direction, additional columns to cover, etc. You may workaround some of these limitations though.
Function-based indexes are supported but they can only be specified through DDL metadata. If you are not using DDL metadata, consider adding another column to the view that projects the function expression, then place an index on that new column. Queries to the view will need to be modified as appropriate though to make use of the new column/index.
If additional covered columns are needed, they may be added to the index columns. This however is only applicable to comparable types. Adding additional columns will increase the amount of space used by the index, but may allow its usage to result in higher performance when only the covered columns are used and the main table is not consulted.

3.2.3. Internal Materialization Options

By default the results are cached for the life of virtual machine, unless you force the cache refresh through admin-console or by executing a system procedure. However, if you like to invalidate the cache after a pre-defined time add the following cache hint to the transformation SQL that makes up your view. This will set it for 5 mins (300000 milliseconds). (TTL is an abbreviation for time to live.) 
/*+ cache(ttl:300000) */ select * from Source.UpdateProduct
After doing this, deploy the VDB to the server. The next time you use the virtual table directly or indirectly by issuing a user query against this VDB, the virtual table contents will be cached. The cached contents will be used for any future queries.
There are few different ways to load or refresh the contents of the Internal Materialized tables:
  • Loading of materialized contents is automatic when the user query is executed like the one below. (It is assumed for this example that you used the cache hint mentioned above.) 
    SELECT * FROM Products.UpdateProduct
    This will retain the contents of the materialization table for 300000 milliseconds (5 minutes). After the defined cache time expires, any user query like above will see that the cache time has expired and will start a new asynchronous job to load the contents of the view into materialization cache.

    Important

    In this example, the user query started a new refresh/load job. As a default, this invalidates the current results unless the vdb property lazy-invalidate is set to true. For details about this property, see Using TTL Snapshot Refresh in Section 3.2.2, “Internal Materialization Usage Steps” If this property is set, while the load/refresh job is running, the user queries continue to return the expired results. When the refresh/reload job completes, then the old results will be flushed and new results will be used for any future user requests.
    The main idea here is not to block user queries while the refresh job is running. This is mainly due to the fact that you can not predict how long a refresh job going to run. This is "implicit" materialization loading, if your use case does not allow this kind of refresh then use below techniques for "explicit" materialization load.
  • To start a load/refresh job on internal materialized view immediately, launch the "admin-console", go to the Runtime tab, select Virtual Databases, select your VDB, click the Caching tab, Materialized Tables, select your view and click Reload.
    When you choose to invalidate, then any user queries that come in against this materialization table will be blocked until the cache contents are fully loaded. If the invalidation is not chosen then the behaviour will be the same as that seen in the first option above.
  • You can also issue SQL command "EXEC SYSADMIN.refreshMatView(viewname=>'Portfolio.UpdateTable', invalidate=>true)" from any JDBC query tool or through Admin Shell to clear the contents of materialization table and start the refresh of contents immediately. The same invalidation rules apply here as in the above.
    This third option has the advantage of allowing you to automate the refresh process via scripting it, so you can run it at frequent intervals.