Data Warehousing Community Forum
February 08, 2012, 10:30:44 pm

Pages: [1]   Go Down
  Print  
Author Topic: View and Materialized View  (Read 751 times)
unknown
DW Apprentice
**

Reputation: +8/-0
Offline Offline

Posts: 44



View Profile
« on: September 12, 2007, 09:01:11 pm »

DB Gurus,

Please differentiate between View and Materialized View.

when we go for a Materialized view and when for a view?

Help is appreciated..
Logged

Thanks
Unknown Smiley
OraDBA
DW Fresher
*

Reputation: +1/-0
Offline Offline

Posts: 1



View Profile
« Reply #1 on: October 24, 2007, 12:00:02 pm »

Hi ,
Here it goes regarding View and Materialized View in Oracle ....

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. For eg., if we are frequently issue the following query

SELECT empname,empid FROM emp;

I might well want to make this a view.To create a view use the create view command as mentioned below -

CREATE VIEW view_emp
AS
SELECT empname,empid FROM emp;

This command creates a new view called VIEW_EMP.

Important Note -
This command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view. This means that every time this view is being accessed, Oracle has to go out and execute the view and query the database data.
Let me explain it -
When you fire the following query

SELECT * FROM view_emp WHERE empid BETWEEN 500 AND 1000;

then Oracle will transform the query into -

SELECT * FROM (select empid from emp) WHERE empid BETWEEN 500 AND 1000;

Materialized View
They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).These copies are read-only. If update is required to the local copies, then the Advanced Replication feature is used.

Materialized views are created with REFRESH options -

1. The refresh method used by Oracle to refresh data in materialized view : FAST/COMPLETE/FORCE
2. Whether the view is primary key based or row-id based
3. The time and interval at which the view is to be refreshed :
    The following parameters are used while creating materialized views ->
     START WITH clause tells the database when to perform the first replication from the master          table to the local base table.It should evaluate to a future point in time.
     The NEXT clause specifies the interval between refreshes

I hope these details clear out the concepts and right usage of Views & Materialized views to you.
My suggestion for Large Production databases (including Datawarehouse) is to go for Materialized views instead of views (due to performance issues) .
Logged

OraDBA - Fixing a screwed up world, one database at a time ...
Pages: [1]   Go Up
  Print  
 
Jump to: