视图的定义及分类参考:http://blog.itpub.net/28536251/viewspace-1871039/
如果目标SQL中包含了视图,且该视图为一个复杂视图,则优化器会评估视图合并的成本,如果成本低于原SQL的成本,才会做视图合并。
创建一个复杂视图:
SQL> create view employees_vw as select department_id,sum(salary) sal_sum from employees group by department_id;
View created.
SQL> select d.department_name,e.sal_sum from employees_vw e,departments d where e.department_id=d.department_id and department_name='IT';
Execution Plan
----------------------------------------------------------
Plan hash value: 3808327043
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 224 | 5 (20)| 00:00:01 |
| 1 | HASH GROUP BY | | 8 | 224 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 280 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 280 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 21 | 3 (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 | 70 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
可以看到执行计划中没有视图employees_vw的名字,且将视图中的group by放到了两个表连接之后执行,表示优化器已经做了视图合并。可以使用NO_MERGE Hint来禁止优化器进行视图合并:
SQL> select /*+ no_merge(e) */d.department_name,e.sal_sum from employees_vw e,departments d where e.department_id=d.department_id and department_name='IT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1313696113
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 29 | 5 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | EMPLOYEES_VW | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | SORT AGGREGATE | | 1 | 7 | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 70 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
可以看到执行计划中出现了视图EMPLOYEES_VW的名字,则表示优化器没有对视图进行视图合并。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-2140720/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2140720/