Data Warehousing Community Forum
February 08, 2012, 10:30:44 pm
Home
Help
Search
Login
Register
Data Warehousing Community Forum
>
Databases
>
Oracle
>
View and Materialized View
Pages: [
1
]
Go Down
« previous
next »
Print
Author
Topic: View and Materialized View (Read 751 times)
unknown
DW Apprentice
Reputation: +8/-0
Offline
Posts: 44
View and Materialized View
«
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
OraDBA
DW Fresher
Reputation: +1/-0
Offline
Posts: 1
Re: View and Materialized View
«
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
« previous
next »
Jump to:
Please select a destination:
-----------------------------
General Category
-----------------------------
=> Introductions
=> Anouncements & News
=> Suggestions
=> General Discussion
=> Job Opportunities
-----------------------------
Data Warehousing
-----------------------------
=> Data Warehousing Concepts
=> Data Modeling
-----------------------------
Databases
-----------------------------
=> Oracle
===> PL/SQL
=> Microsoft SQL Server
=> IBM DB2
=> Teradata
-----------------------------
ETL Tools
-----------------------------
=> Informatica
===> Installation & Configuration
=> Data Stage
=> Ab Initio
=> Oracle Warehouse Builder
=> DTS/SSIS
-----------------------------
Reporting Tools
-----------------------------
=> Business Objects
=> Cognos
=> Crystal Reports
=> Hyperion
=> Brio
=> Microstrategy
-----------------------------
Operating Systems
-----------------------------
=> UNIX / Linux
===> Unix Shell Scripting
=> Windows
-----------------------------
Other Technologies & Tools
-----------------------------
=> JAVA