views and materialised

[Former Member]

Hai Everyone,

What is the difference between view and materialised view?

can the views be updated?

[Former Member · Accepted solution]

Hai Sruthi,

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.

Hope this helps you,

Thanks & Regards

Pujitha

[Former Member]

Hai Pujitha,

Your last point on triggers had helped me a lot.

Thanks

[Former Member]

Hi Sruthi,

Views evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else. The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.

Materialized views are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query resultset has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once (or as often as you refresh your materialized view), rather than each time you select from the materialized view. In addition, with query rewrite enabled, Oracle can optimize a query that selects from the source of your materialized view in such a way that it instead reads from your materialized view. In situations where you create materialized views as forms of aggregate tables, or as copies of frequently executed queries, this can greatly speed up the response time of your end user application. The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed.

Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated by combining them with materialized view logs, which act as change data capture sources on the underlying tables.

Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application

All the Best,

Praveen

[Former Member]

Hi sruthi,

1. Views

View takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.

All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.

A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.

In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).

Materialized Views

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.

A materialized view 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.

The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.

2. Yes we can update the view which reflects the original table.But certain limitations exist depending upon the type of view

Update data

There are some restrictions for updating data through a view.

u2022A view cannot modify more than one table. So if a view is based on two or more tables, and you try to run a DELETE statement, it will fail. If you run an UPDATE or INSERT statement, all columns referenced in the statement must belong to the same table.

u2022It's not possible to update, insert or delete data in a view with a DISTINCT clause.

u2022You cannot update, insert or delete data in a view that is using GROUP BY.

u2022It's not possible to update, insert or delete data in a view that contains calculated columns.

Also be aware of columns that cannot contain NULL. If it has no default value, and is not referenced in the view, you will not be able to update the column.

All the Best,

Madhu...