Cache a view without materialization ...

[ki.alam]

I'm using dynamic VDBs, and I was wondering if there was a way to cache the view's data.  From the docs it looks like materialization isn't available for dynamic VDBs, so I tried to do:

  CREATE VIEW attributes (

                                attribute varChar(100) NOT NULL,

                                src varchar(4000) NOT NULL

                        ) AS

                                /*+ cache(ttl:3600000) */ select attribute, src from master where src like 'b1x551%';

It doesn't appear to have taken.  Any other tricks on how I could induce caching with a dynamic vdb?

Thanks,

Ki

[rareddy]

Every feature is available in Dynamic VDB, except support for the XML Document model. So, yes materialization is supported. Look at providing the OPTIONS properties for view to define the Materialization.

See Internal Materialization - Teiid 8.11 (draft) - Project Documentation Editor

External Materialization - Teiid 8.11 (draft) - Project Documentation Editor

and for all available properties

DDL Metadata - Teiid 8.11 (draft) - Project Documentation Editor

Ramesh..

[markaddleman]

Related to this, is there a convenient mechanism to mediate the engine's processing of views with a translator?  I would like to insert a TeiidExecutionFactory into the view processing pipeline to enable a few use cases:

1. result set caching
2. manipulating the query before executing

[shawkins]

Mark, the approach would be to have a translator expose tables, rather than views and then handle the custom logic there.  However if anything other than simple table scans are supported, then that will complicate the handling.

[markaddleman]

Thanks

[markaddleman]

We're using Teiid to "up-lift" some legacy data sources into a relational model for OLAP-style analysis.  The sources are running a high-volume transactional system so we don't want to burden them for every pivot of the data thus some kind of materialization is helpful.  Teiid's current external materialization doesn't fit the bill because the client only needs a small fraction of the sources' data but we cannot know what the client needs are a priori.  Essentially, this is an incremental, on-demand ETL job.

I have tried a couple of different approaches  but I haven't found anything satisfying.  As you point out above, the failure is the inability to give the planner a complete view of what's going on.  For efficiency reasons, the source translator supports dependent joins and has an access pattern defined.  I haven't found any solution that doesn't end up breaking the access pattern.  I thought I would be very clever by following a pattern like this:

create view performant_source_view (

...

constraint access_pattern accesspattern (...)

) as

select t.c1, dj.c2 from t.a, /*+ makedep */ dj where t.z=dj.z;

create view incremental_etl (

...

)

as

select fake_out_planner.c1, fake_out_planner.c2

from performant_source_view, table(call insert_into_cache(a.c1, b.c2)) fake_out_planner

where (c1) not in (select (c1) from physical_cache_table

union

select c1, c2 from physical_cache_table;

The idea behind insert_into_cache stored proc is to stuff each row into the physical_cache_table then echo each row that passed into it.  The problem is that the plan changes and the access pattern for performance_source_view cannot be satisfied.

I'm not sure if this is a hopeless approach or I'm missing something fundamental.

[shawkins]

I'm not sure if this is a hopeless approach or I'm missing something fundamental.

I don't think your missing something.  Incremental caching functionality like this is currently only supported by caching procedures - which depending upon how many possible results can be obtained and how large they are could be viable.

Otherwise you'd probably want to log a feature request.

[markaddleman]

> Otherwise you'd probably want to log a feature request - See more at: https://developer.jboss.org/thread/258329#sthash.h3Zc70aS.dpuf

I have a number of feature requests that have fallen out of my latest project.  I will bring each one up on a separate thread to help avoid confusion.

[markaddleman]

I meant to also add that I'm starting these feature requests as forum discussion items.  I'll turn each one into proper a jira ticket once the discussion settles out.

[shawkins]

Thanks Mark, it looks like a good set of issues to discuss.