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;

但是会发现一个问题，就是对可更新物化视图的更新，无法将数据推送到主表之中

posted on 2022-12-02 10:56 yq1DB 阅读(1582) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？