Author
Home > Technology > What is the Difference Between View and Materialized View?

Guilherme Mazui

•5 minutes to read

What is the Difference Between View and Materialized View?

🆚 Go to Comparative Table 🆚

The main difference between views and materialized views in SQL lies in their data storage and query performance. Here are the key differences:

  • Dynamic vs. Static: Views are dynamic, meaning they always reflect the latest data from the underlying tables or views when accessed. Materialized views, on the other hand, are static and only show the data from the last refresh.
  • Storage: Views are virtual and do not store any data. The query underlying the view is executed each time the view is accessed, fetching the latest data from the underlying tables. Materialized views, however, store the result set of the underlying query on disk, which can be accessed like a regular table.
  • Performance: Materialized views generally offer better performance than views because the data is stored on disk and can be indexed, allowing for faster query execution. Views, on the other hand, depend on the performance of the underlying tables and the complexity of the view's query definition.
  • Refresh: Materialized views need to be manually or automatically refreshed to keep the data up-to-date. Views do not require any manual refresh, as they always reflect the latest data from the underlying tables.

In summary, views are more suitable for ad hoc queries and provide the latest data, while materialized views are more efficient for frequently accessed data that does not require real-time updates. Materialized views can improve query performance by storing and indexing the result set, whereas views depend on the performance of the underlying tables and their query definition.

On this page
  • What is the Difference Between View and Materialized View?
  • Comparative Table: View vs Materialized View

    • Comparative Table: View vs Materialized View

    Here is a table highlighting the differences between View and Materialized View:

    Feature View Materialized View
    Definition View is a logical virtual copy of a table created by a "select query", with the result not stored anywhere in the disk. Materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use.
    Data Storage View does not store data on the disk or in the database. Materialized view stores the result set of the query on the disk or in the database.
    Performance View's performance is lower than that of materialized views, as the query needs to be executed each time the data is accessed. Materialized view has better performance than views, as the data is stored on the disk, reducing the need for frequent execution of the query.
    Update Frequency Views are used when data is accessed infrequently and the data in a table gets updated frequently. Materialized views are used when data is accessed frequently and not often updated or when the query consumes many resources.
    Security Security can be better controlled in a view rather than a table, as views can be used to hide certain columns from users. Security can also be better controlled in a materialized view, similar to a view.
    Automatic Refresh Materialized views can be set up to refresh automatically on a periodic basis. A rowid of a view is the same as the original table, while rowid of a materialized view is different.
    Applications Views are mostly used in application-based environments, as they provide a more feasible, logical representation of table data without taking up extra space. Materialized views are mostly used in data warehousing or business intelligence applications.

    In summary, views are used when data is accessed infrequently and updated frequently, while materialized views are used when data is accessed frequently and not often updated. Materialized views store the query result set on the disk, providing better performance than views.

    Read more:

    Guilherme Mazui

    Guilherme Mazui is graduated in journalism from the Federal University of Minas Gerais (UFMG) and a master's degree in Communication from the University of São Paulo (USP). In addition, he has experience in advertising writing and has worked as a content editor in several companies.