Re: materialized views
- From: Thomas Day <tomday2@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 11 Apr 2005 10:50:32 -0400
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
