View Details in Oracle

1. View Overview

A view is actually a query SQL statement used to display relevant data in one or more tables or other views. The view uses the results of a query as a table. Therefore, the view can be viewed as a stored query or a virtual table. The view comes from the table. All modifications to the view data are eventually reflected in the base table of the view. These modifications must comply with the integrity constraints of the base table and also trigger the trigger defined on the base table. (Oracle supports explicit definitions of triggers and logical constraints on the view)

2. View Storage

Unlike tables, a view does not require storage space allocation and does not contain actual data. The view only defines a query. The data in the view is obtained from the base table, which is dynamically generated when the view is referenced. Because a view is based on other objects in the database, a view only needs to occupy the space defined by the data dictionary, without additional storage space.

3. View Functions

You can use a view to display data in the base table in different forms. The view is powerful in that it can sort the data in the base table according to the needs of different users. Common usage of a view is as follows:

You can use the view to set columns and data rows that can be accessed by users, providing additional security control for the table.

Hide data complexity

Join can be used in a view to create a new dataset with related columns in multiple tables. This view hides the fact that data comes from multiple tables.

Simplify Your SQL statements

You can use a view to query information from multiple tables without having to know how these tables are connected.

Display Data in the base table from different angles

The column name of a view can be changed without affecting the base table of the view.

Enable ApplicationProgramDoes not affect the definition of the base table.

A view defines three columns in a base table containing four data columns. After a new column is added to the base table, the application that uses this view will not be affected because the view definition is not affected.

Save complex queries

A query may perform complex calculations on table data. After you save the query as a view, you only need to query the view for similar computing each time.
Logical Data independence

View can make applications and database tables independent to a certain extent. If there is no view, the application must be created on the table. With the view, the program can be built on the view, so that the program and the database table are separated by the view.
4. View Working Mechanism

The view definition is the query statement it uses. Oracle stores this definition in text form in the data dictionary. When you reference a view in an SQL statement, Oracle performs the following operations:

Integrate the statements that reference the view with the Definition Statement of the view into a statement.
Resolved consolidated statements in the shared SQL Zone
Execute this statement
When the existing shared SQL partition is not consistent with the consolidated statement, Oracle creates a new shared SQL partition for this statement. Therefore, SQL statements that reference a view can also use the existing shared SQL zone to save memory and improve performance.

5. View dependency

A view is a query that references other objects (tables, views). Therefore, a view depends on the referenced objects. Oracle automatically processes view dependencies. For example, if you remove a base table from a view and recreate the table, Oracle checks whether the new base table conforms to the view definition and determines whether the view is valid.

6. updatable connection View

A connection view refers to multiple tables or views referenced in the from clause of the definition query of a view. The updatable connection view is the connection view that can execute update, insert, and delete operations. To ensure that a view is updatable, its definition cannot contain the following syntax structure:

Set Operators
Distinct Operator
Aggregate or analytical functions
Group by, order by, connect by, or start
Use a set expression in the list after select
Use subquery in the list after select
Join (with exceptions)
For non-updatable views, you can use the instead of trigger to modify their data.