Oracle：物化视图（初阶·1）
物化视图，属于Oracle Advanced Replication（高级复制）的一部分。它能用于复制数据，并提高查询操作的性能。它是数据库对象的一种。
物化视图被创建的时候，会自动的生成一张与物化视图同名的数据库表，该表被称为MV基础表。MV基础表用于存储物化视图存储的SQL（MV SQL）查询出来的结果。
在创建物化视图的时候，对于数据，可以选择立即生成数据；或者，延迟生成数据。延迟生成数据，可以在物化视图创建完成后，于刷新阶段拿到数据。
物化视图有两种刷新方式：
完全刷新。
快速刷新。
基于不同的刷新方式，需要的物化视图的组件也是不同的。
在完全刷新中，物化视图不需要额外的支持，它会将MV中的内容彻底删除，然后使用MV SQL新刷新的结果。
而快速刷新因为仅仅对最近一次刷新进行DML修改，所以它需要依据MV日志来跟踪MV基础表DML更改的情况而判断快速刷新的时候需要具体作何操作。
——————————————————
准备数据：
A：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
SQL> alter user scott identified by oracle account unlock; User altered. SQL> conn scott/oracle Connected. SQL> select table_name from user_Tables; TABLE_NAME ------------------------------ DEPT EMP BONUS SALGRADE DEPT_2 SQL> SQL> create table sales ( 2 sales_id number, 3 sales_amt number, 4 region_id number, 5 sales_dtt date, 6 constraint sales_pk primary key (sales_id) 7 ); Table created. SQL> desc sales; Name Null? Type ----------------------------------------- -------- ---------------------------- SALES_ID NOT NULL NUMBER SALES_AMT NUMBER REGION_ID NUMBER SALES_DTT DATE SQL> select * from sales; no rows selected SQL> insert into sales values(1,101,10,sysdate-10); 1 row created. SQL> insert into sales values(2,511,20,sysdate-20); 1 row created. SQL> insert into sales values(3,11,30,sysdate-30); 1 row created. SQL> commit; Commit complete. SQL> |
上面的代码准备了一张用于存放销售数据的表“sales”，通过下列的SQL可以查看日常销售情况的报表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select * from sales; SALES_ID SALES_AMT REGION_ID SALES_DTT ---------- ---------- ---------- --------- 1 101 10 30-NOV-15 2 511 20 20-NOV-15 3 11 30 10-NOV-15 SQL> select sum(sales_amt) sales_amt,sales_dtt from sales group by sales_dtt; SALES_AMT SALES_DTT ---------- --------- 11 10-NOV-15 101 30-NOV-15 511 20-NOV-15 SQL> |
B：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> SQL> select d.deptno,d.dname,e.empno,e.ename from emp e,dept d where e.deptno = d.deptno; DEPTNO DNAME EMPNO ENAME ---------- -------------- ---------- ---------- 10 ACCOUNTING 7782 CLARK 10 ACCOUNTING 7839 KING 10 ACCOUNTING 7934 MILLER 20 RESEARCH 7566 JONES 20 RESEARCH 7902 FORD 20 RESEARCH 7876 ADAMS 20 RESEARCH 7369 SMITH 20 RESEARCH 7788 SCOTT 30 SALES 7521 WARD 30 SALES 7844 TURNER 30 SALES 7499 ALLEN DEPTNO DNAME EMPNO ENAME ---------- -------------- ---------- ---------- 30 SALES 7900 JAMES 30 SALES 7698 BLAKE 30 SALES 7654 MARTIN 14 rows selected. SQL> select d.dname,count(e.ename) from emp e,dept d where e.deptno = d.deptno group by d.dname; DNAME COUNT(E.ENAME) -------------- -------------- ACCOUNTING 3 RESEARCH 5 SALES 6 SQL> |
一、物化视图诞生前：
对日常销售情况的结果集的查询可能会被多次执行，而每次执行将会消耗大量的数据库资源。
所以，为了降低查询该结果集的成本，可以单独创建一个表（实表）来存储这些记录。（业务上，自然人逻辑的规则）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> create table sales_daily as 2 select sum(sales_amt) sales_amt,sales_dtt from sales group by sales_dtt; Table created. SQL> desc sales_daily; Name Null? Type ----------------------------------------- -------- ---------------------------- SALES_AMT NUMBER SALES_DTT DATE SQL> select * from sales_daily; SALES_AMT SALES_DTT ---------- --------- 11 10-NOV-15 101 30-NOV-15 511 20-NOV-15 SQL> |
这样，只需要定期更新（刷新）转储表“sales_daily”的数据，即可降低每次执行select的成本（资源消耗）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SQL> select count(*) from sales_daily; COUNT(*) ---------- 3 SQL> delete from sales_daily; 3 rows deleted. SQL> select count(*) from sales_daily; COUNT(*) ---------- 0 SQL> SQL> insert into sales_daily 2 select sum(sales_amt) sales_amt,sales_dtt from sales group by sales_dtt; 3 rows created. SQL> select count(*) from sales_daily; COUNT(*) ---------- 3 SQL> select * from sales_daily; SALES_AMT SALES_DTT ---------- --------- 11 10-NOV-15 101 30-NOV-15 511 20-NOV-15 SQL> |
上面的“delete from sales_daily”也可以用“truncate table xxx”替代。
而上面的这个过程清晰的描述了在Oracle的物化视图中，“刷新”功能在做的事情。
比起上面的人为操作，物化视图可以是数据刷新自动化，并更高幅度的增强其功能。
二、物化视图诞生后：
创建物化视图的权限要求：
与物化视图相关的权限是系统权限，而不是对象权限，所以，需要获得权限方面的信息，需要查询：dba_sys_privs
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select distinct privilege from dba_sys_privs where privilege like '%MATERIALIZED%'; PRIVILEGE ---------------------------------------- DROP ANY MATERIALIZED VIEW CREATE MATERIALIZED VIEW ALTER ANY MATERIALIZED VIEW CREATE ANY MATERIALIZED VIEW SQL> |
由于，创建物化视图的时候会自动的创建一个物化视图基础表，所以要成功的创建物化视图，你需要有以下权限：
1. CREATE MATERIALIZED VIEW
2. CREATE TABLE
由于物化视图是基于对主表（MV SQL中的FROM部分的表）的查询操作，所以，如果创建MV的用户不是主表的拥有者，那么需要对其具有‘SELECT’权限（对象权限，需要查询：dba_tab_priv。
在我的当前环境里，我操作的用户是：SCOTT。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select grantee,privilege from dba_sys_privs where GRANTEE='SCOTT' and privilege like '%MATERIALIZED VIEW%'; no rows selected SQL> SQL> grant CREATE MATERIALIZED VIEW TO SCOTT; Grant succeeded. SQL> SQL> select grantee,privilege from dba_sys_privs where GRANTEE='SCOTT' and privilege like '%MATERIALIZED VIEW%'; GRANTEE PRIVILEGE ------------------------------ ---------------------------------------- SCOTT CREATE MATERIALIZED VIEW SQL> |
创建完全刷新的MV。
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> create materialized view emp_dept_status_mv 2 segment creation immediate 3 refresh 4 complete 5 on demand 6 as 7 select d.dname,count(e.ename) from emp e,dept d where e.deptno = d.deptno group by d.dname; Materialized view created. SQL> |
你可以在系统对象视图中查看到：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SQL> col segment_name for a28 SQL> col owner for a8 SQL> col object_type for a18 SQL> col segment_type for a18 SQL> col tablespace_name for a8 SQL> SQL> set linesize 300 SQL> SQL> select object_name,object_type from dba_objects where object_type='MATERIALIZED VIEW'; OBJECT_NAME OBJECT_TYPE ------------------------ ----------------------- EMP_DEPT_STATUS_MV MATERIALIZED VIEW SQL> SQL> select object_name,object_type from dba_objects where object_name like '%EMP_DEPT_STATUS_MV%'; OBJECT_NAME OBJECT_TYPE ---------------------------- ----------------------- EMP_DEPT_STATUS_MV TABLE I_SNAP$_EMP_DEPT_STATUS_MV INDEX EMP_DEPT_STATUS_MV MATERIALIZED VIEW SQL> SQL> select owner,segment_name,segment_type,tablespace_name,bytes/1024 "GB" from dba_segments where segment_name like '%EMP_DEPT_STATUS_MV%'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPA GB -------- ---------------------------- ------------------ -------- ---------- SCOTT EMP_DEPT_STATUS_MV TABLE USERS 64 SCOTT I_SNAP$_EMP_DEPT_STATUS_MV INDEX USERS 64 SQL> |
对象“I_SNAP$_EMP_DEPT_STATUS_MV”为MV的唯一索引，创建目的是为了提高刷新的性能。
刷新数据：
Before：
|
1 2 3 4 5 6 7 8 9 |
SQL> select * from emp_dept_status_mv; DNAME COUNT(E.ENAME) -------------- -------------- ACCOUNTING 3 RESEARCH 5 SALES 6 SQL> |
插入数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> insert into emp (empno,ename,deptno) values (8081,'Adamhuan',20); 1 row created. SQL> insert into emp (empno,ename,deptno) values (8082,'JackMa',20); 1 row created. SQL> insert into emp (empno,ename,deptno) values (8083,'LuiceLiu',30); 1 row created. SQL> |
再次查询：
|
1 2 3 4 5 6 7 8 9 |
SQL> select d.dname,count(e.ename) from emp e,dept d where e.deptno = d.deptno group by d.dname; DNAME COUNT(E.ENAME) -------------- -------------- ACCOUNTING 3 RESEARCH 7 SALES 7 SQL> |
但是，物化视图还没有相应的变化：
|
1 2 3 4 5 6 7 8 9 |
SQL> select * from emp_dept_status_mv; DNAME COUNT(E.ENAME) -------------- -------------- ACCOUNTING 3 RESEARCH 5 SALES 6 SQL> |
开始刷新：
快速刷新是会失败的：
Tips：exec dbms_mview.refresh(‘xxxxx’,’F’)，其中，第二个参数的F（Fast），表示快速刷新。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> exec dbms_mview.refresh('emp_dept_status_mv','F'); BEGIN dbms_mview.refresh('emp_dept_status_mv','F'); END; * ERROR at line 1: ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."DEPT" ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2802 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3039 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2998 ORA-06512: at line 1 SQL> |
因为该物化视图没有MV Log。
只有完全刷新才能成功：
Tips：exec dbms_mview.refresh(‘xxxxx’,’C’)，其中，第二个参数的C（Complete），表示完全刷新。
|
1 2 3 4 5 |
SQL> exec dbms_mview.refresh('emp_dept_status_mv','C'); PL/SQL procedure successfully completed. SQL> |
刷新完成后，再次查看物化视图的状态：
|
1 2 3 4 5 6 7 8 9 |
SQL> select * from emp_dept_status_mv; DNAME COUNT(E.ENAME) -------------- -------------- ACCOUNTING 3 RESEARCH 7 SALES 7 SQL> |
以上，展示了最基本的物化视图的使用。
—————————————————————
Done。
