Interesting While doing search I found this thread Re: TEIID does not refreshes after TTL have been passed in which it is mentioned that
the "proactive" materialized views will refresh them self anyway, even if noone uses them, and whenever you need the view, it will always be fast.
Do we have such option to automatically load materialized views with out user access ? Can any one share settings to configure this setting in Teiid Designer.
I would suspect that CHILD_VIEW3 is already once loaded before you issued the query for VIEW_TOPLEVEL thus the anomaly, otherwise if that is first time loading then it would have behaved exactly similar to other CHILD_VIEWS. The scheduler for cache refreshes is not exact up to the millisecond, it is close approximation of time.
Now, internal materialization at the end of the TTL, the cache gets invalidated not refreshed. Only when a user query comes in looking for data then it sees the invalidated flag, then refreshes the cache and serves the contents. During re-load/refresh your query will be waiting for the job to finish.
You can proactively call / schedule (using cron job, or windows scheduler) a refresh of cache, using "exec SYSADMIN.refreshMatview", which will keep the cache always fresh. If you are using latest versions of Teiid, you can also use in built scheduler to keep these caches fresh, see our documentation on ALLOW_MATVIEW_MANAGEMENT. I will also answer your related question on this here How to Set Up teiid_rel:ALLOW_MATVIEW_MANAGEMENT property in Teiid Designer based VDB