通过案例学调优之--跨库建立物化视图（Materialized View）
程序员文章站
2022-04-09 14:41:14
...
通过案例学调优之--跨库建立物化视图（MaterializedView）应用环境：操作系统：RedHatEL55Oracle：Oracle10gR2一、物化视图概述Oracle的物化视图是包括一个查询
在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。
案例分析：
本案例架构
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.