[teiid-issues] [JBoss JIRA] Resolved: (TEIID-829) revamp code table caching

[Steve Hawkins (JIRA)]

     [ https://jira.jboss.org/browse/TEIID-829?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steve Hawkins resolved TEIID-829.
---------------------------------

    Fix Version/s: 7.1
                       (was: 7.1.1)
       Resolution: Done


Updated from a dev list posting:

With the changes to the mat view logic, code tables are now implemented as an internal materialized view.

For example, lookup('t1', 'c2', 'c1', 1) gets logically turned into the following:

CREATE MATERIALIZED VIEW IF NOT EXISTS #CODE_T1.C1.C2 (c1 <c1 type>, c2 <c2 type> PRIMARY KEY (c1)) AS /*+ cache(pref_mem) */ select c1, c2 from t1;

SELECT c2 FROM #CODE_T1.C1.C2 WHERE c1 = 1;

The performance difference with the old implementation of code tables is that:
-There is minor additional overhead in creating the subsequent select, but this command skips all normal planning/parsing paths so the cost is negligible.
-There index structure is lg(n) for key lookup, not constant time (hash based).
-The index and table pages may not be in memory, but will prefer to remain there through soft references.  For heavily used lookup functions, this should not introduce many additional disk accesses.

The advantages of this change are that:
-Since they are no longer forced to remain in memory, and are accessed through the buffer manager, there is no longer a practical need for explicit limits on code table creation.
-They will reuse the same replication logic as a regular implicit materialized view.

In the docs we will discourage the use of the lookup function in favor of creating an analogous internal materialized view.  The advantages of this approach are:
-It's relatively easy to do.  Create a view selecting the appropriate columns, designate the primary key, flip the isMaterialized flag.
-More control of the possible return columns.  Code tables will create a mat view for each key/value pair.  If there are multiple return columns it would be better to have a single mat view.
-Built-in system function/table support
-More control of the cache hint - it also supports a TTL for automatic snapshot refresh.
-The ability to use option nocache 
-There is little difference in the performance.  If a lookup is performed as an uncorrelated subquery, it will be turned into a constant prior to pushdown.  If it is performed as a correlated subquery, it will be no different that using a correlated lookup function.  Optionally it could be done as a join, in which case, the sort by the primary key has already been performed.

As of now there is no provision to flush code tables.  The old approach of across the entire server is certainly too coarse.

> revamp code table caching
> -------------------------
>
>                 Key: TEIID-829
>                 URL: https://jira.jboss.org/browse/TEIID-829
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>    Affects Versions: 7.0
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 7.1
>
>
> Code tables have several design issues:
> 1. the cache should allow for eviction and possibly expiration.
> 2. the cache should be backed by the common caching facility.
> 3. should allow for explicit scoping - at a minimum global vs. vdb, but additional may be good to allow session and request.
> 4. the implementation should be minimally blocking (in terms of both query execution and use of synchronization).

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira