Materialized Views January 10, 2008
Posted by Duncan in Oracle, PeopleSoft, SQL.trackback
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>
BUILD IMMEDIATE
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.
Comments
Sorry comments are closed for this entry
The PeopleSoft Tipster Blog 
[…] 8i. Back then it was called Snapshot Replication. For a quick overview of materialized views read Duncan Davies’ post. To set up materialized view replication is rather simple. But it takes a few […]
Hi there,
Nice Startup for the Materialized view Definition. I am also looking for this kind of Definition that the materialized view will be usefull not only in Multi-Database system but also in a single Database system just to hold the dataset in the db for Complex data extraction process. Thanks for your defn.
I am working in Oracle Incentive Compensation as a technical person. My responsibilty is to extract the Sales Order Data for every month and feed into Incentive Compensation Engine. For that I had developed a PL/SQL program to extract the data and dump into the staging table. Incentive Engine will pick up the data from there and calculate commission for the salesperson.
But now the problem is there are about more than 6 million sales orders for every month. PL/SQL program option seems like take more time to process all sales order to put in staging table. I am looking for the differernt option. Can i go for Materialized view option? construct a materialized on a sql query which will extract data from sales orders for every day (MV Refresh) and incentive will pick up the data from materialized view instead of the staging table populated by PL/SQL program at the month end.
Any help will be appreciated