有关物化视图及查询重写功能

物化视图的创建及刷新方式介绍网上有很多，就不重复了，这里主要通过实验来验证查询重写功能。

SQL> desc hr.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

 

SQL> desc hr.jobs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_ID                                    NOT NULL VARCHAR2(10)
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 MIN_SALARY                                         NUMBER(6)
 MAX_SALARY                                         NUMBER(6)

未创建物化视图的执行计划

SQL> select a.first_name,a.email,b.job_title from hr.employees a,hr.jobs b where a.job_id=b.job_id;

107 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3851899397

--------------------------------------------------------------------------------
----------

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------
----------

|   0 | SELECT STATEMENT             |           |   107 |  5457 |     4   (0)|
00:00:01 |

|   1 |  NESTED LOOPS                |           |   107 |  5457 |     4   (0)|
00:00:01 |

|   2 |   TABLE ACCESS FULL          | EMPLOYEES |   107 |  2568 |     3   (0)|
00:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID| JOBS      |     1 |    27 |     1   (0)|
00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | JOB_ID_PK |     1 |       |     0   (0)|
00:00:01 |

--------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."JOB_ID"="B"."JOB_ID")

创建物化试图：

SQL> conn hr/hr
Connected.
SQL> create materialized view hr.hr_test build immediate
  2  enable query rewrite as
  3  select a.first_name,a.email,b.job_title from hr.employees a,hr.jobs b where a.job_id=b.job_id;

Materialized view created.

SQL>
SQL>
SQL> set autot trace
SQL> select a.first_name,a.email,b.job_title from hr.employees a,hr.jobs b where a.job_id=b.job_id;

107 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1694931380

--------------------------------------------------------------------------------
--------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
me     |

--------------------------------------------------------------------------------
--------

|   0 | SELECT STATEMENT             |         |   107 |  4815 |     3   (0)| 00
:00:01 |

|   1 |  MAT_VIEW REWRITE ACCESS FULL| HR_TEST |   107 |  4815 |     3   (0)| 00
:00:01 |

--------------------------------------------------------------------------------
--------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
         17  consistent gets
          1  physical reads
          0  redo size
       5333  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed
可以看到查询已经被自动重写了，只扫描了物化视图。

说明：查询重写功能是优化器自动选择的，与你执行查询的语句无关。必须使用CBO的优化器，且将query_rewrite_enabled参数设置为true。可以使用hint：norewrite或rewrite引导是否使用查询重写。

 

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

转载于:http://blog.itpub.net/7969839/viewspace-701035/