3 Replies Latest reply on Apr 8, 2015 8:46 AM by Steven Hawkins

    How to refresh Transaction Materialized View to include newly inserted rows in teiid

    govindaraj s Novice

      Hi,

       

       

      I have transaction materialized view which has more than 60 thousands records, when trying to refresh, it takes more time (3 minutes) to refresh the whole materialized view, here we are using internal materialized view, Do we have any methods to include newly inserted rows alone in the materialized view without refresh the whole materialized view?

       

       

      Thanks & Regards

      Govindaraj s.

        • 1. Re: How to refresh Transaction Materialized View to include newly inserted rows in teiid
          Steven Hawkins Master

          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
            govindaraj s Novice

            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
              Steven Hawkins Master

              > 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.

              1 of 1 people found this helpful