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种：
- SPJ Merge simple views that contains Select , Project and Join only (referred as SPJ views)
- 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.
Leave a Reply