Oracle物化视图
物化视图概念
什么是物化视图
在Oracle9i之前，Oracle提供了快照（snapshot）的功能，在9i中这一功能改名为物化视图（Materialized View），又称为实体化视图。
物化视图是一个与表类似的数据库对象，它存储远程数据在单个时间点的副本，物化视图可以来源于表、视图甚至另一个物化视图
问题的提出：为什么需要物化视图？
为什么需要物化视图？
在海量数据中检索所需的记录，查询速度是十分重要的。查询表或视图，再遇到了多表连接、聚合函数的情况下，查询速度会变得更加恶劣。
使用聚合函数、多表链接来查询数据，每次查询都需要耗费大量的资源来解析语句，如果可以把这些聚合出的结果集保存下来，那么下一次查询不需要耗费太多时间了，为了实现这个目录，Oracle提供了物化视图。
物化视图具有以下有特点：
-
减少网络负载。可以通过物化视图将主表数据分发，把负载分布在多台服务器上，而不是统一访问一台数据库服务器（读写分离）。
-
数据子集功能。物化视图可以基于行级或者列级复制数据，而不需要复制整个表。例如在一个地区的数据库中只复制主表中该地区的数据，减少不必要的数据。
-
支持离线操作。物化视图不需要实时的网络连接，比如可以在晚上的某个时间点设置定时任务，定时从主表中获取一天的增量数据。
-
预先保留查询结果。对于数据仓库，创建的物化视图多是聚合视图，单一表聚合视图和多表连接，这些计算是比较耗时的，使用物化视图存下预先计算好的结果，可以大幅提升速度。因此物化视图的最大的好处便是提高查询速度。
普通视图与物化视图的区别
普通视图
普通的视图只是一张虚表，它不占据段区块的空间。视图只有逻辑定义，它定义的语句存在数据字典里，因此不需要额外的存储空间。当查询视图的时候，只是重新执行一次SQL语句而已。
普通视图的数据存在于基表中，本身不含有数据。
查询视图的SQL文本：
SELECT view_name,text
FROM dba_views;
物化视图
与普通视图不一样，物化视图是具有段结构的，就像表一样，是实际存在数据的。当查询物化视图时不需要再运行一遍视图定义的SQL。
物化视图可以基于分区表，而物化视图本身也可以分区。
物化视图还可以用于复制、移动等方面。
物化视图语法
（1）通常模式
create materialized view 物化视图名 -- 1. 创建物化视图
build [immediate | deferred
] -- 2. 创建方式，默认immediate
refresh [force | fast | complete | never
] -- 3. 物化视图刷新方式，默认force
on [
commit | demand] -- 4. 刷新触发方式 不填则是on demand
start
with 开始时间 -- 5. 设置开始时间
next 间隔时间 -- 6. 设置间隔时间
with [primary key | rowid] -- 7. 类型，默认primary key
[enable | disable] query rewrite -- 8. 是否启用查询重写 默认disable
as -- 9. 关键字
查询语句; -- 10. select 语句
（2）ON PREBUILT TABLE模式
CREATE MATERIALIZED VIEW view-name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;
语法解释：
- Bulid子句，物化视图的创建方式
1） Immediate：立刻刷新物化视图，也就是立刻复制主表中的数据
2） deferred：在第一次请求时，刷新填充物化视图
- refresh，视图的刷新方式
1） force：默认的刷新方式。尝试快速刷新，如果不能执行快速刷新，则执行完全刷新
2） fast：快速刷新，只刷新增量部分。如果事先没有针对源表的物化视图日志，则创建失败
3） complete：完全刷新，这将会更新视图内所有的数据（具体过程：存储该视图的segment被truncate，然后重新查询）
4） never：物化视图不进行任何刷新
- 触发方式
1） on commit：当视图的基表发生commit操作时刷新 （需要建立实体化视图日志）。当指定了on commit，则不能指定START WITH 和NEXT
2） on demand：这种方式代表数据库不会刷新数据。除非使用手动刷新或其他方式刷新。
- 开始时间，间隔时间
1） start with：第一次刷新的时间
2） next：下一次自动刷新所需的时间间隔
3） start with&next的优先级比on demand高，所以实际上如果指定了start with和next，on demand也可以省略了
- 类型，with primary key 或者 with rowid
1） 默认基于primary key
2） 如果是基于rowid，则不能使用连接，聚合函数的方式建立（因为不知道取哪一个的rowid）
- query rewrite 启用查询重写
查询重写的意思是，当对基表进行查询时，oracle会判断能否通过查询物化视图的方式来获取数据，如果查询有聚集或连接的操作，通常查询物化视图能提升速度。默认是disable query rewrite；
启用重写查询的话，不能使用start with 和 next来限制了。
物化视图实验一
准备
权限检查
要创建物化视图，首先需要具有 CREATE MATERIALIZED VIEW的权限
--创建物化视图的权限
GRANT CREATE MATERIALIZED VIEW TO sim;
-- 通常物化视图需要获取另一个库的基表来建立，所以通常会加上一个创建dblink的权限
GRANT CREATE DATABASE LINK TO sim;
创建实验表
CREATE TABLE orders (
order_id NUMBER,
item_code VARCHAR2(240),
amount NUMBER
);
插入数据后等待更新
INSERT INTO orders VALUES(1,'A_01',600);
INSERT INTO orders VALUES(2,'B_01',700);
INSERT INTO orders VALUES(3,'B_02',444.5);
INSERT INTO orders VALUES(4,'B_02',610);
COMMIT;
create or replace view order_v as select * from orders;
select * from order_v;
-- 这里我建立一个普通视图用作对比
创建物化视图1
建立一个物化视图，一分钟刷新一次
CREATE MATERIALIZED VIEW orders_min_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE + 1 / 1440
AS
SELECT *
FROM orders o;
插入数据后立刻查询该视图，会发现查询不到刚才插入的数据
INSERT INTO orders VALUES(5,'C_01',808);
commit;
select * from orders_min_mv;
等待一分钟后刷新，才可以查询出来
创建物化视图2
接来下创建一个物化视图，刷新频率为一天刷新一次
CREATE MATERIALIZED VIEW orders_day_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE + 1
AS
SELECT *
FROM orders o;
insert into orders values(11,'D_01',650);
insert into orders values(12,'D_01',350);
insert into orders values(13,'D_01',1000);
COMMIT;
select * from orders_day_mv;
一天刷新一次，如果想要立刻刷新一次，可以使用dbms_mview.refresh这个过程来手动刷新物化视图
BEGIN
dbms_mview.refresh(list => 'orders_day_mv',
method => 'COMPLETE', -- 也可以使用fast，增量刷新
refresh_after_errors => TRUE);
END;
select * from orders_day_mv;
收集统计信息
BEGIN
DBMS_STATS.gather_table_stats(
ownname => 'SIM',
tabname => 'ORDERS_DAY_MV');
END;
物化视图实验二
使用完全刷新的方式，会截断物化视图段并重新查询填充数据，因此在对远程表执行时，可能会非常耗时，并涉及大量网络流量。
为了降低复制成本，可以使用“快速刷新”的方式，这种方式需要创建物化视图日志，以捕获自上次刷新以来对基表的所有更改。
创建物化视图日志
alter table orders add constraint orders_pk1 primary key(order_id);
CREATE MATERIALIZED VIEW LOG ON sim.orders
TABLESPACE sim_data --指定表空间
WITH PRIMARY KEY --默认主键约束，所以需要对表加上主键约束
INCLUDING NEW VALUES; --可选
只有建立了日志才可以执行以下语句
CREATE MATERIALIZED VIEW orders_mv1
REFRESH FAST -- 快速刷新
ON COMMIT
AS
SELECT o.order_id,
o.item_code,
o.amount
FROM orders o;
--否则报错：
ERROR at line 9:
ORA-23413: table "SIM"."ORDERS" does not have a materialized view log
注意事项：
1、虽然物化视图日志提高了物化视图刷新的性能，但它确实增加了在基表上执行DML所需的工作量
2、如果不执行定期刷新，物化视图日志可能会变得非常大
刷新组
建立一个on demand类型的物化视图，而且没有指定刷新间隔
CREATE MATERIALIZED VIEW orders_mv2
REFRESH FAST ON DEMAND
AS
SELECT *
FROM orders o;
建立刷新组
BEGIN
DBMS_REFRESH.make(
name => 'SIM.MIN_REFRESH', -- 定义刷新组名称
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/
BEGIN
DBMS_REFRESH.add(
name => 'MIN_REFRESH', -- 刷新组名
list => 'HISTORY_MV2', -- 视图名
lax => TRUE);
END;
/
-- 删除刷新组方法
-- DBMS_REFRESH.destroy(name => 'SIM.MIN_REFRESH');
插入数据后等一分钟后再查询
INSERT INTO orders VALUES(100,'A_01',10);
INSERT INTO orders VALUES(101,'A_01',10);
INSERT INTO orders VALUES(102,'A_01',10);
commit;
SELECT * FROM orders_mv2;
处理刷新组外，还可以使用定时任务来刷新
具有聚合函数的物化视图
物化视图可用于提高各种查询的性能，包括执行数据聚合和转换的查询。这使得工作只需完成一次，就可以被多个会话重复使用，从而减少了服务器上的总负载。
CREATE MATERIALIZED VIEW orders_item_avg_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE
AS
SELECT o.item_code, avg(o.amount) avg_amount
FROM orders o
GROUP BY o.item_code;
SQL> set autotrace on explain
SQL> set line 400
SQL> SELECT o.item_code, avg(o.amount) avg_amount FROM orders o GROUP BY o.item_code;
Execution Plan
----------------------------------------------------------
Plan hash value: 2183589723
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 45 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 5 | 45 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| ORDERS | 11 | 99 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
创建物化视图之后
SQL> SELECT o.item_code, avg(o.amount) avg_amount FROM orders o GROUP BY o.item_code;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| ORDERS_ITEM_AVG_MV | 5 | 65 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
检查这两个参数
当使用物化视图来提高转换和聚合的性能时，必须设置QUERY_REWRITE_INTEGRITY和QUERY_REWRITE_ENABLED参数，否则服务器将无法自动利用查询重写。如果永久需要，可以在pfile或spfile文件中设置这些参数。
SQL> show parameter QUERY_REWRITE_INTEGRITY;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_integrity string enforced
SQL> show parameter QUERY_REWRITE_ENABLED;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
使用job刷新
首先创建存储过程
CREATE OR REPLACE PROCEDURE REFRESH_VIEW
AS
BEGIN
--DBMS_OUTPUT.PUT_LINE('start...');
DBMS_MVIEW.REFRESH('orders_day_mv', 'C');
END USP_RVW_REFRESH_VIEW;
创建job定时任务
BEGIN
dbms_scheduler.create_job(
job_name => 'REFRASH_JOB', --job名
job_type => 'STORED_PROCEDURE', --job类型
job_action => 'REFRESH_VIEW', --存储过程名
start_date => SYSDATE, --开始执行时间
repeat_interval => 'FREQ=MINUTELY;INTERVAL=60*24', --下次执行时间
comments => 'refresh_test', --注释
auto_drop => FALSE, --job禁用后是否自动删除
enabled => TRUE);
END;
创建dblink
select * from user_sys_privs where privilege like upper('%DATABASE LINK%');
CREATE DATABASE LINK pdb1 CONNECT TO sim IDENTIFIED BY "11111111" USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =txy)(PORT =1539))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb1)
)
)';
select * from ss_deparment@pdb1;
可更新物化视图
以上我们创建的物化视图都是只读（read-only）物化视图，要想获得可更新的能力，需要加上FOR UPDATE子句。
对只读物化视图进行插入操作，会报错
SQL> insert into orders_mv2 values(99,'TEST_INSERT',9999);
insert into orders_mv2 values(99,'TEST_INSERT',9999)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
创建可更新物化视图，并插入数据
-- 其余子句可以自行添加
CREATE MATERIALIZED VIEW orders_mv3 FOR UPDATE AS
SELECT * FROM orders;
insert into orders_mv3 values(1000,'TEST_INSERT',1000);
select * from orders_mv3;
select * from orders;
但是会发现一个问题，就是对可更新物化视图的更新，无法将数据推送到主表之中
