Views in Oracle _oracle

1. Overview of Views

A view is actually a query SQL statement that displays related data in one or more tables or other views. The view uses the results of a query as a table, so the view can be viewed as a stored query or a virtual table. Views are derived from tables, and all modifications to the view data are ultimately reflected in the base table of the view, which must be subject to the integrity constraints of the base table and trigger that is defined on the base table. (Oracle supports explicit definition of triggers and the definition of some logical constraints on views)

2. Storage of views

Unlike tables, views do not require allocation of storage space, nor does the view contain actual data. The view simply defines a query that the data in the view is obtained from the base table, which is generated dynamically when the view is referenced. Because the view is based on other objects in the database, a view requires only the space in the data dictionary to hold its definition, without the additional storage space.

3. The role of the view

Users can display data from a base table in different forms, and the view is powerful in that it can organize the data in the base table according to the needs of different users. Common uses for views are as follows:

The view allows you to set the columns and rows of data that the user is allowed to access, thereby providing additional security control for the table

Hide Data complexity

A connection (join) can be used in a view to form a new dataset with related columns from multiple tables. This view hides the fact that the data originates from more than one table for the user.
　
simplifying SQL statements for users

Users can use views to query information from multiple tables without having to understand how the tables are connected.
　
Display data from a base table in a different perspective

The column name of the view can be arbitrarily changed without affecting the base table of this view
　
To make an application unaffected by changes in the base table definition

A 3 column in a base table that contains 4 data columns is queried in the definition of a view. When a new column is added to the base table, the application using this view will not be affected because the definition of the view is not affected.
　
save a complex query

A query might perform complex calculations on table data. Once the user has saved the query as a view, you only need to query this view each time you perform a similar calculation.
Logical Data Independence

Views allow applications and database tables to be isolated to some degree. If there is no view, the application must be based on the table. With a view, the program can be built on top of the view, and the program is separated from the database table by the view.
4. Working mechanism of the view

The definition of a view is the query statement it uses, and Oracle stores the definition as text in the data dictionary. When a user references a view in an SQL statement, Oracle does the following:

To consolidate statements that refer to a view to a statement with a view's definition statement
Resolving consolidated statements in a shared SQL zone
Execute this statement
Oracle creates a new shared SQL area for this statement when there are no consistent statements in the existing shared SQL zone. As a result, SQL statements referencing views can also take advantage of existing shared SQL extents to conserve memory and improve performance.

5. Dependency of view

Because the definition of a view is a query that references other objects (tables, views), the view depends on the object it refers to. Oracle will automatically handle the dependencies of the view. For example, when a user removes a base table from a view and then rebuilds the table, Oracle checks to see if the new base table conforms to the definition of the view and determines the validity of the view.

6. Updatable Connection View

A connection view refers to multiple tables or views that are referenced in the FROM clause of a definition query for a view. An updatable connection view is a connection view that can perform Update,insert, and DELETE operations. In order to ensure that the view is updatable, its definition cannot contain the following syntax structure:

Set operator
DISTINCT operator
aggregate function or analytic type function
GROUP By,order By,connect by, or START with words
Use a collection expression in the list after SELECT
Use subqueries in the list after SELECT
Joins (join) (with exceptions)
For views that are not updatable, you can use the INSTEAD of triggers to modify their data.