Jul 8 2008, 02:15 PM
I have a table, and an ordinary view from the table that is just selecting everything from it, "select *".
I need to create materialized view to improve the view performance.
What would be the right approach - to create another view just like the existing one, only materialized, that would select from the base table? or create materialized view that selects * from the existing view?
In other words - what's better, to have materialized view selecting from table or from ordinary view?
I actually created both, the query plan didn't show much difference in performance. Still, there might be something. Need an advice.
Jul 8 2008, 03:54 PM
In addition to that I also have another case, just like the one above, only this time the ordinary view is very complex and joins many tables.
Again, should I use the same view definition for creating materialized view, or create materialized view as "select * from complex query"?
Jul 8 2008, 04:40 PM
"I need to create materialized view to improve the view performance."
Materialized views don;t do well on views, because they are views themselves.
Also, the base tables must be of low updates. Here is how to choose a when to use materialized views:http://www.dba-oracle.com/t_finding_materi...ew_contents.htm
"should I use the same view definition for creating materialized view"