Creating Index for Internal Materialized Table

[j3hntan]

Hi,

Looking around but I do not see how to create an index for internal materialized table. From the document it says index is important for internal materialized table performance. In my scenario, the source view is exposed, the view is faster than internal materialized table (if query individually through teiid). However, the joint query of course complex joint with other tables, internal materialized table performs better. I do not want to specify to developer in this scenario query this and in that scenario query that as the data is basically the same.

Additionally, how does internal materialization and indices work?

[rareddy]

John,

If you create the primary key on the view that is used as index on the mat view table. Teiid keeps a separate copy of indexes so that it can do fast searches. Also, think about turning the materialization on the joined view table rather than the source view table for this to be more effective. When you are creating the source view materialized table, you are creating replicated source table, however you will go through the join logic every time, where as when you materialize the joined view table you only incur that very first time and any queries over it will be faster.

HTH

Ramesh..

[shawkins]

Putting an index (either unique or non-unique) on the view in Designer will also create an index for the materialized view.  Only simple ordered indexes are supported.  You cannot for instance create hash, bit, or function based indexes at this time.  The internal logic will select the appropriate index to use based upon the query criteria and order by.

Steve