Solved

Views vs MVs

Posted on 2014-01-20
4
245 Views
Last Modified: 2014-01-21
Why do we prefer views or materialized views..  I know views has advantages like restricting access to all columns for security but we can select few columns in MVs as well.  Could you please suggest the advantages of MVs here.
0
Comment
Question by:d27m11y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 39795497
If a query takes a really long time to run the MV can have the results pre-computed for you.

This assumes the data changes slowly enough that generating the results into the MV will still be valid when a user queries them
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 125 total points
ID: 39795498
A view is a "window" into certain data in your database.  When you select data from a view, the database engine still has to fetch the data, perform the relevant I/O's and return the data to you.

If you have a complex query that may run for say 5 minutes, you have to sit and wait for that to happen.

A view does not occupy any space in the database, it's just a definition of an defined query into the data.

A Materialized View is a physical database object.  It is refreshed from the primary data sources on a preset value (maybe hourly, maybe nightly) and could be used to populate data in a data warehouse, for example.

If you have complex computations or access paths that need to be done, that work is done while the MV is being refreshed so when you access data from the MV, all the calculations and whatever else was being done that takes 5 minutes with a regular view should all have been done while the MV was being refreshed, so theoretically you get your data NOW instead of waiting.

Of course, views get you real time data.  MV's get you data as of the last refresh (when ever that was).

If you've got any more specific question, feel free to ask, hope that gets you pointed in the right direction.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 39795505
For example,   you could have a query like this...


select * from table1@db_link1 t1 jeft join table2@db_link t2
on t2.id = t1.id

pulling data across 2 database links in order to generate a local result can be an expensive and time consuming operation, if your network connection is unreliable the query may simply fail.

Using a materialized view of that query, the results could be generated once, saved and then not generated again until data changed on one or both of the remote systems.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 125 total points
ID: 39796142
Views and MVs have a set of distinct features/purpose for which they are existing. Please refer to the below links which give comprehensive and simple to understand the point by point differences between these 2 objects.

https://community.oracle.com/thread/663466?start=0&tstart=0
http://its-all-about-oracle.blogspot.sg/2013/06/difference-between-view-and.html
http://www.oratable.com/views-materialized-views-difference/

Thanks,
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question