Following are the Disadvantages of materialized View in SQL
1.We can not perform any DML Operations on materialized View ,but you can perform DDL Operations like DROP.The thing is here it stores the all records even if it is duplicate or non-duplicates,especially which we are using aggregate values.For example daily loads,monthly loads,yearly loads.such cases it would be very helpful storing of entire data if it is new or old.
The disadvantage is takes space Can only be based on a simple Select if you require real time data. maintaining the materialized View Logs has an overhead on the master system.
2.We can't perform DML on materialized View because it is like snapshot or read only .it is mainly used for reporting purposes.A materialized view can be either read-only, updatable or writable. We Can't Perform DML on read-only but can Perform DML on updatable or writable.
3.On readable - we cant perform DML.
on updatable- we can perform DML, we need to create the materialized View by using FOR UPDATE clause and it must belong to materialized group.
on writable- if we create a materialized View by using FOR UPDATE but do not assign a group it would become writable, we can perform DML but as soon as you refresh the materialized View, changes will be lost and will not be pushed to master table.
1.We can not perform any DML Operations on materialized View ,but you can perform DDL Operations like DROP.The thing is here it stores the all records even if it is duplicate or non-duplicates,especially which we are using aggregate values.For example daily loads,monthly loads,yearly loads.such cases it would be very helpful storing of entire data if it is new or old.
The disadvantage is takes space Can only be based on a simple Select if you require real time data. maintaining the materialized View Logs has an overhead on the master system.
2.We can't perform DML on materialized View because it is like snapshot or read only .it is mainly used for reporting purposes.A materialized view can be either read-only, updatable or writable. We Can't Perform DML on read-only but can Perform DML on updatable or writable.
3.On readable - we cant perform DML.
on updatable- we can perform DML, we need to create the materialized View by using FOR UPDATE clause and it must belong to materialized group.
on writable- if we create a materialized View by using FOR UPDATE but do not assign a group it would become writable, we can perform DML but as soon as you refresh the materialized View, changes will be lost and will not be pushed to master table.