Help - Search - Members - Calendar
Full Version: What's better, to have materialized view select from table or from ordinary view?
Oracle DBA Forums > Oracle > Oracle Forum
Ninel
Hello,
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.

Thank you.
Ninel
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"?

Thanks
aussie_dba
"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"

yes.
Ninel
THANKS!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.