Recently I was reading a buddy's optimization SQL and used several special hint, but I was always puzzled by this, so I did some tests on this issue (refer to some materials)
1. no_unnest, unnest
Unnest is called the subquery expansion. As the name suggests, it is not to let the subquery nest in itself.
Therefore, the un_unnest double negation means yes, that is, the subquery is not expanded, so that it is nested in it.
If a view or subquery appears in the WHERE clause of a query. Therefore, before Oracle's CBO parses this SQL statement, it needs to convert the view or subquery to "open "~ This operation is called unnest ~ Then, you can join tables in the primary query and subquery to generate an execution plan. You can use the hint no_unnest to tell CBO not to "open" view or subquery ~ In this way, the view or subquery can only be parsed as an independent query. The tables in the view or subquery cannot be connected to the tables in the primary query.
Now we have a simple experiment:
SQL> select count (*) from dba_objects;
Count (*)
----------
60494
Here, the subquery is automatically expanded (unnest), that is, tmp_liuhc_1 and tmp_liuhc_2 hash join are together.
Next, if we do not want tmp_liuhc_2 to expand, we want to finish it separately and then perform a filter operation with external queries.
Then we add hint no_unnest:
Therefore, when should we use no_unnest to filter subqueries after they are executed independently?
First, the returned result set of the subquery should be small, and then the input distinct value of the peripheral query should be small.
2. push_subq
If no_unnest is used to prevent the subquery from being expanded and completed independently, push_subq is used to make the subquery join first.
Therefore, this hint actually controls the join order. SQL> Create Table tmp_liuhc_3 as select * From dba_objects;
Table created
SQL> Create Table tmp_liuhc_4 as select * From dba_objects;
Table created
SQL> analyze table tmp_liuhc_3 compute statistics;
Table analyzed
SQL> analyze table tmp_liuhc_4 compute statistics;
Table analyzed
SQL> Create index idx_tmp_liuhc_3 on tmp_liuhc_3 (object_id );
Index created
3. push_predThe difference between mergeable view and unmergeable view should be clarified first for the hint push_pred.
This is clearly explained in concept:
Mergeable and unmergeable viewsthe optimizer can merge a view into a referencing query block when the view has one or more base tables, provided the view does not
Contain:
Set operators (Union, Union all, intersect, minus)
A connect by clause
A rownum pseudo Column
- Aggregate functions (avg, Count, Max, Min, sum) in the select list
When a view contains one of the following structures, it can be merged into a referencing query block only if
Complex view MergingIs Enabled (as described below ):
A group by clause
- A distinct operator in the select list
View merging is not possible for a view that has multiple base tables if it is on the right side of an outer join. if a view on the right side of an outer join has only one base table, however, the optimizer can use complex view merging even if an expression
In the view can return a non-null value for a null. See
"Views in outer joins" for more information.
At the end, we find that an unmergeable view is on the right side of the outer join.
In this case, the well-known merge hint is also ineffective. For such a simple query, we can see that the predicate tmp_liuhc_3.object_name = cursor is merge into the view, so I put tmp_liuhc_view to the right of the Outer Join. This is the tmp_liuhc_view which belongs to the unmergeable, by default, the optimizer cannot add the predicate merge to the tmp_liuhc_view. Therefore, the tmp_liuhc_view command is executed separately:
Then we use the hint push_pred force optimizer to insert the predicge into the view. We can see that "view pushed predicate": Although merge hint has the same effect, for this unmergeable view, merge hint is invalid. It can be seen that merge hint is powerless for the view on the right of the outger join operation.
To sum up, three hints that are easy to confuse:
No_unnest/unnest is used to determine whether to expand the subquery, push_subq is used to query the connection sequence of the subquery, and push_pred is used to use external query predicates for the unmergeable view.
