Appropriate use of materialized views

[peggy_ratt]

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.

[Kateryna]

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.

[peggy_ratt]

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.

[Kateryna]

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

[Sponsored content]