how do materialized views work

[prashanthi1]

Hi,

We are trying materialized views. We enable 'Materialized=true' in table properties and added ttl cache in select query.

When we try the odata URL like /odata/table('Data1')?$format=json, it is not fetching the data if their is any duplicate entry for the column which is the primary key, even though 'Data1' is unique row.

i would like to know if the url i fired is doing a full table scan and storing the complete result in cache and then getting data, from cache? If so we would not try the cache option on our view tables. Please help.

Thanks in advance,

Prashanthi.

[rareddy]

It should *not* issue a table scan, when it is key based access. I suggest turn on the log and see what queries are being submitted. The Log context for odata is "org.teiid.ODATA"

[shawkins]

There are a couple things here that need to be decoupled to understand what is going on.  It could help clarify if you seeing different behavior with and without materialization. 

> it is not fetching the data if their is any duplicate entry for the column which is the primary key, even though 'Data1' is unique row.

Do you mean that you are not getting an entity back when one is expected?  Again start with eliminating materialization from the picture, then once we can ensure that you are getting expected behavior you can re-introduce it.

[prashanthi1]

@Ramesh, I have checked the command log. i see 'finalRowCount=6840' and the query is is having (g0.id = g1.id) and ORDER_BY clauses.

[shawkins]

The first query to the materialized view will perform a full snapshot load of the materialization table.  Is that the expected row count for the entire view?