Oracle CBO Several basic query conversion detailed _oracle

In the execution plan development process, the transformation and the choice has this different task; in fact, after a query finishes syntax and permission checks, the first step that is called "query transformation" occurs, where a series of query blocks are converted and then "optimized" (the optimizer calculates costs for different plans in order to determine the final execution plan and chooses the final execution plan).

We know that the query block is distinguished by the Select keyword, and the way the query is written determines the relationship between the query blocks, which are usually embedded in another query block or in some way connected to it; for example:

Copy Code code as follows:

SELECT * FROM Employees where department_id into (select department_id from departments)

is a nested query block, but their purpose is to explore if the change of query writing will provide a better query plan.

The steps for this kind of query conversion are completely transparent to the execution user, to know that the converter may completely rewrite your SQL statement structure without changing the query result set, it is necessary to reassess the psychological expectations of our query statements, although this conversion is generally a good thing, To get a better and more efficient execution plan.

Let's now discuss several basic transformations:

1. View Merging
2. Subquery Solution Nesting
3. Pre-PUSH predicate
4. Materialized view query rewrite

One, view merge

This approach is easier to understand, it expands the embedded view into a separate processing query block, or merges it with the remainder of the query into a total execution plan, and the converted statement basically does not contain a view.

A view merge usually occurs when the predicate of an external query block includes:

1, the column that can be used in the index of another query block
2, the column that can be used in the partition truncation of another query block
3, in a join view can limit the number of rows returned to the condition

In this case, the view does not always have its own subquery plan, which is analyzed in advance and typically merged with other parts of the query to gain performance gains, as the following example.

Copy Code code as follows:

Sql> Set Autotrace traceonly explain


--Make a view merge


Sql> SELECT * from EMPLOYEES A,


2 (select department_id from EMPLOYEES) B_view


3 Where a.department_id = B_view. DEPARTMENT_ID (+)


4 and A.salary > 3000;

Execution Plan
----------------------------------------------------------
Plan Hash value:1634680537

----------------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


----------------------------------------------------------------------------------------


| 0 |                   SELECT STATEMENT |  |   3161 |     222k| 3 (0) | 00:00:01 |


|  1 |                   NESTED LOOPS outer|  |   3161 |     222k| 3 (0) | 00:00:01 |


|* 2 | TABLE ACCESS full|   EMPLOYEES |  103 |     7107 | 3 (0) | 00:00:01 |


|* 3 | INDEX RANGE SCAN |    Emp_department_ix |    31 |     93 | 0 (0) | 00:00:01 |


----------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

2-filter ("A".) SALARY ">3000)
3-access ("A".) department_id "=" department_id "(+))

--use No_merge to prevent views from being overridden
Sql> SELECT * from EMPLOYEES A,
2 (select/*+ no_merge */department_id from EMPLOYEES) B_view
3 Where a.department_id = B_view. DEPARTMENT_ID (+)
4 and A.salary > 3000;

Execution Plan
----------------------------------------------------------
Plan Hash value:1526679670

-----------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


-----------------------------------------------------------------------------------


| 0 |           SELECT STATEMENT |  |   3161 |     253k| 7 (15) | 00:00:01 |


|* 1 |           HASH JOIN Right outer|  |   3161 |     253k| 7 (15) | 00:00:01 |


|   2 |           VIEW |   |  107 |     1391 | 3 (0) | 00:00:01 |


|    3 | TABLE ACCESS Full |   EMPLOYEES |   107 |     321 | 3 (0) | 00:00:01 |


|* 4 | TABLE ACCESS Full |   EMPLOYEES |  103 |     7107 | 3 (0) | 00:00:01 |


-----------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-access ("A".) department_id "=" B_view "." department_id "(+))
4-filter ("A".) SALARY ">3000)

In some cases, view merging is prohibited or restricted, which happens if the parse function, aggregate function, set operation (such as Union,intersect,minux), the ORDER BY clause, and any one of the rownum are used in a query block; We can still use the/*+ merge (v)/hint to force the use of view merging, but it is important to ensure that the returned result set is consistent!!! The following example:

Copy Code code as follows:

Sql> set Autotrace on


--Using the aggregate function Avg causes the view merge to fail


Sql> SELECT e1.last_name, E1.salary, v.avg_salary


2 from Hr.employees E1,


3 (SELECT department_id, avg (Salary) Avg_salary


4 from Hr.employees E2


5 GROUP by department_id) v


6 WHERE e1.department_id = v.department_id and e1.salary > v.avg_salary;

Execution Plan
----------------------------------------------------------
Plan Hash value:2695105989

----------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


----------------------------------------------------------------------------------


| 0 |           SELECT STATEMENT |    |   17 |     697 | 8 (25) | 00:00:01 |


|* 1 |           HASH JOIN |    |   17 |     697 | 8 (25) | 00:00:01 |


|   2 |           VIEW |    |   11 |     286 | 4 (25) | 00:00:01 |


|    3 |           HASH GROUP by |    |    11 |     77 | 4 (25) | 00:00:01 |


|     4 | TABLE ACCESS full|   EMPLOYEES |   107 |     749 | 3 (0) | 00:00:01 |


|   5 | TABLE ACCESS Full |   EMPLOYEES |  107 |     1605 | 3 (0) | 00:00:01 |


----------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-access ("E1".) department_id "=" V "." department_id ")
Filter ("E1".) SALARY ">" V "". Avg_salary ")

--Use/*+ merge (v)/To force view merging
Sql> SELECT/*+ MERGE (v) */E1.last_name, e1.salary, v.avg_salary
2 from Hr.employees E1,
3 (SELECT department_id, avg (Salary) Avg_salary
4 from Hr.employees E2
5 GROUP by department_id) v
6 WHERE e1.department_id = v.department_id and e1.salary > v.avg_salary;

Execution Plan
----------------------------------------------------------
Plan Hash value:3553954154

----------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


----------------------------------------------------------------------------------


| 0 |           SELECT STATEMENT |   |  165 |     5610 | 8 (25) | 00:00:01 |


|* 1 |           FILTER |       |       |            |          | |


|   2 |           HASH GROUP by |   |  165 |     5610 | 8 (25) | 00:00:01 |


|* 3 |           HASH JOIN |  |   3296 |     109k| 7 (15) | 00:00:01 |


|     4 | TABLE ACCESS full|   EMPLOYEES |  107 |     2889 | 3 (0) | 00:00:01 |


|     5 | TABLE ACCESS full|   EMPLOYEES |   107 |     749 | 3 (0) | 00:00:01 |


----------------------------------------------------------------------------------

Two, the subquery to solve the nesting

The most typical is that the subquery is transformed into a table connection, and the main difference between it and the view merge is that its subquery is located in the WHERE clause, which is detected by the converter for nesting.

Here is an example of a subquery ==> table join:

Copy Code code as follows:

Sql> Select employee_id, last_name, salary, department_id


2 from Hr.employees


3 where department_id in


4 (Select department_id


5 from hr.departments where location_id > 1700);

Execution Plan
----------------------------------------------------------
Plan Hash value:432925905

---------------------------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


---------------------------------------------------------------------------------------------------


| 0 |                   SELECT STATEMENT |    |   34 |     884 | 4 (0) | 00:00:01 |


|  1 |                   NESTED LOOPS |       |       |            |          | |


|   2 |                   NESTED LOOPS |    |   34 |     884 | 4 (0) | 00:00:01 |


|    3 | TABLE ACCESS by INDEX rowid|     Departments |    4 |     28 | 2 (0) | 00:00:01 |


|* 4 | INDEX RANGE SCAN |     Dept_location_ix |       4 |     | 1 (0) | 00:00:01 |


|* 5 | INDEX RANGE SCAN |    Emp_department_ix |       10 |     | 0 (0) | 00:00:01 |


|   6 | TABLE ACCESS by INDEX ROWID |    EMPLOYEES |   10 |     190 | 1 (0) | 00:00:01 |


---------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

4-access ("location_id" >1700)
5-access ("department_id" = "department_id")

--Use/*+ no_unnest/force to generate execution plans for subqueries separately
Sql> Select employee_id, last_name, salary, department_id
2 from Hr.employees
3 where department_id in
4 (select/*+ no_unnest */department_id
5 from hr.departments where location_id > 1700);

Execution Plan
----------------------------------------------------------
Plan Hash value:4233807898

--------------------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


--------------------------------------------------------------------------------------------


| 0 |             SELECT STATEMENT |    |   10 |    190 | 14 (0) | 00:00:01 |


|* 1 |             FILTER |       |       |            |          | |


|   2 | TABLE ACCESS Full |   EMPLOYEES |  107 |     2033 | 3 (0) | 00:00:01 |


|* 3 | TABLE ACCESS by INDEX rowid|     Departments |     1 |     7 | 1 (0) | 00:00:01 |


|* 4 | INDEX UNIQUE SCAN |     DEPT_ID_PK |       1 |     | 0 (0) | 00:00:01 |


--------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-filter (EXISTS SELECT/*+ no_unnest/0 from "HR". " Departments "
"Departments" WHERE "department_id" =:b1 and "location_id" >1700))
3-filter ("location_id" >1700)
4-access ("department_id" =:B1)

You can see that a query that does not perform a subquery nesting only uses filter to match two tables, and the first query of the predicate information does not have the slightest change, which means that for each row of the 107 rows returned in the Employees table, a subquery needs to be executed once. Although there is an optimization of subquery caches in Oracle, we cannot judge the pros and cons of these two schemes, but the disadvantages of nested loops,filter operations are obvious.

If you include related subqueries, the solution nesting process typically converts the related subquery into a nested view and then joins the table X in the main query, such as:

Copy Code code as follows:

Sql> Select outer.employee_id, Outer.last_name, Outer.salary, outer.department_id


2 from Hr.employees outer


3 where Outer.salary >


4 (select AVG (inner.salary)


5 from Hr.employees Inner


6 where inner.department_id = outer.department_id);

Execution Plan
----------------------------------------------------------
Plan Hash value:2167610409

----------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


----------------------------------------------------------------------------------


| 0 |           SELECT STATEMENT |    |   17 |     765 | 8 (25) | 00:00:01 |


|* 1 |           HASH JOIN |    |   17 |     765 | 8 (25) | 00:00:01 |


|   2 | VIEW |    Vw_sq_1 |   11 |     286 | 4 (25) | 00:00:01 |


|    3 |           HASH GROUP by |    |    11 |     77 | 4 (25) | 00:00:01 |


|     4 | TABLE ACCESS full|   EMPLOYEES |   107 |     749 | 3 (0) | 00:00:01 |


|   5 | TABLE ACCESS Full |   EMPLOYEES |  107 |     2033 | 3 (0) | 00:00:01 |


----------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-access ("item_1" = "OUTER"). department_id ")
Filter ("OUTER".) SALARY ">" AVG (INNER. SALARY) ")

The query above is to convert a subquery into a view to hash join with the main query, and the converted query actually looks like this:

Copy Code code as follows:

Sql> Select outer.employee_id, Outer.last_name, Outer.salary, outer.department_id
2 from Hr.employees outer,
3 (select Department_id,avg (Salary) Avg_sal from Hr.employees Group by department_id) inner
4 Where inner.department_id = outer.department_id and outer.salary > inner.avg_sal;

In fact, the execution plan of these two statements is also consistent

Third, the predicate pushes forward

The predicate is propelled from an internal query block into a query block that cannot be merged, allowing the predicate to be selected earlier, filtering out unwanted rows of data earlier, and improving efficiency, which can also be used to allow some indexes to be used.

Copy Code code as follows:

--Example of predicate forward push


Sql> Set Autotrace traceonly explain


Sql> SELECT e1.last_name, E1.salary, v.avg_salary


2 from Hr.employees E1,


3 (SELECT department_id, avg (Salary) Avg_salary


4 from Hr.employees E2


5 GROUP by department_id) v


6 WHERE e1.department_id = v.department_id


7 and E1.salary > V.avg_salary


8 and e1.department_id = 60;

Execution Plan
----------------------------------------------------------
Plan Hash value:3521487559

-----------------------------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


-----------------------------------------------------------------------------------------------------


| 0 |                   SELECT STATEMENT |     |    1 |     41 | 3 (0) | 00:00:01 |


|  1 |                   NESTED LOOPS |       |       |            |          | |


|   2 |                   NESTED LOOPS |     |    1 |     41 | 3 (0) | 00:00:01 |


|    3 |                   VIEW |     |    1 |     26 | 2 (0) | 00:00:01 |


|     4 |                   HASH GROUP by |     |     1 |     7 | 2 (0) | 00:00:01 |


|      5 | TABLE ACCESS by INDEX rowid|     EMPLOYEES |    5 |     35 | 2 (0) | 00:00:01 |


|* 6 | INDEX RANGE SCAN |     Emp_department_ix |       5 |     | 1 (0) | 00:00:01 |


|* 7 | INDEX RANGE SCAN |     Emp_department_ix |       5 |     | 0 (0) | 00:00:01 |


|* 8 | TABLE ACCESS by INDEX ROWID |     EMPLOYEES |    1 |     15 | 1 (0) | 00:00:01 |


-----------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

6-access ("department_id" =60)
7-access ("E1".) department_id "=60)
8-filter ("E1".) SALARY ">" V "". Avg_salary ")

--Do not push the predicate forward
Sql> SELECT e1.last_name, E1.salary, v.avg_salary
2 from Hr.employees E1,
3 (SELECT department_id, avg (Salary) Avg_salary
4 from Hr.employees E2
5 WHERE rownum > 1-rownum equals both No_merge and no_push_pred prompts, which disables both view merging and predicate push
6 GROUP by department_id) v
7 WHERE e1.department_id = v.department_id
8 and E1.salary > V.avg_salary
9 and e1.department_id = 60;

Execution Plan
----------------------------------------------------------
Plan Hash value:3834222907

--------------------------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


--------------------------------------------------------------------------------------------------


| 0 |                   SELECT STATEMENT |     |   3 |     123 | 7 (29) | 00:00:01 |


|* 1 |                   HASH JOIN |     |   3 |     123 | 7 (29) | 00:00:01 |


|   2 | TABLE ACCESS by INDEX rowid|     EMPLOYEES |    5 |     75 | 2 (0) | 00:00:01 |


|* 3 | INDEX RANGE SCAN |     Emp_department_ix |       5 |     | 1 (0) | 00:00:01 |


|* 4 |                   VIEW |    |   11 |     286 | 4 (25) | 00:00:01 |


|    5 |                   HASH GROUP by |    |    11 |     77 | 4 (25) | 00:00:01 |


|     6 |                   COUNT |       |       |            |          | |


|* 7 |                   FILTER |       |       |            |          | |


|       8 | TABLE ACCESS Full |   EMPLOYEES |   107 |     749 | 3 (0) | 00:00:01 |


--------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-access ("E1".) department_id "=" V "." department_id ")
Filter ("E1".) SALARY ">" V "". Avg_salary ")
3-access ("E1".) department_id "=60)
4-filter ("V".) department_id "=60)
7-filter (rownum>1)

Comparing the two queries above, you can see in the first query, the DEPARTMENT_ID=60 predicate was pushed into view V, so that the internal view query needed only the average salary of the department number 60, and in the second query, the average salary for each department would be calculated. Then in the connection with the external query to use the DEPARTMENT_ID=60 condition filter, relatively here in order to wait for the application of predicate conditions, the query did more work.

Iv. using materialized views for query rewriting

When the query rewrite function is turned on for materialized views, the CBO optimizer evaluates the cost of access for the corresponding query to the base table and materialized views, and if the optimizer considers the query results to be more efficient from the materialized view, it automatically chooses to perform the materialized view, otherwise the query plan is generated for the base table.

Or look at the chestnuts:

Copy Code code as follows:

Sql> Set Autotrace traceonly explain


Sql> Select Department_id,count (employee_id) from EMPLOYEES Group by department_id;

Execution Plan
----------------------------------------------------------
Plan Hash value:1192169904

--------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


--------------------------------------------------------------------------------


| 0 |           SELECT STATEMENT |    |    11 |     33 | 4 (25) | 00:00:01 |


|  1 |           HASH GROUP by |    |    11 |     33 | 4 (25) | 00:00:01 |


|   2 | TABLE ACCESS full|   EMPLOYEES |   107 |     321 | 3 (0) | 00:00:01 |


--------------------------------------------------------------------------------

--Create materialized view logs
Sql> create materialized view Log on EMPLOYEES with sequence,
2 rowID (employee_id,department_id) including new values;

Materialized view log created.

--Create materialized views and specify query rewrite functionality
Sql> Create materialized View mv_t
2 Build immediate refresh fast on commit
3 Enable query rewrite as
4 Select Department_id,count (employee_id) from EMPLOYEES Group by department_id;

Materialized view created.

Sql> Select Department_id,count (employee_id) from EMPLOYEES Group by department_id;

Execution Plan
----------------------------------------------------------
Plan Hash value:1712400360

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |    |   12 |     312 | 3 (0) | 00:00:01 |
|  1 | Mat_view REWRITE ACCESS full|    mv_t |   12 |     312 | 3 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

You can see that in the second query, although it is the specified query Employees table, the optimizer automatically chooses the execution path of the materialized view because it determines that the materialized view already records the result set data required by the current query, and that direct access to materialized views can be more efficient.

It is noteworthy that the materialized view query rewrite here automatically occurs, as well as the use of/*+ rewrite (mv_t)/hint to force a query rewrite to occur.

Summarize:

Although the optimizer rewrote our query structure in the context of user transparency, but usually this is based on the CBO optimization model of the decision is more efficient choice, which is what we expect, but also provides us with a learning method, that is, in the process of writing SQL statements, always consider the role of the optimizer.