A table is where data is stored. You always start with tables first, and then your usage pattern dictates whether you need views or materialized views.

A view is like a stored query for future use, if you’re frequently joining or filtering the same tables the same way in multiple places.

A materialized view is like a combination of both: it’s a table that is automatically populated and refreshed via a view. You’d use this if you were using views, and want to pre-join or pre-aggregate the rows to speed up queries.

materialized view

Figure below shows an example of using the Materialized View pattern. Data in the Order, OrderItem, and Customer tables in separate partitions in a Microsoft Azure storage account are combined to generate a view containing the total sales value for each product in the Electronics category, together with a count of the number of customers who made purchases of each item.

materialized view2

Materialised View:Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data.It  provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.

Senario:While creating a dataware house we need to go for staging part at that time this is useful.

VIEWS:

We have 2 types

1)Read only

2)Updatable

An updatable view lets you insert, update, and delete rows in the view and propagate the changes to the target master table.

 

In order to be updatable, a view cannot contain any of the following constructs:

SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list

or finally (with some exceptions) a JOIN

 

Views that are not updatable can be modified using an INSTEAD OF trigger.

Reference:

https://msdn.microsoft.com/en-us/library/dn589782.aspx

https://blogs.saphana.com/2014/10/27/how-simple-finance-removes-redundancy-the-case-of-materialized-views/

 

 

 

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *