-
1. Re: Programmatically creating materialized views
markaddleman Jul 28, 2011 11:25 AM (in response to markaddleman)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?
-
2. Re: Programmatically creating materialized views
rareddy Jul 28, 2011 11:34 AM (in response to markaddleman)On the Materialized View. We will also reply to above question in little bit.
-
3. Re: Programmatically creating materialized views
rareddy Jul 28, 2011 12:12 PM (in response to markaddleman)1 of 1 people found this helpfulMark,
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.
Ramesh..
-
4. Re: Programmatically creating materialized views
markaddleman Jul 28, 2011 1:59 PM (in response to rareddy)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? -
5. Re: Programmatically creating materialized views
markaddleman Jul 28, 2011 2:03 PM (in response to rareddy)Also, can you give an example of adding constraints that trigger the IMV view to create indexes?
-
6. Re: Programmatically creating materialized views
rareddy Jul 28, 2011 2:33 PM (in response to markaddleman)Mark,
Yes,
/*+ 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);
Ramesh..
-
7. Re: Programmatically creating materialized views
rareddy Jul 28, 2011 3:18 PM (in response to markaddleman)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.
-
8. Re: Programmatically creating materialized views
markaddleman Jul 29, 2011 8:28 AM (in response to rareddy)That's what I thought; I just wanted to make sure. Thanks