A view in a database is a virtual table whose content is defined by an SQL query. Like a table, a view consists of rows (that is, records) with the same attributes (that is, columns or fields). However, a view does not exist as a stored set of data values in a database. Instead, the database stores an SQL query that is to be executed when you access a view. A view simplifies complex SQL operations, improves data access security, and provides a way to package data logic.
Classification
Standard views
Standard views, also called non-materialized views or just views, are the most common ones. A standard view stores only the SQL query that defines the view, but not the query result. When you query a standard view, the database dynamically executes the SQL query that defines the view to retrieve the latest data.
Materialized views
Unlike standard views, materialized views store the query results in physical storage. A materialized view regularly synchronizes data from its base tables to keep the data up-to-date. Materialized views can greatly optimize the query performance, especially in large data sets involving complex joins and aggregations.
Benefits
Simplified queries
Views can package complex queries to simply the queries from applications. You can query views the same way you query normal tables.
Security and privilege control
Views can control access to data by displaying only data on which a specific user or user group has privileges. This ensures data security and complies with data privacy policies.
Logical data independence
When the underlying data structure changes, a view provides a unified API so that you do not need to modify the applications relying on the data.
Data integration
You can use a view to extract data from multiple tables, thus creating a logical representation of data to integrate the data.
Application scenarios
Database abstraction layer
In a multi-layer application architecture, views provide a database abstraction layer to simplify application development and maintenance.
Data security
Views can be used for role-based access control. In this case, only the data important to a role is displayed.
Complex reporting and analysis
A view can package the logic of a complex reporting and analysis query that summarizes data from multiple tables, to simplify and optimize data retrieval.
Performance optimization
Materialized views can significantly improve the performance of queries involving complex operations such as aggregations and joins, because data in such views is pre-calculated and stored.
Standard views and materialized views can improve the efficiency and ease of data access while ensuring security and performance.