View operations and data access

Data operations in views

In a database, a view is a virtual representation based on the result set of base tables. You can query views and, under specific conditions, perform DML operations, such as insert, update, and delete operations, on views. Operations on a view affect data in its base tables and are controlled by the integrity constraints and triggers of the base tables.

Example

The following example shows how to create an updatable view named staff_dept_10 that contains only the information about employees in Department 10. The CHECK OPTION constraint ensures that DML operations performed on the view take effect only within the data range defined by the view.

Therefore, you can insert data rows for employees in Department 10, but you cannot insert data rows for employees in Department 30.

CREATE VIEW staff_dept_10 AS
SELECT employee_id, last_name, job_id, manager_id, department_id
FROM   employees
WHERE  department_id = 10
WITH CHECK OPTION CONSTRAINT staff_dept_10_cnst;

Data access from views

OceanBase Database stores the queries that define views in the internal data dictionary. When you query data from a view, OceanBase Database performs the following operations:

1. Parse your query, and when a view is identified, obtain the query that defines the view from the data dictionary and parse the query.
2. Try to combine the query that defines the view with your query to generate a better execution plan.
3. Query data based on the generated execution plan.

Example

1. A view named staff_dept_10 is created.

   CREATE VIEW staff_dept_10 AS
   SELECT employee_id, last_name, job_id, manager_id, department_id
   FROM   employees
   WHERE  department_id = 10
   

2. The staff_dept_10 view is accessed in the following query.

   SELECT last_name FROM staff_dept_10 WHERE employee_id = 200;
   

3. OceanBase Database parses the query.

   SELECT last_name
   FROM   (SELECT employee_id, last_name, job_id, manager_id, department_id
            FROM   employees
            WHERE  department_id = 10) staff_dept_10
   WHERE  employee_id = 200;
   

4. OceanBase Database rewrites the query to combine it with the query that defines the view.

   SELECT last_name
   FROM  employees
   WHERE  employee_id = 200 AND department_id = 10;
   

5. OceanBase Database generates an execution plan and executes the SQL query.

Updatable views

Views that can be used for updating data are called updatable views. You can update and drop updatable views.

Example

The following example updates the staff_dept_10 view by deleting part of data represented by the view.

DELETE FROM staff_dept_10 WHERE employee_id = 200;

OceanBase Database processes the request as follows:

1. Parse the statement and replace staff_dept_10 with the query that defines the view.
2. Verify that the view meets the constraints on an update object.
3. Combine the query that defines the view with the statement.
4. Generate an execution plan and execute the statement.

OceanBase Database essentially executes the following statement:

DELETE FROM employees WHERE employee_id = 200 AND department_id = 10;

You can update a view only when the following conditions are met:

• The update operation involves only one entity table.
• In the query that defines the view, each row of data represents only a unique entity in the table to be modified. The query does not involve a many-to-many join.
• The view definition does not contain the WITH READ ONLY constraint.