-
1. Re: How to refresh Transaction Materialized View to include newly inserted rows in teiid
shawkins Apr 7, 2015 8:06 PM (in response to govindarajs)There are system procedures to update matviews - https://docs.jboss.org/author/display/TEIID/System+Procedures
updateMatView is for external materialized views
refreshMatViewRow or refreshMatViewRows is for internal materialized views
-
2. Re: How to refresh Transaction Materialized View to include newly inserted rows in teiid
govindarajs Apr 8, 2015 3:07 AM (in response to shawkins)Thanks for your response Steven ,
We only tried refreshMatViewRow command and not the refreshMatView command as it refreshes the complete view and consumes nearly 3 minutes of time.
But we were not successful with this approach because, currently we are using Teiid 8.7 in jboss EAP 6.1 and Teiid designer 8.5 and I understand, the Teiid designer 8.5 does not support the cache hint ([TEIIDDES-2383] Transformation editor removes cache hint in SQL - JBoss Issue Tracker). So we cannot set update (UPDATABLE) command in the view transformation layer to refresh a single row of the internal materialized view.
So question is,
1. Do refreshMatViewRow(s) command update the indexed view for an existing row only for an update done on the source or will it work also for an insert done to the source, like say will insert this new row into the indexed view?
2. So the primary key defined in the indexed view, should it be also a primary key in the source tables? Because we have defined a column with default value as UUID() as a primary key for the indexed views to avoid the duplicate key issue in materialized view. So these primary keys are defined on the fly at run-time and will have no connection with the source.
Thanks
Govindaraj s.
-
3. Re: How to refresh Transaction Materialized View to include newly inserted rows in teiid
shawkins Apr 8, 2015 8:46 AM (in response to govindarajs)1 of 1 people found this helpful> 1. Do refreshMatViewRow(s) command update the indexed view for an existing row only for an update done on the source or will it work also for an insert done to the source, like say will insert this new row into the indexed view?
It is for any insert/update/delete. If the row with the given identifier has been added, it will be added to the materialization table. If it has been modified, it will be updated. And removed if it has been deleted.
> So the primary key defined in the indexed view, should it be also a primary key in the source tables? Because we have defined a column with default value as UUID() as a primary key for the indexed views to avoid the duplicate key issue in materialized view. So these primary keys are defined on the fly at run-time and will have no connection with the source.
Ideally to perform single row updates you'll want to have the query against the view via primary key break down into performant queries against the source tables. So they may not need primary keys, but could use an index depending upon their relationship with the view definition. The updateMatView procedure is more flexible in that you can update based upon something other than the primary key, which could be done for the internal materializations as well with an enhancement.