Views are a very useful resource in relational databases: they let you create logical representations of the data stored in tables, making querying, security, and maintenance easier.
But what exactly are views?
A view can be understood as a virtual table based on the result of a query. In other words, instead of storing data directly, it only displays data that belongs to other tables. That data is retrieved through a SQL statement every time the view is executed.
According to the Oracle 19c documentation:
Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.
Advantages of using views
– Simplify complex queries
Using views lets you encapsulate a complex query inside a simple one.
– Security in data access
With views you can restrict access to specific columns or rows, exposing only the data that is actually needed.
– Abstraction
Views make it possible to implement an abstraction layer that hides the table structure or even the source of the data.
– Query reuse
For queries that run with some frequency, creating a view is recommended to reduce code repetition.
Types of views in Oracle
Views
They can range from a simplified structure based on a single table, with no aggregate functions or joins, to more complex ones with joins across multiple tables, aggregate functions, subqueries, and so on.
Views with a simple structure usually allow inserts and updates against the data in the base table.
Views that do not allow inserts and updates are the ones that contain clauses such as DISTINCT, aggregate functions, GROUP BY, ORDER BY, subqueries in the select, recursive conditions with the WITH clause, and so on.
Regardless of its structure, a view takes up no disk space.
More information is available at https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/UPDATE.html
Example:
CREATE VIEW vw_empresas AS
SELECT id, nome_empresa
FROM empresas;Materialized Views
Materialized views, on the other hand, store data physically, which means they do consume disk space.
Materialized views can be used to improve performance for complex queries or queries that involve large volumes of data.
They can also be used to transport or replicate data to other Oracle databases through database links.
MVs can be refreshed on a schedule or on demand, and they can be fully rebuilt on every refresh or refreshed incrementally using mlogs (materialized view logs).
Example:
CREATE MATERIALIZED VIEW mv_vendas
REFRESH FAST ON COMMIT
AS
SELECT produto_id, SUM(quantidade) AS total_vendido
FROM vendas
GROUP BY produto_id;