Materialized Views January 10, 2008Posted by Duncan in Oracle, PeopleSoft, SQL.
A materialized view (aka Snapshot) is a sort of ‘summary table’, the use of which allows you to reduce the processing time and complexity of some queries.
It’s a view where the data is defined via a SQL statement, but the resulting dataset is actually stored in the database (which can then be indexed, analysed etc). Depending upon the parameters chosen Oracle can keep the data in your view in sync with that in the tables upon which it is based. They were originally designed for replication (i.e. holding local copies of remote tables) but they’ve been adapted for performance tuning and reporting use.
When you have a large SQL query, a portion of which could be ‘pre-calculated’, it may well be possible to extract this and use it in a materialized view. This hugely benefits query performance, particularly if you can get some or all of the more complex work done in advance (and run only once too, rather than repeatedly in a nested subquery). It’s preferable to creating a table to do the same as Materialized Views can be updated when the data they are built from alters.
The syntax is as follows:
CREATE MATERIALIZED VIEW <view_name>
REFRESH COMPLETE ON DEMAND
AS (<Sql Query Goes Here>)
Now you’ve seen the considerable upside, it’s only fair I point out that there are two (fairly minor) potential downsides to Materialized views:
1) As it writes the dataset to the database it does use extra disk space, the increase in query performance should more than offset this negative.
2) If the master table performance receives a lot of inserts, performance may suffer due to the overhead of also inserting into the Materialized View.
This is – as far as I know – an Oracle-only function, however I believe Indexed views in SQL Server are similar to so non-Oracle sites aren’t excluded from this performance improving goodness.
There’s a whole lot more to Materialized Views than the high-level view given here, but a quick Google shows plenty of resources to sate those hungry for more detail.