Oracle Views vs Materialized Views – Difference, Advantages, Disadvantages and Features

Oracle Views
  • Does not require storage
  • Views stored in the database in the form SQL statements
  • When you query on the view the output generates on the fly from the view definition SQLs.
  • Views populate the data from the base tables.
  • Using the updatable views we can update the base tables
  • View is a virtual table
  • View can be used to simplify the SQL statements for the user or to isolate an application from any future changes to the base table definition.
  • View can be used to improve the security by restricting access to a predetermined set of rows or columns.
  • Views always return the latest data from the base tables.
  • The performance of the views are 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.
  • Disadvantages of the views are we cannot implement constraints, triggers, indexes on the views. All based on the underlying table
Oracle Materialized Views
  • MVs are the schema objects with storage.
  • In MVs the underlying query results are stored in separate storage
  • Data in the MVs gets periodically refreshed depends on the requirement
  • The data from the MVs might not be latest
  • MVs are mostly used for data warehousing applications or business intelligence or reporting purpose
  • MVs can be set to refresh manually or as per schedule.
  • MVs are getting refreshed with reference to the MV logs
  • You can define a materialized view on a base table, partitioned table or view and you can define indexes on materialized view
  • Materialized Views can be created in the same database where the base tables exists or in a different database as well.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google