【Oracle CBO优化器】视图合并View Merging技术 _complex_view_merging & _simple_view_merging

Oracle CBo优化器中 各优化模块如下:

相关参数了

 

_complex_view_merging 和 _simple_view_merging

kkqvm  contains functions for merging simple SPJ, outer-joined SPJ, and complex (distinct or group by) views.

 

其中视图合并View Merging 主要分成2种:

  1. SPJ   Merge simple views that contains Select , Project and Join only (referred as SPJ views)
  2. CVM  views containing grouping, aggregation, distinct and outer join

 

•Recursively process any views referenced by the current view
•Either completely merge the view into the referencing query block
•OR simply move the definition of the view in-line into the referencing query block
SPJ View Merging

SPJ :

SELECT ENAME, DNAME FROM EMP E1, (

SELECT ENAME, DNAME,  SAL   FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO )  V

WHERE V.SAL >10000 and V.DNAME = E1.DNAME;

 

SELECT ENAME, DNAME FROM EMP E1

, EMP E, DEPT D

WHERE E.SAL >10000 and D.DNAME = E1.DNAME

AND E.DEPTNO = D.DEPTNO

 

•When processing the transformed query, the predicate ‘SAL>10000’ can be applied before the join of the EMP and the DEPT tables. This transformation can vastly improve query performance by reducing the amount of data to be joined.

 

 

Complex View Merging

 

CVM :

 

SELECT e1.ename,
  v.max_sal
FROM emp e1,
  ( SELECT e2.deptno, MAX(e2.sal) max_sal FROM emp e2 GROUP BY e2.deptno
  ) v
WHERE e1.deptno = v.deptno
AND e1.sal      = v.max_sal;

|

SELECT e1.ename,
  MAX(e2.sal) max_sal
FROM emp e1,
  emp e2
WHERE e1.deptno = e2.deptno
GROUP BY e2.deptno,
  e1.rowid,
  e1.ename,
  e1.sal
HAVING e1.sal=MAX(e2.sal)

 

 

KKQ View Merging  SPJ, outer-joined, lateral, and complex view merging.

 

MOs note:

Complex View Merging is an enhancement that allows views containing DISTINCT or GROUP BY constructs to be
merged.
View merging is an enhancement that allows view to be re-written as operations against the base table,
removing the view code from the equation and potentially relieving restrictions that may compromise performance on
some occasions.
Note that views containing set operators (UNION, UNION ALL, INTERSECT, MINUS), CONNECT BY and the ROWNUM
pseudocolumn cannot be merged.
These restrictions are in place to avoid cases where the resultant query from the merge would be impossible or
illegal, or would result in incorrect results.
For example, if a view containing ROWNUM was merged then the value of ROWNUM would be affected by the merge
and might produce different results.
View merging behavior can be manipulated with the NO_MERGE hint (all views) or with
Parameter:COMPLEX_VIEW_MERGING (underscore) (complex views).
From Oracle 9i the default behaviour is for complex view merging to be enabled (_COMPLEX_VIEW_MERGING = TRUE
)
The following are some examples of queries showing them being merged. Consider the following query:
select e.empno, V.memp
from
(select empno, max(empno) as memp from emp group by empno) V,
emp e
where V.empno = e.empno
and e.ename = 'SMITH';
The query is a join between an inline view called "V" and the "emp" table. With complex view merging off the plan is:
alter session set "_complex_view_merging" = false;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=34)
1 0 HASH JOIN (Cost=6 Card=1 Bytes=34)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=1 Bytes=8)
3 1 VIEW (Cost=3 Card=14 Bytes=364)
4 3 SORT (GROUP BY) (Cost=3 Card=14 Bytes=42)
5 4 INDEX (FULL SCAN) OF 'EMPIX' (NON-UNIQUE) (Cost=2 Card=14 Bytes=42)
In the explain plan it can be seen that the join is between the outer table "EMP" and a VIEW.
The VIEW keyword means that the view in the query has not been merged and the 'group by' condition is applied
within this view.
If complex view merging is turned on, then the view can be merged:

alter session set "_complex_view_merging" = true;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=18)
1 0 SORT (GROUP BY) (Cost=4 Card=1 Bytes=18)
2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=18)
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=1 Bytes=15)
4 2 INDEX (RANGE SCAN) OF 'EMPIX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=3)
Note that the join is now between the 2 base tables whereas before it was between the outer table and a view
containing the inner table.
The VIEW keyword disappears completely because the view has been removed.
The advantages of this (among other things) are
the tables can now be directly joined to each other which may result in the availability of better access methods
the 'group by' can now be applied AFTER the join has occurred.
Since the join may restrict the amount of data presented to the group by,
this may mean that there is less data to group and thus may improve performance.
Also note that complex view merging may be applied to views that have been created as a result of subquery
unnesting if the original subquery is uncorrelated.

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569