1 Reply Latest reply on Aug 12, 2015 10:34 AM by Steven Hawkins

    query on Updatable materialized view

    Rupesh Singh Newbie

      Hi all,

       

      My requirement is to load a materialized view and update it when new records are added/deleted  in source tables.

       

      As per guide Internal Materialization - Teiid 8.10 - Project Documentation Editor


      " An updatable internal materialized view may use the SYSADMIN.refreshMatViewRow procedure to update a single row in the materialized table. If the source row exists, the materialized view table row will be updated. If the source row does not exist, the correpsonding materialized row will be deleted."


      So "If the source row exists, the materialized view table row will be updated" means if new records have been added in source table, these records will be inserted into materialized view ?

      Similarly if some records have been deleted from source table, can we fire "delete mat_view where id in ( 11,12,13) ? Kindly validate my understanding and explain in detail..

       

      OR we need some custom ways ?


      Thanks

      Rupesh Singh

        • 1. Re: query on Updatable materialized view
          Steven Hawkins Master

          > OR we need some custom ways ?

           

          The refreshMatViewRow or refreshMatViewRows for Internal materialization operates over just primary keys on the view, so you need to know the primary key of what is affected. 

           

          We will be looking to consolidate the refresh logic with the SYSADMIN.updateMatView procedure that is currently for external materialization.  It is a little more flexible in that you can specify a predicate to determine what rows need updating.