A related question: The documentation indicates that internal materialized views will create indexes when there are constraints on the table. Can those constraints be primary keys? Do I specify the primary key on the materialized view table object? Or the "source" table?
On the Materialized View. We will also reply to above question in little bit.
1 of 1 people found this helpful
Are you using Internal Materialized Views (IMV) or External Materialized View (EMV)?
The usage pattern you shown above
Table view = ... view.setMaterialized(true); view.setSelectTransformation(sql);
will work for IMV. Note that in the select transformation above you need to use a cache hint to cache the contents for certain duration. If you are not using the cache hint, I am not sure the behaviour is, I suspect it would be going to the source every time, as the cached contents will be invalidated on arrival. For details on cache hint check out the Caching Guide. If you still see issues, please post more details.
For EMV, you need to set the
Table view = ... view.setMaterialized(true); view.setSelectTransformation(sql); view.setMaterializedTable(mvTable); view.setMaterializedStageTable(stageTable)
in this scenario, you are saying that the contents of the mvTable are the contents of your view. The refreshing the content of the mvTable is entirely an external process controlled by you as user. Teiid does not do any checking of staleness factor. Staging table is used as temporary table during the external refresh process, so that the query engine can still serve the results from the mvTable during loading of the contents. Once the updated contents are loaded to the Staging table, then you can use table rename commands in database to move statging into mv table. See this general guide to Materialization.
Hope this helps.
Thanks, Ramesh. We are not applying a cache hint to eh select transformation query. Do you see a problem with caching it forever? (ie "
/*+ cache */")Will refreshing the materialized view still work?
Also, can you give an example of adding constraints that trigger the IMV view to create indexes?
/*+ cache */ means cache forever. You can either use TTL like /*+ cache(ttl:300000)*/ to cache 300000 milli seconds, or you can refresh any time using
call sysadmin.refreshMatView(viewname=>'view', invalidate=>true);
I am not sure I understand the question. You set the indexes on the view by
Table view = new Table... view.setIndexes(...); // primary key view.setUniqueKeys(...); // unique keys
when you materialize the table, along with the data the indexes will be created and cached.
That's what I thought; I just wanted to make sure. Thanks