Home » SQL & PL/SQL » SQL & PL/SQL » materialized view
materialized view [message #289974] Wed, 26 December 2007 23:42 Go to next message
atulwagh
Messages: 18
Registered: June 2007
Location: Mumbai
Junior Member
Hi,
We can create view through CREATE VIEW statement
and can create materialized view through CREATE MATERIALIZED VIEW statement.
Can anyone tell me the difference between the views created by above two statements.
When we should create normal view and when materialized view?????



Regards
Atul......


[Updated on: Fri, 28 December 2007 12:58] by Moderator

Report message to a moderator

Re: materialized view........... [message #289975 is a reply to message #289974] Wed, 26 December 2007 23:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Please check the link

Thumbs Up
Rajuvan.

[Updated on: Wed, 26 December 2007 23:52]

Report message to a moderator

Re: materialized view........... [message #290041 is a reply to message #289974] Thu, 27 December 2007 03:58 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Normal view always refer the base table (logical storage) but materialized view stores the data physically.
Suppose if you alter the table you need to drop the view and you have to recreate , but in case of materialized view it will refresh automatically.
Normal view is used in all applications but materialized view mostly used in data warehouse purpose.
Re: materialized view........... [message #290058 is a reply to message #289974] Thu, 27 December 2007 05:06 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:

Suppose if you alter the table you need to drop the view and you have to recreate


could you please explain this ?

Thumbs Up
Rajuvan
Re: materialized view........... [message #290398 is a reply to message #290041] Fri, 28 December 2007 12:52 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
A view is simply a stored query. When you access it , the under laying query is fired and access the base tables. A materialized view is a query that is executed against the base tables, but stores the query results in a physical table. Because the base query was already run, the results can be returned very quickly from the MV, however until you refresh the MV (scheduled or on commit) you will be looking at stored data that might not reflect what is in the actual base tables.

If you modify the base table, the view or MV will become invalid if you remove any of the columns or tables that it references.

[Updated on: Fri, 28 December 2007 12:53]

Report message to a moderator

Re: materialized view [message #290597 is a reply to message #289974] Sun, 30 December 2007 06:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes ,

Thats what i wanted to meantion .

Quote:

Suppose if you alter the table you need to drop the view and you have to recreate , but in case of materialized view it will refresh automatically.


May be the person who Posted this statement is unaware / confused of term ALTER and UPDATE.

Thumbs Up
Rajuvan
Re: materialized view [message #290656 is a reply to message #289974] Mon, 31 December 2007 01:05 Go to previous message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

View - store the SQL statement in the database and let you use it as a table. Everytime you access the view,? the SQL statement executes.

Materialized View - stores the results of the SQL in table form in the database. SQL statement only executes once and after that everytime you run the query, the stored result set is used. Pros include quick query results

and also you check it out this link

http://blog.oraclebrains.com/?p=47


Thanks
Mano
Previous Topic: merge causes problem in distributed environment (merged)
Next Topic: NUmeric format
Goto Forum:
  


Current Time: Sun Dec 04 20:41:53 CST 2016

Total time taken to generate the page: 0.04194 seconds