Re: materialized views

Do some tests to see if there is a performance problem. The other basic question is, why use materialized views?

We used materialized views to maintain a database for ad-hoc queries. The base database was 80G (one table was 7G) and OLTP (average of 400 users logged on with 20 active sessions at any one time).

We used primary-key snapshots so the we wouldn't have to rebuild the snapshot database if a table had to be restored on the base database. We set up refresh groups based on the base table's volitility; tables that are basically look-up get refreshed on a 24 hour cycle, tables that change more quickly (at the center of a star scheme) are updated on a 15 minute cycle.

The performance hit for running the snapshots was less than the performance hit would be from users running ill-formed ad-hoc queries on the production database.

On the snapshot database we also had denormalized datamarts that were formed to answer specific users' (i.e., upper management) queries.

--
http://www.freelists.org/webpage/oracle-l