Outlining Oracle Views

1. Overview of the View

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 thought of as a stored query or a virtual table. Views originate from tables, and all changes to view data are eventually reflected in the base table of the view, which must obey the integrity constraints of the base table and also trigger triggers defined on the base table. (Oracle supports explicitly defining triggers and defining some logical constraints on the view)

2. Storage of views

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

3. The role of the view

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

View allows you to set the columns and rows of data that are accessible to the user, providing additional security controls for the table

Hide Data complexity

A connection (join) can be used in a view to form a new dataset with related columns in multiple tables. This view hides the fact that the data originates from multiple tables for the user.

simplifying the user's SQL statements

Users can use views to query information from multiple tables without needing to know 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

Make the application unaffected by changes in the base table definition

A 3 column in the base table that contains 4 data columns is queried in the definition of a view. When new columns are added to the base table, applications that use this view will not be affected because the definition of the view is not affected.

Saving Complex Queries

A query might perform complex calculations on table data. After the user saves the query as a view, it is only possible to query this view each time a similar calculation is made.
Logical Data Independence

Views enable applications and database tables to be somewhat independent. If there is no view, the application must be built on the table. With a view, the program can be built on top of the view so that the program is separated from the database table by the view.
4. Working mechanism of views

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

Consolidate statements that reference views and definition statements for views into a single statement
Parsing consolidated Statements in a shared SQL zone
Execute this statement
Oracle creates a new shared SQL zone for this statement when the existing shared SQL area is not consistent with the consolidated statement. Therefore, SQL statements that reference views can also take advantage of existing shared SQL areas to conserve memory and improve performance.

5. Dependency of Views

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

6. Updatable Connection views

A connection view refers to multiple tables or views that are referenced in the FROM clause of a defined query in a view. An updatable connection view is a connection view that can perform Update,insert, and DELETE operations. To ensure that the view is updatable, the 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 clause
Use a set expression in a list after SELECT
Using subqueries in the list after SELECT
Connection (join) (with exception)
For non-updatable views, the INSTEAD of triggers can be used to modify their data.

Outlining Oracle Views