Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Materialized Views in Oracle Database

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 23-01-2010
Member
 
Join Date: May 2009
Posts: 1,187
Materialized Views in Oracle Database
  

Can somebody please give me a some tutorial guidance regarding materialized views/snapshots (preferably in Oracle) and perhaps some sample scripts too.
What are the uses of view object in oracle and how I will create it. Tell me something about this.How the materialized view is different from normal Views.

Reply With Quote
  #2  
Old 23-01-2010
Member
 
Join Date: Apr 2008
Posts: 1,948
Materialized Views in Oracle Database

The Materialized view is an object of oracle database. The Materialized views, which contains data from the remote tables that are also called as snapshots.
It is used in the term of data warehousing.

Generally we use the materialized view because the base can not be updated by unauthorized user who don't have permission to access the base table.They can only read the data of the view.They can't do any update and all.

But in the case of materialized views we take it as a physical representation of table data and authorized user make some queries and if required they can update the data. It is used to keep your data with a secure manner.
Reply With Quote
  #3  
Old 23-01-2010
Member
 
Join Date: May 2008
Posts: 2,008
Creating Materialized Views in Oracle

I am going to create a materialized view which is a physical representation of remote tables:

The following syntax will create a primary key materialized view on the emp table stored on the remote database.

SQL>CREATE MATERIALIZED VIEW comp_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_database;

Where "comp_emp_pk" is the name of primary key column and "Remote_database" is the name of the remote database to which you are accessing currently.The Fast is used to create a view log on the master table.you can do the same task to make the view log on master table in this way also :

SQL>CREATE MATERIALIZED VIEW LOG ON emp;

Last edited by Katty : 23-01-2010 at 04:11 PM.
Reply With Quote
  #4  
Old 23-01-2010
Member
 
Join Date: Apr 2008
Posts: 2,001
Difference between Materialized Views and Simple Views

View :
View is another object of oracle database.It is a set a sql statements which joins one or more tables and retrieve the data from base table according to the structure of base table and view creation. It doesn't contain the data but it kept a structure of base table and retrieve the data whenever user requires.

Materialized View:
In the case of Materialized view ,This is a concept usually applied in Data warehousing, these views contain the data from which it is created. It provides a way to access the data easier and faster from the remote database.The main reason to the creation of Materialized view is to perform calculations and display data from more than one tables using joins concept.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Materialized Views in Oracle Database"
Thread Thread Starter Forum Replies Last Post
Subqueries in oracle database Landan Software Development 4 09-02-2010 05:57 PM
Installing RAC with oracle database Aileen_L Software Development 4 08-02-2010 04:38 PM
The synonyms in oracle database Norse Software Development 4 02-02-2010 10:46 PM
Packages in oracle database Garlands Software Development 3 30-01-2010 02:55 PM


All times are GMT +5.5. The time now is 02:12 PM.