[teiid-users] code tables

[Steven Hawkins]

Hello all,

With the changes to the mat view logic, code tables are now implemented as an implicit 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;

Note that we do not generally support the creation of anything but a local temporary table or the use of the CREATE IF NOT EXIST/AS clauses.  The additional clauses would be easy to add though.

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.
-Could be reported on/refreshed just like other materialized vies.

Another possibility though is to discourage the use of the lookup function in favor of creating an analogous materialized view.  The advantages of this approach are:
-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.
-More control of the cache hint - it also supports a TTL for automatic snapshot refresh.
-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.

Steve