Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Views vs MVs

Posted on 2014-01-20
4
Medium Priority
?
251 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 1000 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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 500 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 1000 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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

715 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