view and materialized view

[Former Member]

Hi Experts,

1.Difference between Table and View.

2.Difference between View and materialized view.

3.Is it possible to update the data using view.

Could you please any one help me..

Thanks&Regards

Ramesh

[Former Member · Accepted solution]

a) The database table has a physical existence in the database.

b) A view is a virtual table, that is one that does not actually exist. View is made up of a query on one or many tables in a database.

c) materialized view stores the results of the SQL in table form in the database. where as view stores the SQL.

SQL statement only executes once and after that everytime you run the query the stored result set is used. which improve the performence.

Updating the data using the view is not possible as view is depend on base table. You can perform DML on the view but not using the view.

[Dev · Accepted solution]

While I am trying to create an MV my Hana doesn't recognizes it's syntax and we are on sap bw 7.5 sp 20 on Hana 2.3. May I know if materialisation doesn't support here?

[Former Member]

Hai Ramesh,

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]

1 - A table is physical and holds data, a view just holds SQL code.

2 - A view just holds SQL code, a materialised view becomes physical when it is refreshed

3 - Update what data? Data is updated via an update statement. To change a view, you would either alter it or drop it and recreate it.