Quantcast
OracleBrains.Com header image 2

Differences Between Normal Views and Materialized Views

December 15th, 2006 by Rajender Singh · 4 Comments

This post is in response to Metalized view by MAhmad

Views

A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. It can used in much the same way as database table. When ever query is fired against it database taked the stored SQL statement and creates a table in memory and the temporary table.

As per my understanding and experience I have seen the use of VIEWS in two situtations:

  1. Hide complexity of a SQL statement
    Example: I need to design 10 report based on 3 tables. All reports are based on same set of data which drive from union between the 3 tables but different WHERE clause. Instead of writing the Union SQL in all 10 reports, I will simply create a view based on that SQL, and then use that view to create report with different WHERE clause.
  2. Security Purpose
    Example: I have table with different company data in it. I need to restrict access of user to his company data only. For this I can create a package with company variable in it (Set at the time of user choose the company) and then create view with WHERE clause as company equal to package company variable.

Materialized Views
One of the defination that I found:

Materialized Views(also known as snapshots in prior releases) is a pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.

It improves query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response.

Conclusion:

While views are stored in the database as a SQL statement and are created as needed, a materialized view is created as a table, and physically stored in the database.

Tags: Oracle Administration · Oracle Database · SQL and PL/SQL

4 responses so far ↓

  • 1 Jignesh // May 13, 2008 at 5:09 am

    good

  • 2 Simi // Feb 5, 2009 at 7:44 pm

    Good one.
    Check out this site too
    http://oraclepassport.com
    Section – Materialized Views

  • 3 alok // Mar 6, 2009 at 9:04 pm

    thank u .

  • 4 Good // Aug 1, 2010 at 12:47 pm

    Good

Leave a Comment