A view is the logical representation of one or more tables. In essence, a view is a query on the storage.
A view exports the data from the table it is based on, which is called the base table. The base table can be a table or other view. All operations performed on the view actually affect the base table. You can use views in most places where tables are used. Note
The data structure of materialized views is different from that of standard views.
Views enable you to customize the presentation of data for different types of users. Views are typically used to:
Provide additional table security by limiting access to a group of predetermined rows or columns of a table.
Hide data complexity.
For example, you can use JOIN to define a single view. JOIN groups related columns or rows from multiple tables. However, the view hides the fact that the information it presents originates from several tables. In addition, queries may also use table information to perform a large amount of computation. Therefore, users can query views without knowing how to perform JOIN queries or computation.
Present data in a different way from that of the base table.
For example, you can rename the columns of a view without affecting the table on which the view is based.
Isolate the application from changes in the base table definition.
For example, the query for the view definition refers to three columns in a four-column table. If you add a fifth column to the table at this time, the definition of the view will not be affected. All applications that use this view are not affected as well.
Features of views
Unlike tables, views do not allocate storage space or store data. Instead, views are defined by the queries that extract or export data from the base table. Views are generated based on other objects, so queries that define views in the data dictionary require no storage other than the database memory.
The view has dependencies on the objects it references, which are automatically processed by OceanBase. For example, if you delete and recreate the base table of a view, OceanBase will determine whether the view definition can accept the new base table.
Data operations in views
Views are derived from tables. Therefore, they are similar to each other in many aspects. You can query a view and perform DML operations on the view. Operations performed on a view affect the data in one of the base tables and are restricted by the requirements of base table integrity and the trigger.
Access data in a view
OceanBase Database stores the definition of a view in the data dictionary as the text of the query that defines the view.
When a view is referenced in an SQL statement, OceanBase Database performs the following operations:
Merge queries for the view with queries that define the view and its underlying views, whenever possible.
OceanBase Database optimizes merged queries as if you made the queries without referencing the views. Therefore, OceanBase Database can use indexes on referenced base table columns, regardless of whether the columns are referenced in the view definition or in user queries for the view.
Sometimes, OceanBase Database cannot merge the view definition with a user query. In this case, OceanBase Database may not use all indexes on referenced columns.
Parse the merged statement in a shared SQL area.
OceanBase Database parses a statement that references a view in a new shared SQL area only if no existing shared SQL area contains a similar statement. Therefore, views provide the benefit of reduced memory usage associated with the shared SQL statement.
- Execute the SQL statement.
Updatable join views
Join views have more than one table or view in its FROM clause.
An updatable join view, or a modifiable join view, is a join view that involves two or more base tables or views and supports DML operations. An updatable view contains multiple tables in the top-level FROM clause of the SELECT statement and is not restricted by the WITH READ ONLY clause.
A view must meet several criteria to be updatable. For example, a general rule is that an INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.