5 Replies Latest reply on Jan 24, 2012 12:57 PM by Steven Hawkins

    External Materialized Table Issue

    John Tan Newbie

      Hi,

       

      I was trying to use external materialized using https://community.jboss.org/wiki/AHowToGuideForMaterializationcachingViewsInTeiid , however, the materialized table is not being used. In the actual fact it is still loading internal and cache the data. (tried 7.3, 7.4 all same behavior)

        • 1. Re: External Materialized Table Issue
          Ramesh Reddy Master

          John,

           

          If you have done the steps in "External Materialization" section correctly it will work as designed. Note that apart from setting a view table is "materialized", you need to also point to source table where you can load from. I suggest you go through the steps more carefully.

           

          If not you would have to provide us with more debugging info on this to diagnose. Our internal tests work fine.

           

          Ramesh..

          • 2. Re: External Materialized Table Issue
            John Tan Newbie

            I was trying a few times based on the instruction but the view is still caching (internal). As such right now, no choice I point the view to the vw_<table> directly (without materialization) and using original source table to load the view similar to the instruction for external materialization. I might be missing something?

            • 3. Re: External Materialized Table Issue
              Steven Hawkins Master

              If you set the Materialized Table property on a materialized view, then internal materialization will not be used.  With that property set, all queries without a nocache hint will go the materialized table rather than using the view query or internal materialization.

               

              Steve

              • 4. Re: External Materialized Table Issue
                John Tan Newbie

                Basically I have a source view (Oracle) called MySourceView.

                 

                1. I imported MySourceView to source schema called SRC (source)
                2. I created external schema with VW_MyExtTable called VWE and imported this is VWE schema (source)
                3. I created a view schema caled VIEW (view)
                4. I then created a view called MyTeiidView in schema VIEW in which transformation query is basically select * from SRC.MySourceView
                5. I then clicked on MyTeiidView, chose Materialized = true, chosen Materialized table = VWE.VW_MyExtTable

                 

                After deployed, when I query VIEW.MyTeiidView (select * from VIEW.MyTeiidView) the data is from SRC.MySourceView. From server log, internal materialization is called.

                • 5. Re: External Materialized Table Issue
                  Steven Hawkins Master

                  John,

                   

                  Can you provide the log?

                   

                  Steve