通过案例学调优之--跨库建立物化视图（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.
将用户添加到MySQL：完整的教程May 12, 2025 am 12:14 AM掌握添加MySQL用户的方法对于数据库管理员和开发者至关重要，因为它确保数据库的安全性和访问控制。1)使用CREATEUSER命令创建新用户，2)通过GRANT命令分配权限，3)使用FLUSHPRIVILEGES确保权限生效，4)定期审计和清理用户账户以维护性能和安全。
掌握mySQL字符串数据类型：varchar vs.文本与charMay 12, 2025 am 12:12 AMchosecharforfixed-lengthdata，varcharforvariable-lengthdata，andtextforlargetextfield.1）chariseffity forconsistent-lengthdatalikecodes.2）varcharsuitsvariable-lengthdatalikenames，ballancingflexibilitibility andperformance.3）
MySQL：字符串数据类型和索引：最佳实践May 12, 2025 am 12:11 AM在MySQL中处理字符串数据类型和索引的最佳实践包括：1)选择合适的字符串类型，如CHAR用于固定长度，VARCHAR用于可变长度，TEXT用于大文本；2)谨慎索引，避免过度索引，针对常用查询创建索引；3)使用前缀索引和全文索引优化长字符串搜索；4)定期监控和优化索引，保持索引小巧高效。通过这些方法，可以在读取和写入性能之间取得平衡，提升数据库效率。
mysql：如何远程添加用户May 12, 2025 am 12:10 AMToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori
MySQL字符串数据类型的最终指南：有效的数据存储May 12, 2025 am 12:05 AMtostorestringsefliceflicyInmySql，ChooSetherightDataTypeBasedyOrneOrneEds：1）USEcharforFixed-LengthStstringStringStringSlikeCountryCodes.2）UseVarcharforvariable-lengtthslikenames.3）USETEXTCONTENT.3）
MySQL：字符串数据类型可用哪些字符集？May 10, 2025 am 12:07 AMmysqloffersvariouscharactersetsforstringdatatypes：1）latin1 forwesterneuropeanlanguages，2）utf8 formultingualsupport，3）utf8mb4f OREXTEDENDENDENENICODECLUDINGEMOJIS，4）UCS2FORIXED-WIDTHENCODING，5）assiiforbasiclatin.ChoosideStherightStetSetensensersdaintegrity
mysql：斑点流比存储它们更好吗？May 10, 2025 am 12:06 AM流式传输BLOB确实比直接存储更好，因为它能减少内存使用和提高性能。1）通过逐步读取和处理文件，避免了数据库膨胀和性能下降。2）流式传输需要更复杂的代码逻辑，且可能增加I/O操作次数。
MySQL字符串类型：存储，性能和最佳实践May 10, 2025 am 12:02 AMmySqlStringTypesimpactStorageAndPerformanCeaseAsfollows：1）长度，始终使用theSamestoragespace，whatcanbefasterbutlessspace-felfficity.2）varCharisvariable varcharisvariable length，morespace-morespace-morespace-effficitybuteftife buteftife butfority butfority textifforlyslower.3）
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
热工具
适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。
记事本++7.3.1
好用且免费的代码编辑器
EditPlus 中文破解版
体积小，语法高亮，不支持代码提示功能
MinGW - 适用于 Windows 的极简 GNU
这个项目正在迁移到osdn.net/projects/mingw的过程中，你可以继续在那里关注我们。MinGW：GNU编译器集合（GCC）的本地Windows移植版本，可自由分发的导入库和用于构建本地Windows应用程序的头文件；包括对MSVC运行时的扩展，以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。
ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境
