当前位置: 开发笔记 > 数据库 > 正文

通过案例学调优之--跨库建立物化视图（MaterializedView）

作者：ssl87 | 来源：互联网 | 2017-05-12 16:09

通过案例学调优之--跨库建立物化视图（MaterializedView）应用环境：操作系统：RedHatEL55Oracle：Oracle10gR2一、物化视图概述Oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。

通过案例学调优之--跨库建立物化视图（Materialized View）应用环境：操作系统： RedHat EL55 Oracle： Oracle 10gR2 一、物化视图概述 Oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。

通过案例学调优之--跨库建立物化视图（Materialized View）

应用环境：

操作系统： RedHat EL55

Oracle： Oracle 10gR2

一、物化视图概述

Oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。

物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

物化视图可以查询表，视图和其它的物化视图。

通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。

对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。

在复制环境下，创建的物化视图通常情况下主键，rowid和子查询视图。

物化视图由于是物理真实存在的，故可以创建索引。

二、物化视图刷新

物化视图，根据不同的着重点可以有不同的分类:

1) 按刷新方式分：FAST/COMPLETE/FORCE

2) 按刷新时间的不同：ON DEMAND/ON COMMIT

3) 按是否可更新：UPDATABLE/READ ONLY

4) 按是否支持查询重写：ENABLE QUERY REWRITE/DISABLEQUERY REWRITE

默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。

注意：设置REFRESH ON COMMIT的物化视图不能访问远端对象。

在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。

物化视图有三种刷新方式：COMPLETE、FAST和 FORCE。

1) 完全刷新（COMPLETE）会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。

2) 快速刷新（FAST）采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。

对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

3) 采用FORCE方式，Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。

物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

三、创建物化视图命令

create materialized view [view_name]
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)
]
as
{创建物化视图用的查询语句}

案例分析：

本案例架构

1）在test1库上建立db link

tnsnames.ora:

[oracle@rh6 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rh6.cuug.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test1)
    )
  )
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rh6.cuug.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )

在prod库上建立tom用户，并授权
15:18:08 SYS@ prod >create user tom identified by tom;
User created.
15:18:27 SYS@ prod >grant connect,resource to tom;
Grant succeeded.
15:18:49 SYS@ prod >grant all on scott.emp1 to tom;
Grant succeeded.

在test1库上建立db link
15:12:12 SYS@ test1 >grant create database link,create public database link to tom;
Grant succeeded.

15:13:59 TOM@ test1 >create database link db_link_prod connect to tom identified by tom using 'prod';
Database link created.

测试：
15:19:10 TOM@ test1 >select * from scott.emp1@db_link_prod;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- --------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.

2）在prod库的emp1表上建立物化视图日志

15:06:49 SCOTT@ prod >create table emp1 as select * from emp;
Table created.

15:09:07 SCOTT@ prod >alter table emp1 add constraint pk_emp1 primary key(empno);
Table altered.

15:09:26 SCOTT@ prod >create materialized view log on emp1;
Materialized view log created.

3）在test1上建立物化视图

在prod库上对tom授权
15:19:07 SYS@ prod >grant select any table to tom;
Grant succeeded.

在test1库上对tom授权
15:22:11 SYS@ test1 >grant create materialized view to tom;
Grant succeeded.

TOM@ test1 >create materialized view mv1_emp1
refresh fast on demand
 as
 select * from scott.emp1@db_link_prod;
 
测试：
15:33:15 TOM@ test1 >select * from mv1_emp1;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- --------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.

4）测试物化视图数据刷新

在基表上更新数据
15:33:10 SYS@ prod >conn scott/tiger
Connected.
15:35:59 SCOTT@ prod >select * from emp1 where empno=7788;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- -------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20   
      
15:36:04 SCOTT@ prod >update emp1 set deptno=40 where empno=7788;
1 row updated.
15:36:23 SCOTT@ prod >commit;
Commit complete.

15:36:35 SCOTT@ prod >select * from emp1 where empno=7788;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- -------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    40
      
在物化视图上查看数据更新
15:35:13 TOM@ test1 >select * from mv1_emp1 where empno=7788;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- --------
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      
默认物化视图不会自动更新，需手工更新
15:38:12 TOM@ test1 >exec dbms_mview.refresh('mv1_emp1','fast');
PL/SQL procedure successfully completed.

15:38:41 TOM@ test1 >select * from mv1_emp1 where empno=7788;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- --------
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    40

至此，物化视图建立完成！

5）查看数据和日志更新信息

在test1上查看数据刷新信息
15:39:02 TOM@ test1 >select mview_name, last_refresh_date, staleness from user_mviews;
MVIEW_NAME                     LAST_REFRESH_DATE   STALENESS
------------------------------ ------------------- -------------------
MV1_EMP1                       2014-08-22 15:38:41 UNDEFINED

在prod上查看物化视图日志更新信息
15:40:41 SCOTT@ prod >select log_owner,master,log_table,PRIMARY_KEY,LAST_PURGE_DATE,LAST_PURGE_STATUS from user_mview_logs
LOG_OWNER                      MASTER                         LOG_TABLE                      PRI LAST_PURG LAST_PURGE_STATUS
------------------------------ ------------------------------ ---------
SCOTT                          EMP1                           MLOG$_EMP1                     YES 22-AUG-14                 0

6）在物化视图上创建索引

15:39:39 TOM@ test1 >CREATE index mv1_ind on mv1_emp1(ename) tablespace indx;
Index created.

16:39:15 TOM@ test1 >select index_name,index_type,table_name,BLEVEL,leaf_blocks FROM user_indexes
16:39:30   2  where index_name='MV1_IND';
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ --------------------------- ------------
MV1_IND                        NORMAL                      MV1_EMP1                                0           1

16:40:02 TOM@ test1 >select * from mv1_emp1 where ename='scott';
no rows selected
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 720877713
-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  MAT_VIEW ACCESS BY INDEX ROWID| MV1_EMP1 |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | MV1_IND  |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ENAME"='scott')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
        310  recursive calls
          0  db block gets
         44  consistent gets
          0  physical reads
          0  redo size
        695  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

案例错误信息：

建立物化视图出错

15:31:27 TOM@ test1 >create materialized view mv1_emp1

15:32:36 2 refresh fast on demand

15:32:36 3 as

15:32:36 4 select * from scott.emp1@db_link_prod;

create materialized view mv1_emp1

ERROR at line 1:

ORA-12018: following error encountered during code generation for "TOM"."MV1_EMP1"

ORA-00942: table or view does not exist

解决：

在基表所在的库上，进行授权：

15:19:07 SYS@ prod >grant select any table to tom;

Grant succeeded.

推荐阅读

join
如何在MySQL中将零值替换为先前的非零值？

本文介绍了如何在MySQL中将零值替换为先前的非零值的方法，包括使用内联查询和更新查询。同时还提供了选择正确值的方法。 ... [详细]

蜡笔小新 2023-12-14 16:59:24
数据库
使用Power Design和SQL Server进行数据库反向工程的方法详解

本文介绍了如何使用Power Design（PD）和SQL Server进行数据库反向工程的方法。通过创建数据源、选择要反向工程的数据表，PD可以生成物理模型，进而生成所需的概念模型。该方法适用于SQL Server数据库，对于其他数据库是否适用尚不确定。详细步骤和操作说明可参考本文内容。 ... [详细]

蜡笔小新 2023-12-14 16:18:06
join
使用 Ubuntu 中的 Python 获取浏览器历史记录

使用Ubuntu中的Python获取浏览器历史记录原文: ... [详细]

蜡笔小新 2023-12-14 08:57:59
数据库
PHP中的MySQL函数库及其常用函数介绍

本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-14 08:19:53
数据库
Oracle中tnsnames.ora的作用和配置方法

本文介绍了Oracle数据库中tnsnames.ora文件的作用和配置方法。tnsnames.ora文件在数据库启动过程中会被读取，用于解析LOCAL_LISTENER，并且与侦听无关。文章还提供了配置LOCAL_LISTENER和1522端口的示例，并展示了listener.ora文件的内容。 ... [详细]

蜡笔小新 2023-12-14 07:44:06
数据库
Spring特性实现接口多类的动态调用详解

本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍，以及getBeansOfType方法的应用，解决了在实际工作中遇到的接口及多个实现类的问题。同时，文章还提到了SPI使用的不便之处，并介绍了借助ApplicationContext实现需求的方法。阅读本文，你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]

蜡笔小新 2023-12-14 03:24:19
数据库
Java String与StringBuffer的区别及其应用场景

本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]

蜡笔小新 2023-12-13 19:21:06
oracle
Oracle分析函数first_value()和last_value()的用法及原理

本文介绍了Oracle分析函数first_value()和last_value()的用法和原理，以及在查询销售记录日期和部门中的应用。通过示例和解释，详细说明了first_value()和last_value()的功能和不同之处。同时，对于last_value()的结果出现不一样的情况进行了解释，并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]

蜡笔小新 2023-12-13 19:07:23
sql
MyBatis错题分析解析及注意事项

本文对MyBatis的错题进行了分析和解析，同时介绍了使用MyBatis时需要注意的一些事项，如resultMap的使用、SqlSession和SqlSessionFactory的获取方式、动态SQL中的else元素和when元素的使用、resource属性和url属性的配置方式、typeAliases的使用方法等。同时还指出了在属性名与查询字段名不一致时需要使用resultMap进行结果映射，而不能使用resultType。 ... [详细]

蜡笔小新 2023-12-13 18:40:17
insert
ASP.NET Tips: 获取插入记录的ID的方法详解

本文详细介绍了在ASP.NET中获取插入记录的ID的几种方法，包括使用SCOPE_IDENTITY()和IDENT_CURRENT()函数，以及通过ExecuteReader方法执行SQL语句获取ID的步骤。同时，还提供了使用这些方法的示例代码和注意事项。对于需要获取表中最后一个插入操作所产生的ID或马上使用刚插入的新记录ID的开发者来说，本文提供了一些有用的技巧和建议。 ... [详细]

蜡笔小新 2023-12-13 17:03:18
数据库
VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]

蜡笔小新 2023-12-13 15:15:30
insert
SpringJdbcTemplate的使用详解

本文详细介绍了Spring的JdbcTemplate的使用方法，包括执行存储过程、存储函数的call()方法，执行任何SQL语句的execute()方法，单个更新和批量更新的update()和batchUpdate()方法，以及单查和列表查询的query()和queryForXXX()方法。提供了经过测试的API供使用。 ... [详细]

蜡笔小新 2023-12-13 14:27:11
数据库
高质量SQL书写的30条建议

本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]

蜡笔小新 2023-12-13 13:24:33
sql
如何查看mysql的安装路径

本文介绍了通过mysql命令查看mysql的安装路径的方法，提供了相应的sql语句，并希望对读者有参考价值。 ... [详细]

蜡笔小新 2023-12-13 13:23:09
数据库
问题2：重新命名或者移动数据文件、日志文件到新的位置

本文讨论了在数据库打开和关闭状态下，重新命名或移动数据文件和日志文件的情况。针对性能和维护原因，需要将数据库文件移动到不同的磁盘上或重新分配到新的磁盘上的情况，以及在操作系统级别移动或重命名数据文件但未在数据库层进行重命名导致报错的情况。通过三个方面进行讨论。 ... [详细]

蜡笔小新 2023-12-13 13:02:24

ssl87

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章