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