Oracle查询转换（一）简单视图合并

 

视图的定义及分类参考：http://blog.itpub.net/28536251/viewspace-1871039/
如果目标SQL中包含了视图，且该视图为一个简单视图，则优化器会将视图拆开，直接使用视图的基表来生成执行计划，故在执行计划中将看不到视图的名字了。

 

创建一个简单视图：
SQL> create view employees_50_vw as select employee_id,last_name,job_id,salary,commission_pct,department_id from employees where department_id=50;

View created.

SQL> select employee_id,last_name,salary,department_name from employees_50_vw e,departments d where e.department_id=d.department_id;

45 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1492013603

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    45 |  1575 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |    45 |  1575 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMPLOYEES   |    45 |   855 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

 

可以看到执行计划中没有视图employees_50_vw的名字，表示优化器已经做了视图合并。可以使用NO_MERGE Hint来禁止优化器进行视图合并：

 

SQL> select /*+ no_merge(e) */employee_id,last_name,salary,department_name from employees_50_vw e,departments d where e.department_id=d.department_id;

45 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3057875378

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |    45 |  3105 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |                 |    45 |  3105 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS     |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK      |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                 |    45 |  2385 |     4  (25)| 00:00:01 |
|   5 |    VIEW                      | EMPLOYEES_50_VW |    45 |  2385 |     3   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL        | EMPLOYEES       |    45 |   855 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

 

可以看到执行计划中出现了视图employees_50_vw的名字，则表示优化器没有对视图进行视图合并。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28536251/viewspace-2140649/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28536251/viewspace-2140649/