Appropriate use of materialized views

View previous topic View next topic Go down

Appropriate use of materialized views

Post  peggy_ratt on Tue May 05, 2009 10:04 am

We are experiencing some IO opportunities and the DBA (Oracle) team is suggesting that we make more use of materialized views. I am not well versed or experienced with these and am looking for some advice regarding when to use / not use. Not sure if this is the most appropriate category for this question or not.

peggy_ratt

Posts : 2
Join date : 2009-04-20
Location : Lewisville, TX

View user profile

Back to top Go down

Re: Appropriate use of materialized views

Post  Kateryna on Tue May 05, 2009 1:57 pm

I use materialized views for years in data marts. They are suitable when there are not a lot of data, the queries are simple and, of course, no SCD. In other words, when delete (truncate) and insert work faster then a complicated logic of incremental update. It's also useful when you build a prototype to provide something to report developers quickly.

When there is a complex query then a table and a refresh procedure is definitly better.
There are also materialized queries limitaions. Just not all queries will work for materialized views but work fine in insert... select.
Sometimes (Oracle 9i, Solaris) after an unsignificant change in the tables used in the query the materialized view can not be refreshed and should be recreated from scratch.

Kateryna

Posts : 5
Join date : 2009-05-05

View user profile

Back to top Go down

Re: Appropriate use of materialized views

Post  peggy_ratt on Tue May 05, 2009 5:24 pm

so, is it appropriate to say that materialized views work best with smaller tables and with queries that are static? One of our challenges is that our users have ad-hoc access to run reports and we cannot predict many of the queries that will execute.

peggy_ratt

Posts : 2
Join date : 2009-04-20
Location : Lewisville, TX

View user profile

Back to top Go down

Re: Appropriate use of materialized views

Post  Kateryna on Tue May 05, 2009 5:44 pm

Well, I mentioned using materialized views only as a basis for dimensions and fact tables instead of usual tables. There is an other implementation of materialized views in ad-hoc queries or regular reports for query rewrites. But it's a different story. If you can not predict an ad-hoc query you can not create a materialized view to rewrite it. In the opposite, if you have a "hard-coded" report you can create a materialized view for rewriting of aggregations and still have an ability to drill to details in an original transactional fact table

Kateryna

Posts : 5
Join date : 2009-05-05

View user profile

Back to top Go down

Re: Appropriate use of materialized views

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum