Solved

what is materilaized views?

Posted on 2008-06-16
11
186 Views
Last Modified: 2008-06-17
Hi Experts,
what is materialized views?
how its helps in database performance?why we refresh it?

could you please help me the concept of materialized view?
0
Comment
Question by:daniesh
  • 6
  • 5
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21791791
>what is materialized views?
is a kind of view, but storing the results like a table. based on the refresh parameters, the data can be updated automatically behind, either fully or with transactional update.

>how its helps in database performance?
materialized views can reduce I/O on the queries (check out query rewrite), doing pre-aggregation.

>why we refresh it?
because the underlying data changes, and unlike a traditional view, the underlying data is not queried, but the stored data.
0
 

Author Comment

by:daniesh
ID: 21791830
but view can also store the result like a table if we update any table if we have created a views on it then the changes will reflect in the view.
please explain the below statement in more details

materialized views can reduce I/O on the queries (check out query rewrite), doing pre-aggregation.

0
 

Author Comment

by:daniesh
ID: 21791836
could you please explain me the basic difference between views and materialized view.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21791891
>but view can also store the result like a table
no. I don't know where you read that, but that's wrong. a normal view does not store any data.

>please explain the below statement in more details
>materialized views can reduce I/O on the queries (check out query rewrite), doing pre-aggregation.

for a order-orderdetails, the normalization rules say:
* the order_details contains the price per item, the qty etc ...
the total of the invoice shall the calculated "on the fly", and not stored in the orders table.

however, for reports etc, if you have to recompute the order total amount each time, this is a waste, and will have to read all the order details over and over again.

a materialized view can keep the total/order along with the order_id in it's store, and getting updated automatically with new/changed orders. the total will be computed once, and when the query asks for the order total, the materialized view can give that value quickly, without having to scan all the order details.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21791896
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:daniesh
ID: 21791961
thanks ..
could you please tell me the difference between view and materialized view.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21791996
as it should be obvious by now:
* a view is nothing else than a stored query text (data is NOT stored by the view)
* a mview is a query definition + storage definition, storing data as defined by the query and the refresh options.
0
 

Author Comment

by:daniesh
ID: 21795498
thanks for your help.
0
 

Author Comment

by:daniesh
ID: 21795513
I think one more difference is that we can refresh materialized view and we cant normal view.

one more we can create view only for a particular session.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21796195
>I think one more difference is that we can refresh materialized view and we cant normal view.
that is less a "difference" than "by design"...

>I think one more difference is that we can refresh materialized view and we cant normal view.
?  I don't know what you refer to? views and materialized views are all persistent, there is no such thing as "view for a session only" ?
0
 

Author Comment

by:daniesh
ID: 21801043
thanks a lot...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now