Data access from views

OceanBase Database stores view definitions in the internal data dictionary as the text of the queries that define the views.

When you reference a view in a query statement to read data, OceanBase Database performs the following steps:

1. Parses the user query. If a view name is detected during the parsing process, OceanBase Database retrieves and parses the query that corresponds to the view definition from the data dictionary.

2. Tries to merge the view definition with the user query. After the merge, a better execution plan may be generated.

3. Generates an execution plan and executes the merged query statement.

Example

1. Create a view named staff_dept_10. The following example shows how to define this view:

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

2. A user executes the following query statement to access the staff_dept_10 view:

   SELECT last_name FROM  staff_dept_10 WHERE  employee_id = 200;
   

3. OceanBase Database parses the preceding user query into the following 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 tries to merge the view definition with the user query during query rewriting.

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

5. The SQL engine of OceanBase Database generates the execution plan for the SQL statement obtained in Step 4 and executes the SQL statement.