Difference between view and materialized view in oracle sql plsql interview question and answer.
Difference between view and materialized view in oracle sql plsql interview question and answer.
View and Materialized view are called as the database object or the named query.
View is the virtual table which stores the select statement from the base table by execution of the sql query at the runtime. View Query is running for the view definition.
In the case of the materialized view, the data from the base table is loaded in the temporary or physical memory location by which the materialized view gets refreshed and all the dependent tables on the materialized view are also get updated after refresh operation. Materialized views are used in lakhs lines of code. In the data lake, tables are dependent on the master table to get updated where more than lakhs line of codes, today every reporting is based on this, in that case materialized view are used and preferred on the master tables. An activity of materialized view will be refreshed by the below command, and it is having the physical data retrieved from the base table is shown with the help of select statement till refresh.
exec DBMS_MVIEW.refresh('Table name which you want to refresh'); Materialized view is used because view is failed to display the data.
View and materialized view are used to provide the result of the select query. There is process of updating MV is called as MV maintenance.
View is the important concept of the database management system.
Whenever an query is fired, view returns the updated data from the base table or the original table and change is clearly seen in it.
VIEW:
It is slower than the materialized view.
When we will update the base table, there is no need refresh it.
Syntax: Create view view_name AS SELECT columns FROM TABLES [WHERE CONDITIONS];
Here, view is the keyword.
It contains the select SQL statement.
It is not required the space for holding the data.
If the base table is dropped, then also the view will not be accessible.
DML operations can directly performed on the view.
It does not store the data, but it stores the SQL query.
Base table dependency doesn't on the other tables, then it is preferred to use the view.
Refresh activity does not take place.
It fetches the latest data from the base table.
Any change in the base table data will reflect in the view immediately.
It is called as the online data retrieved from base tables.
It retrieves the data from the base table.
It is used in the simple queries.
Indexing cannot be applied.
Performance is slow for select queries.
Maintenance or updating cost don't occur.
When the table's data is updated frequently and its access infrequently.
It is dynamic because it will show the updated data.
Storage cost is present.
Execution of automatic action method or trigger to refresh is not required.
Don't require the physical copy of the database.
Execution of the query will provide result or output which is not stored in the disk or the database.
It is designed with fixed architecture approach for defining with sql standard.
Row id is similar with the original table.
Materialized View:
It is faster than the view.
When we will update the base table, there is need to manual refresh it.Refreshing technique is used to refresh the materialized view’s temporary table.
Syntax:
Create Materialized view_name AS
SELECT columns FROM TABLES
[WHERE conditions];
Here, materialized is the keyword.
It contains the data or output of the select statement.
It is required the space for holding the data.
If the base table is dropped, then also the view will be accessible.
DML operations cannot be directly performed on the materialized view.
It stores the data and query.
Base table dependency is on the other tables, then it is preferred to use the materialized view.
Refresh activity takes place. It stores the data of the remote table which is called snapshot. Snapshot is the older form of materialized view which is used in the data warehouse
It does not fetch the latest data from the base table, it fetches the data from the physical table.
Any change in the base table ‘s data will not reflect in the materialized view immediately.
It is called as the offline data or delay data retrieved from base tables.
It retrieves the data from the materialized table which need to be manually updated through refresh from the underlying base tables.
It is used in the complex queries.
Indexing can be applied.
Performance is fast for select queries
Maintenance or updating cost occur.
When the table's data is updated infrequently and its access frequently.
It is static because it will show the updated data after last refresh.
Storage cost is not present.
Execution of automatic action method or trigger to refresh.
It requires the physical copy of the database.
Execution of the query will provide result or output which is stored in the disk or the database.
It is designed with generic architecture approach for not defining with sql standard.
Row id is entirely different with the original table.