View details in oracle
- 2020-09-16 07:50:07
- OfStack
1. Overview of the view
A view is simply a query sql statement that displays relevant data from one or more tables or other views. A view USES the results of a query as a table, so a view can be thought of as a stored query or as a virtual table. Views originate from tables, and all changes to view data are ultimately reflected in the view's base table, subject to the integrity constraints of the base table, and also trigger the triggers defined on the base table. (Oracle supports defining triggers and defining 1 logical constraints explicitly on views)
2. Storage of views
Unlike tables, views do not require storage allocation, nor do they contain actual data. The view simply defines a query where the data in the view is fetched from the base table and is dynamically generated when the view is referenced. Because views are based on other objects in the database, a view only needs to occupy the space in the data dictionary to hold its definitions, and no additional storage space is required.
3. The role of the view
Users can display the data in the base table in different forms through the view. The power of the view is that it can organize the data in the base table according to the needs of different users. Common USES of views are as follows:
Views provide additional security control for tables by allowing users to set the columns and rows of data that they can access
Hiding data complexity
A view can use joins (join) to form a new data set with related columns from multiple tables. This view hides from the user the fact that the data comes from multiple tables.
Simplify the user's SQL statement
Users can use views to query information from multiple tables without knowing how the tables are joined.
Display the data in the base table from different angles
The column name of a view can be changed arbitrarily without affecting the base table of this view
Make the application immune to changes to the base table definition
Three columns in a base table containing four data columns were queried in the definition of a view. When a new column is added to the base table, because the definition of the view is not affected, the application that USES the view is not affected.
Save complex queries
1 query may perform complex calculations on table data. After the user has saved the query as a view, he can simply query the view every time he performs a similar calculation.
Logical data independence
Views can separate the application and the database tables to a degree of 1. If there is no view, application 1 must be based on a table. Once you have a view, your program can be built on top of the view, separating the program from the database tables by the view.
4. How the view works
The definition of a view is the query it USES, and Oracle stores this definition as text in the data dictionary. When a user references a view in an SQL statement, Oracle does the following:
Combine the statement that references the view with the definition statement of the view into a single statement
Parse the consolidated statement in the Shared SQL block
Execute this statement
Oracle only creates a new Shared SQL block for an existing Shared SQL block when it is not sent to the consolidated statement 1. Therefore, SQL statements that reference views can also take advantage of existing Shared SQL areas to save memory and improve performance.
5. View dependencies
Because a view is defined as a query that references another object (table, view), the view depends on the object it references. Oracle automatically handles the dependencies of the view. For example, when a user removes a view's base table and rebuilds it, Oracle checks to see if the new base table conforms to the definition of the view and determines whether the view is valid.
6. Updatable connection view
A join view refers to more than one table or view in the from term of the definition query for a view. The updatable connection view is the connection view that can perform UPDATE, INSERT, and DELETE operations. To ensure that a view is updatable, its definition cannot contain the following syntax:
Set operator
DISTINCT operator
Aggregate or analytical functions
GROUP BY, ORDER BY, CONNECT BY, or START WITH
Use the collection expression in the list after SELECT
Use the subquery in the list after SELECT
Connection (join) (with exceptions)
For views that are not updatable, you can use INSTEAD OF triggers to modify their data.
A view is simply a query sql statement that displays relevant data from one or more tables or other views. A view USES the results of a query as a table, so a view can be thought of as a stored query or as a virtual table. Views originate from tables, and all changes to view data are ultimately reflected in the view's base table, subject to the integrity constraints of the base table, and also trigger the triggers defined on the base table. (Oracle supports defining triggers and defining 1 logical constraints explicitly on views)
2. Storage of views
Unlike tables, views do not require storage allocation, nor do they contain actual data. The view simply defines a query where the data in the view is fetched from the base table and is dynamically generated when the view is referenced. Because views are based on other objects in the database, a view only needs to occupy the space in the data dictionary to hold its definitions, and no additional storage space is required.
3. The role of the view
Users can display the data in the base table in different forms through the view. The power of the view is that it can organize the data in the base table according to the needs of different users. Common USES of views are as follows:
Views provide additional security control for tables by allowing users to set the columns and rows of data that they can access
Hiding data complexity
A view can use joins (join) to form a new data set with related columns from multiple tables. This view hides from the user the fact that the data comes from multiple tables.
Simplify the user's SQL statement
Users can use views to query information from multiple tables without knowing how the tables are joined.
Display the data in the base table from different angles
The column name of a view can be changed arbitrarily without affecting the base table of this view
Make the application immune to changes to the base table definition
Three columns in a base table containing four data columns were queried in the definition of a view. When a new column is added to the base table, because the definition of the view is not affected, the application that USES the view is not affected.
Save complex queries
1 query may perform complex calculations on table data. After the user has saved the query as a view, he can simply query the view every time he performs a similar calculation.
Logical data independence
Views can separate the application and the database tables to a degree of 1. If there is no view, application 1 must be based on a table. Once you have a view, your program can be built on top of the view, separating the program from the database tables by the view.
4. How the view works
The definition of a view is the query it USES, and Oracle stores this definition as text in the data dictionary. When a user references a view in an SQL statement, Oracle does the following:
Combine the statement that references the view with the definition statement of the view into a single statement
Parse the consolidated statement in the Shared SQL block
Execute this statement
Oracle only creates a new Shared SQL block for an existing Shared SQL block when it is not sent to the consolidated statement 1. Therefore, SQL statements that reference views can also take advantage of existing Shared SQL areas to save memory and improve performance.
5. View dependencies
Because a view is defined as a query that references another object (table, view), the view depends on the object it references. Oracle automatically handles the dependencies of the view. For example, when a user removes a view's base table and rebuilds it, Oracle checks to see if the new base table conforms to the definition of the view and determines whether the view is valid.
6. Updatable connection view
A join view refers to more than one table or view in the from term of the definition query for a view. The updatable connection view is the connection view that can perform UPDATE, INSERT, and DELETE operations. To ensure that a view is updatable, its definition cannot contain the following syntax:
Set operator
DISTINCT operator
Aggregate or analytical functions
GROUP BY, ORDER BY, CONNECT BY, or START WITH
Use the collection expression in the list after SELECT
Use the subquery in the list after SELECT
Connection (join) (with exceptions)
For views that are not updatable, you can use INSTEAD OF triggers to modify their data.