what is materilaized views?

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?
danieshAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
danieshAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
danieshAuthor Commented:
could you please explain me the basic difference between views and materialized view.
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
0
 
danieshAuthor Commented:
thanks ..
could you please tell me the difference between view and materialized view.
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
danieshAuthor Commented:
thanks for your help.
0
 
danieshAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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
 
danieshAuthor Commented:
thanks a lot...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.