The Difference Between Views and Materialized Views

December 8, 2010

in faq, views

materialized-view

Other than the word "view" in their names and the fact that both are defined by an underlying SQL, there is little else common between Oracle views and materialized views – yet they can be a source of great confusion to beginners.

Here’s a summary of the key differences between views and materialized views, to put an end to all mix-ups. By the end of the article, you should be in a position to decide if your specific scenario needs a view, a materialized view, or neither.


1. Moment Of Execution

A view’s SQL is executed at run-time. The results are fetched from the view’s base tables when the view is queried.

A materialized view (called snapshot in older Oracle versions) is a "pre-answered" query – the query is executed when the materialized view is refreshed. Its result is stored in the database and the query only browses the result.

2. Space

A view occupies no space (other than that for its definition in the data dictionary).

A materialized view occupies space. It exists in the same way as a table: it sits on a disk and could be indexed or partitioned.

3. Freshness of Output

A view’s output is built on the fly; it shows real-time data from the base tables being queried.

A materialized view does not reflect real-time data. The data is only as up to date as the last time the materialized view was refreshed.

4. Where To Use

A view is best used when:

  • You want to hide the implementation details of a complex query
  • You want to restrict access to a set of rows/columns in the base tables

A materialized view is best used when:

  • You have a really big table and people do frequent aggregates on it, and you want fast response
  • You don’t mind the result being a little out of date, or your application data has more queries than updates (as in a BI/data warehousing system)

Caution!

Are you creating a materialized view to avoid the pain of tuning a query? Don’t do it! A materialized view brings with it the overhead of maintaining extra DB objects that need regular refresh besides giving you out-of-date data, all for something that might have been fixed by writing better code.

Whether your query runs directly on tables, on views or via materialized views, it must be the most efficient query possible.

Materialized views are NOT a quick fix for bad code.

{ 13 comments… read them below or add one }

Siva June 9, 2011 at 1:10 am

Good comparision for a quick outline….Really helpful….Great work

Venkatesh July 21, 2011 at 4:01 am

A very good, detailed comparison..It really helps to understand..Appreciate It..Keep Going

oratabler July 21, 2011 at 8:18 am

Glad to be of help.

Rashi August 31, 2011 at 6:58 pm

good comparision .It’s helpful thanks.

oratabler October 2, 2011 at 10:39 pm

Thanks Rashi.

Manish December 29, 2011 at 6:58 pm

It’s very helpful and the way write it’s very interesting.
Please I wanna know this type of knowledge sharing in Data Warehousing and Informatica.
Please help me.

John Nigesh January 31, 2012 at 6:23 pm

Really awesome

John Nigesh January 31, 2012 at 6:24 pm

Really awesome helpfull great job keept it up.

mohan February 2, 2012 at 3:24 pm

Really super blog, as a learner i am learning a lot here. I want you people to cover all the topics in pl/sql.

Saurabh March 21, 2012 at 10:28 am

Article is well refined & precise. Thanks for sharing such wonderful knowledge. Keep sharing……

Kani January 10, 2013 at 5:41 pm

Very useful comparison. keep posting….

Rahinur Rahaman February 12, 2013 at 12:36 pm

Great comparison between View and Materialized Views(Snapshoots). It gives me better idea ever before.

SANKEERTH February 15, 2013 at 4:27 pm

It’s very helpful to me……..Thank u very much

Leave a Comment

Previous post:

Next post: