精华内容
下载资源
问答
  • 物化视图 (MV)在一个段中存储查询结果,并且能够在提交查询时将结果返回给用户,从而不再需要重新执行查询 — 在查询要执行几次时,这是一个很大的好处。物化视图可以利用一个快速刷新机制从基础表中全部或增量刷新...
  • 主要介绍了PostgreSQL物化视图(materialized view)过程解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • Oracle物化视图创建和使用,设置物化视图的自动刷新时间
  • NULL 博文链接:https://wuaner.iteye.com/blog/686899
  • Oracle怎么根据物化视图日志快速刷新物化视图
  • Oracle物化视图物化视图日志

    万次阅读 多人点赞 2019-04-02 21:43:30
    文章目录物化视图物化视图与普通视图的区别创建一个存放person的表创建一个存放person的address的表初始化数据创建物化视图的语句1.build [immediate|deferred]2.refresh [fast|complete|force] 视图刷新的方式:3.MV...

    以下都是最近我通过网上学习和实践简单总结的一些学习成果,还有很多还是不了解。

    物化视图

    物化视图与普通视图的区别

    物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询, Oracle 都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有实质上的好处。

    而物化视图是一张实际存在的表,是占有数据库磁盘空间的。
    物化视图并不像普通视图那样,只有在使用的时候才去读取数据,而是预先计算并保存表连接或者聚集等比较耗时操作的结果,这样大大提高了读取的速度,特别适合抽取大数据量表的某些信息。

    创建一个存放person的表

    create table test_person(pid int,name varchar2(20));
    alter table test_person add constraint  pk_pid primary key(pid);
    --删除表
    drop table test_person;
    

    创建一个存放person的address的表

    create table test_address(aid int,address varchar2(20),pid int);
    alter table test_address add constraint pk_aid primary key(aid);
    --删除表
    drop table test_address;
    

    初始化数据

    insert into test_person values(1,'kevin');
    insert into test_address values(1,'SHA',1);
    insert into test_person values(2,'vincent');
    insert into test_address values(2,'HKG',2);
    COMMIT;
    

    创建物化视图的语句

    create materialized view [view_name] 
    build [immediate|deferred]
    refresh [fast|complete|force] 
    [ 
    on [commit|demand] | 
    start with (start_time) next (next_time) 
    ] 
     [enable | disable] query rewrite
    as 
    {创建物化视图用的查询语句} 
    

    1.build [immediate|deferred]

    创建方式(BuildMethods):包括BUILD IMMEDIATE和BUILD DEFERRED两种。

    • BUILD IMMEDIATE是在创建物化视图的时候就生成数据。

    • BUILD DEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE

    2.refresh [fast|complete|force] 视图刷新的方式:

    • fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on (主表名)。

    • complete:全部刷新。相当于重新执行一次创建视图的查询语句。

    • force: 这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。

    3.MV数据刷新的时间:

    • on demand:在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据了(也可以使用job定时刷新)

    • on commit:当主表中有数据提交的时候,立即刷新MV中的数据;

    • start ……:从指定的时间开始,每隔一段时间(由next指定)就刷新一次;

    4.查询重写(QueryRewrite)

    包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。

    分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERY REWRITE。

    感觉很少用,有兴趣的可以研究下:
    https://mp.weixin.qq.com/s/5Lg8cSEb3R5WkQwVF9xfPQ

    演示 refresh complete on demand

    create materialized view my_mv_cd
    build immediate refresh complete on demand
    as
    select p.pid as id, p.name, a.address from test_person p,test_address a
    where p.pid = a.aid;
    

    这里的build immediate表示创建物化视图的同时立即导入数据。
    BUILD DEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE。

    在这里插入图片描述

    在demand模式下,修改基表,物化视图是不会跟着改变的,只能手动DBMS_MVIEW.REFRESH等方法来进行刷新,也可以通过JOB定时进行刷新

    演示 build deferred

    create materialized view my_mv_cd2
    build deferred refresh complete on demand
    as
    select p.pid as id, p.name, a.address from test_person p,test_address a
    where p.pid = a.aid;
    

    在这里插入图片描述

    如何手动刷新?

    BEGIN
    DBMS_MVIEW.REFRESH (
    list => 'my_mv_cd2',
    Method =>'C',
    refresh_after_errors => True);
    END;
    

    在这里插入图片描述

    演示 refresh complete on commit

    create materialized view my_mv_cc
    build immediate refresh complete on commit
    as
    select p.pid as id, p.name, a.address from test_person p,test_address a
    where p.pid = a.aid;
    
    insert into test_person values(3,'thomas');
    insert into test_address values(3,'ZHA',3);
    COMMIT;
    

    在这里插入图片描述

    一旦基表有了COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。缺点就是对基表的DML操作影响很大。

    演示on commit情况下的物化视图更新出错

    而且对基表的DML跟对物化视图的刷新应该是在同一个事务下的。所以如果物化视图更新出错,主表也无法提交事务。

    我们可以做如下的一个实验。。

    创建两个表,不设置主键。

    create table test_person2(pid int,name varchar2(20));
    create table test_address2(aid int,address varchar2(20),pid int);
    

    创建物化视图,刷新时间模式设置为on commit

    create materialized view my_mv_test
    refresh complete on commit
    as
    select p.pid as id, p.name, a.address from test_person2 p,test_address2 a
    where p.pid = a.aid;
    

    然后我们给id加一个唯一索引。

    create unique index u_test_index on my_mv_test(id);
    

    现在开始插入数据。。我们插入相同id的数据

    insert into test_person2 values(1,'kevin');
    insert into test_address2 values(1,'SHA',1);
    insert into test_person2 values(1,'vincent');
    insert into test_address2 values(1,'HKG',1);
    COMMIT;
    

    在这里插入图片描述

    然后我们可以发现两个基表也没有数据提交,说明是在同一个事务下进行了rollback。

    所以使用commit需要注意的地方:

    1. 主表提交时基表也提交,系统开销增大。在繁忙的OLTP系统中会影响一部分性能。如果物化视图更新出错,主表也无法提交事务。
    2. 主表和物化视图必须在同一个数据库中
    3. 无法在基表上执行分布式事务
    4. 不支持含有对象类型或Oracle补充支持类型的物化视图

    更多的参考:
    http://blog.itpub.net/29047826/viewspace-1575568/

    简单总结什么情况下不适合使用物化视图

    1.不适合建立物化视图的情况: 基表有频繁的DML操作 / (on commit刷新时很费时,大大增加DML操作的时间)

    2.多表连接的物化视图仅适合 refresh complete 定时刷新,且刷新时间不能小于30min,否则弊大于利(complete是删除原有数据重新连接多表数据)

    如何查看物化视图

    SELECT * FROM user_mviews;
    SELECT * FROM all_mviews;
    

    如何使用job去刷新物化视图

    begin
    dbms_scheduler.create_job(
      job_name => 'sam_job', --job名
      job_type => 'STORED_PROCEDURE', --job类型
      job_action => 'pc_sam', --存储过程名
      start_date => sysdate, --开始执行时间
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=5', --下次执行时间,每5分钟执行存储过程pc_sam
      comments => '测试存储过程', --注释
      auto_drop => false, --job禁用后是否自动删除
      enabled => true);
    end;
    /
    

    1、job_name: 任务名称

    2、job_type:有三种类型,PL/SQL Block、Stored procedure、Executable

    3、job_action:根据job_type的不同,有不同的含义

    • 如果job_type指定的是存储过程,就需要指定存储过程的名字;
    • 如果job_type指定的是PL/SQL块,就需要输入完整的PL/SQL代码;
    • 如果job_type指定的外部程序,就需要输入script的名称或者操作系统的指令名

    4、start_date:开始时间

    5、repeat_interval:运行的时间间隔,上面例子是每天23点运行一次

    6、end_date:到期时间

    7、enabled:创建后自动激活

    8、auto_drop:默认true,即当job执行完毕都到期是否直接删除job

    9、comments:备注

    关于schedule时间设置可以查看官网:https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235

    关于可以repeat_interval设置可以参考:https://blog.csdn.net/zq9017197/article/details/6985109

    删除schedule job

    BEGIN
      DBMS_SCHEDULER.DROP_JOB(
         job_name => 'my_mv_cd_job'
      );
    END;
    /
    

    演示如何创建一个schedule job来刷新物化视图

    首先我们创建一个存储过程,用来刷新我们的物化视图

    create or replace procedure my_mv_cd_procedure_job is
    begin
     dbms_mview.refresh('my_mv_cd');
    end my_mv_cd_procedure_job;
    /
    

    然后我们可以通过如下的sql来查询我们的存储过程。。

    SELECT * FROM all_source where type = 'PROCEDURE';
    
    SELECT * FROM user_source where type = 'PROCEDURE';
    

    然后我们创建一个schedule job来trigger我们的刚刚定义的存储过程。。

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
       job_name           =>  'my_mv_cd_job',
       job_type           =>  'STORED_PROCEDURE', --指定执行的是存储过程
       job_action         =>  'my_mv_cd_procedure_job', --对应的存储过程名称
       repeat_interval    =>  'FREQ=MINUTELY; INTERVAL=1', --1分钟trigger一次
       enabled            =>  true
       );
    END;
    /
    

    然后我们可以通过如下sql查询我们创建的schedule job

    --查询
    select * from ALL_SCHEDULER_JOBS;
    select * from USER_SCHEDULER_JOBS;
    

    ![image]

    我们可以看到执行时间是相隔了1分钟

    我们可以试着插入数据

    insert into test_person values(4,'justin');
    insert into test_address values(4,'HKG',4);
    commit;
    

    1分钟之后就会发现物化视图trigger刷新了。

    在这里插入图片描述

    如何删除物化视图

    drop materialized view {物化视图名称};
    

    物化视图日志

    物化视图的Fast刷新

    前面说到物化视图是可以进行Fast刷新,也就是增量刷新,当每次刷新的时候不必对整个物化视图进行刷新,只需要对有改动的record进行刷新。
    那db是如何知道有哪些record有change呢?
    通过对基表建立物化视图日志,就可以监测到基表的变动,每当基表有变动的时候就会往对应的物化视图日志中写入数据。当进行Fast刷新的时候,db就会把物化视图日志中的改动写入到物化视图中,这样就实现了增量的刷新。

    [67000][439] ORA-00439: feature not enabled: Advanced replication

    create materialized view log on test_kevin with primary key including new values;
    

    当我在本地尝试创建物化视图日志的时候发生了如下错误:

    [67000][439] ORA-00439: feature not enabled: Advanced replication

    我们可以通过如下sql查看我们db开启的功能:

    select * from v$option where parameter = 'Advanced replication';
    

    通过查看我们可以知道我们的高级复制功能是没有被开启的。不知道是当时安装有问题还是可能这是一个加钱才会有的功能吧。

    创建物化视图日志

    如果我们想要创建fast模式刷新的物化视图就一定要对关联的基表都需要创建物化视图日志。

    Oracle 的物化视图的快速刷新要求必须建立物化视图日志,通过物化视图日志可以实现增量刷新功能。

    • 物化视图日志在建立时有多种选项:可以指定为ROWID、PRIMARY KEY和OBJECTID几种类型,同时还可以指定SEQUENCE或明确指定列名。不过上面这些情况产生的物化视图日志的结构都不相同。
    • 当发生DML 操作时,内部的触发器会把变化记录到物化视图日志里,也就是说物化视图不支持DDL的同步,所以在物化视图的编写过程中不可使用select * from 的形式,因为这样当基表发生变化时,物化视图就会失效。

    物化视图日志的名称为MLOG$_后面跟基表的名称,如果表名的长度超过20位,则只取前20位,当截短后出现名称重复时,Oracle会自动在物化视图日志名称后面加上数字作为序号。

    任何物化视图都会包括的4列:

    • SNAPTIME$$:用于表示刷新时间。
    • DMLTYPE$$:用于表示DML操作类型,I表示INSERT,D表示DELETE,U表示UPDATE。
    • OLD_NEW$$:用于表示这个值是新值还是旧值。N(EW)表示新值,O(LD)表示旧值,U表示UPDATE操作。
    • CHANGE_VECTOR$$:表示修改矢量,用来表示被修改的是哪个或哪几个字段。Oracle就是通过CHANGE_VECTOR$$列来记录每条记录发生变化的字段包括哪些。
    • 如果WITH后面跟了ROWID,则物化视图日志中会包含:M_ROW$$:用来存储发生变化的记录的ROWID。
    • 如果WITH后面跟了PRIMARY KEY,则物化视图日志中会包含主键列。
    • 如果WITH后面跟了OBJECT ID,则物化视图日志中会包含:SYS_NC_OID$:用来记录每个变化对象的对象ID。
    • 如果WITH后面跟了SEQUENCE,则物化视图日子中会包含:SEQUENCE$$:给每个操作一个SEQUENCE号,从而保证刷新时按照顺序进行刷新。
    • 如果WITH后面跟了一个或多个COLUMN名称,则物化视图日志中会包含这些列。

    演示创建物化视图with primary key

    我们首先创建一张表

    create table test_person(pid int,name varchar2(20));
    alter table test_person add constraint test_pk_pid primary key(pid);
    

    然后我们创建物化视图日志,指定primary key

    create materialized view log on test_person with primary key;
    

    然后我们可以向基表test_person加入一条数据。

    insert into test_person values(1,'kevin');
    

    然后在update一条数据

    update test_person set name='kevin cai' where pid = 1;
    

    然后我们通过如下sql可以查看物化视图日志的内容。

    select * from mlog$_test_person;
    

    在这里插入图片描述

    including new values

    我们创建物化视图日志的时候还可以带上including new values

    create materialized view log on test_person with primary key including new values;
    

    同样我们也是先insert一条数据然后在update同一条数据,然后我们从mlog$_test_person中的内容就可以看得区别。
    在这里插入图片描述

    NEW VALUES子句允许Oracle数据库将新旧值都保存在物化视图日志中,以便更新DML操作。

    更多参考:

    http://www.itpub.net/forum.php?mod=viewthread&tid=2052180&highlight=

    https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6003.htm#i2119924

    演示创建物化视图with rowid

    CREATE MATERIALIZED VIEW LOG on test_person with rowid INCLUDING NEW VALUES;
    

    在这里插入图片描述

    使用场景,具体不是很了解,可以参考:https://blog.csdn.net/demonson/article/details/81450938

    实现物化视图的增量刷新

    我们先创建两张表

    create table test_person(pid int,name varchar2(20));
    alter table test_person add constraint test_pk_pid primary key(pid);
    create table test_address(aid int,address varchar2(20),pid int);
    alter table test_address add constraint test_pk_aid primary key(aid);
    

    然后我们对基表创建物化视图日志
    我们这里使用的是with rowid来创建物化视图日志

    CREATE MATERIALIZED VIEW LOG on test_person with rowid INCLUDING NEW VALUES;
    CREATE MATERIALIZED VIEW LOG on test_address with rowid INCLUDING NEW VALUES;
    

    然后我们就可以创建fast刷新模式的物化视图。。

    create materialized view my_test_mv refresh fast WITH ROWID as
      select p.pid as p_id, p.name, a.address, p.ROWID as p_ROWID,a.ROWID as a_ROWID
          from test_person p, test_address a where p.pid = a.pid;
    

    注意:
    with rowid的时候记得创建物化视图的时候需要带上select条件ROWID,不然会宝如下错误:
    ORA-12052: cannot fast refresh materialized view

    而且建MV时一定要加上with rowid,因为没有聚合函数的MV默认是with primary key

    更多参考:https://blog.csdn.net/minwang593/article/details/19205553

    这时候我们可以尝试往基表插入数据

    insert into test_person values(1,'kevin');
    insert into test_address values(1,'SHA',1);
    insert into test_person values(2,'vincent');
    insert into test_address values(2,'HKG',2);
    

    我们可以看到物化视图日志中都有数据

    select * from mlog$_test_person;
    select * from mlog$_test_address;
    

    在这里插入图片描述

    但是物化视图中还没有数据。我们可以通过手动方式刷新物化视图中的数据

    BEGIN
    DBMS_MVIEW.REFRESH (
    list => 'my_test_mv',
    Method =>'C',
    refresh_after_errors => True);
    END;
    

    之后就可以发现物化视图中有数据了。
    在这里插入图片描述

    而且一旦物化视图刷新,关联基表的物化视图日志也会被清空。。

    我们可以通过如下sql删除物化视图和物化视图日志:

    --删除物化视图
    drop materialized view my_test_mv;
    --删除物化视图日志
    drop materialized view log on test_person;
    drop materialized view log on test_address;
    

    然后我们可以试试以外连接的语法来创建物化视图

    create materialized view my_test_mv refresh fast WITH ROWID as
      select p.pid as p_id, p.name, a.address, p.ROWID as p_ROWID,a.ROWID as a_ROWID
          from test_person p left join test_address a on p.pid = a.pid;
    

    然后我们发现我们创建物化视图的时候报错如下:

    [72000][12015] ORA-12015: cannot create a fast refresh materialized view from a complex query

    这是因为物化视图快速刷新不支持标准外联接写法,必须要改写sql

    select * from a,b where a.id(+)=b.id;--b为主表,a为补充
    select * from a,b where a.id=b.id(+);--a为主表,b为补充
    

    所以我们将我们的sql改写为

    create materialized view my_test_mv refresh fast WITH ROWID as
      select p.pid as p_id, p.name, a.address, p.ROWID as p_ROWID,a.ROWID as a_ROWID
          from test_person p, test_address a where p.pid = a.pid(+);
    

    更多参考:https://www.cnblogs.com/tracy/archive/2011/09/01/2162080.html

    总结一下创建fast模式物化视图条件:

    多表连接的物化视图想要使用fast刷新时,

    首先,select 语句中包含到的每一个表都需要创建视图日志;

    其次,视图日志中需要指定在select语句和where条件中用到的该表的字段;

    第三,在select中必须包含所涉及到的所有表的rowid,

    第四,外连接不能使用标志sql语法,应使用(+)这种oracle固有语法.

    当然还有很多其他的限制条件,可以参考:https://blog.csdn.net/aiyocxuan/article/details/78732567

    使用物化视图增量刷新的优缺点

    我个人觉得使用增量模式刷新物化视图的优点就是不需要对整个物化视图进行刷新,当物化视图的数据特别大的时候可以减少刷新的时间,其他的优点没有暂时还没感受到。

    缺点:

    1.使用增量模式进行刷新需要对所有关联的基表都建立物化视图日志,首先在数据库中就要占据一部分的空间,其次对基表创建物化视图日志对基表的DML还是有影响的,可以参考下面:
    Oracle 物化视图快速刷新对性能的影响
    http://blog.itpub.net/28539951/viewspace-2127870/

    2.然后对基表的DML和对物化视图日志的更新应该是在同一个事务里的,因为rollback的时候是会一起rollback的,所以如果物化视图日志一旦失败可能导致基表的DML操作失败,对业务上可能会影响。

    3.物化视图日志的维护成本比较高
    物化视图日志经常会由于物化视图长时间没有刷新,或者基表的一次批量数据更改而变得很大,这会影响物化视图的刷新性能,因此对于这种情况需要对物化视图日志进行处理,降低物化视图日志表的高水位线。

    Oracle的物化视图的快速刷新功能,主要是靠物化视图日志来实现的。
    物化视图日志会记录下基表所有的增、删、改操作,而物化视图执行完快速刷新操作后,会从物化视图日志中将本物化视图刷新过且其他物化视图所不需要刷新的记录删除掉。如果其中一个物化视图一直不刷新,那么物化视图日志就会变得越来越大。
    还有一种情况,比如表中插入了大量的数据,或者删除了大量的数据,或者将表中的某一列统一更新为一个值,这种操作都会在物化视图日志中产生大量的记录。
    而物化视图日志的增大必然影响物化视图的刷新速度。一方面,物化视图在刷新的时候要扫描物化视图日志,另一方面,物化视图在刷新结束后,也要清除物化视图日志中的记录,仍然要扫描物化视图日志,因此物化视图日志的大小直接会影响物化视图快速刷新的速度。更重要的是,物化视图日志的高水位一旦增长到一个很高的位置,即使以后物化视图日志中记录很少,甚至没有记录存在,物化视图在刷新的时候仍然需要较长的时间。

    oracle通过v$sql视图查询sql的平均执行时间

    SELECT SQL_TEXT,EXECUTIONS "总执行次数",ELAPSED_TIME/1000 "总耗时(秒)", ELAPSED_TIME/nvl(EXECUTIONS,1)/1000 "平均耗时(秒)",
           PARSE_CALLS "硬解析次数",DISK_READS "物理读次数",BUFFER_GETS "读缓存区次数"
    FROM v$SQL WHERE SQL_TEXT LIKE '%select * from t1%';
    

    更多参考:https://blog.csdn.net/yh_zeng2/article/details/78946807

    展开全文
  • oralce创建物化视图

    2014-04-23 09:16:19
    oralce创建物化视图,基本语法,基本操作
  • Apache doris物化视图

    2021-07-31 10:59:35
    物化视图 物化视图是将预先计算(根据定义好的 SELECT 语句)好的数据集,存储在 doris中的一个特殊的表。 物化视图的出现主要是为了满足用户,既能对原始明细数据的任意维度分析,也能快速的对固定维度进行分析...

    物化视图

    物化视图是将预先计算(根据定义好的 SELECT 语句)好的数据集,存储在 doris中的一个特殊的表。

    物化视图的出现主要是为了满足用户,既能对原始明细数据的任意维度分析,也能快速的对固定维度进行分析查询。

    目前doris物化视图只支持单表,不支持多表join操作

    适用场景

    • 分析需求覆盖明细数据查询以及固定维度查询两方面。
    • 查询仅涉及表中的很小一部分列或行。
    • 查询包含一些耗时处理操作,比如:时间很久的聚合操作等。
    • 查询需要匹配不同前缀索引。

    优势

    • 对于那些经常重复的使用相同的子查询结果的查询性能大幅提升。
    • Palo 自动维护物化视图的数据,无论是新的导入,还是删除操作都能保证 Base 表和物化视图表的数据一致性。无需任何额外的人工维护成本。
    • 查询时,会自动匹配到最优物化视图,并直接从物化视图中读取数据。
    自动维护物化视图的数据会造成一些维护开销,会在后面的物化视图的局限性中展开说明。

    物化视图 VS Rollup

    在没有物化视图功能之前,用户一般都是使用 Rollup 功能通过预聚合方式提升查询效率的。但是 Rollup 具有一定的局限性,他不能基于明细模型做预聚合。

    物化视图则在覆盖了 Rollup 的功能的同时,还能支持更丰富的聚合函数。所以物化视图其实是 Rollup 的一个超集。

    也就是说,之前 ALTER TABLE ADD ROLLUP 语法支持的功能现在均可以通过 CREATE MATERIALIZED VIEW 实现。

    使用物化视图

    Palo 系统提供了一整套对物化视图的 DDL 语法,包括创建,查看,删除。DDL 的语法和 PostgreSQL, Oracle 都是一致的。

    创建物化视图

    这里首先你要根据你的查询语句的特点来决定创建一个什么样的物化视图。这里并不是说你的物化视图定义和你的某个查询语句一模一样就最好。这里有两个原则:

    1. 从查询语句中抽象出,多个查询共有的分组和聚合方式作为物化视图的定义。
    2. 不需要给所有维度组合都创建物化视图。

    首先第一个点,一个物化视图如果抽象出来,并且多个查询都可以匹配到这张物化视图。这种物化视图效果最好。因为物化视图的维护本身也需要消耗资源。

    如果物化视图只和某个特殊的查询很贴合,而其他查询均用不到这个物化视图。则会导致这张物化视图的性价比不高,既占用了集群的存储资源,还不能为更多的查询服务。

    所以用户需要结合自己的查询语句,以及数据维度信息去抽象出一些物化视图的定义。

    第二点就是,在实际的分析查询中,并不会覆盖到所有的维度分析。所以给常用的维度组合创建物化视图即可,从而到达一个空间和时间上的平衡。

    通过下面命令就可以创建物化视图了。创建物化视图是一个异步的操作,也就是说用户成功提交创建任务后,Palo 会在后台对存量的数据进行计算,直到创建成功。

    具体的语法可以通过下面命令查看 CREATE MATERIALIZED VIEW。

    支持聚合函数

    目前物化视图创建语句支持的聚合函数有:

    • SUM, MIN, MAX
    • COUNT, BITMAP_UNION, HLL_UNION
    • BITMAP_UNION 的形式必须为:BITMAP_UNION(TO_BITMAP(COLUMN)) column 列的类型只能是整数(largeint也不支持), 或者 BITMAP_UNION(COLUMN) 且 base 表为 AGG 模型。
    • HLL_UNION 的形式必须为:HLL_UNION(HLL_HASH(COLUMN)) column 列的类型不能是 DECIMAL , 或者 HLL_UNION(COLUMN) 且 base 表为 AGG 模型。

    更新策略

    为保证物化视图表和 base 表的数据一致性, Palo 会将导入,删除等对 base 表的操作都同步到物化视图表中。并且通过增量更新的方式来提升更新效率。通过事务方式来保证原子性。

    比如如果用户通过 INSERT 命令插入数据到 base 表中,则这条数据会同步插入到物化视图中。当 base 表和物化视图表均写入成功后,INSERT 命令才会成功返回。

    查询自动匹配

    物化视图创建成功后,用户的查询不需要发生任何改变,也就是还是查询的 base 表。Palo 会根据当前查询的语句去自动选择一个最优的物化视图,从物化视图中读取数据并计算。

    用户可以通过 EXPLAIN 命令来检查当前查询是否使用了物化视图。

    物化视图中的聚合和查询中聚合的匹配关系:

    物化视图聚合查询中聚合

    其中 bitmap 和 hll 的聚合函数在查询匹配到物化视图后,查询的聚合算子会根据物化视图的表结构进行一个改写。详细见实例2。

    查询物化视图

    查看当前表都有哪些物化视图,以及他们的表结构都是什么样的。通过下面命令:

     MySQL [test]> desc mv_test all;
     +-----------+---------------+-----------------+----------+------+-------+---------+--------------+
     | IndexName | IndexKeysType | Field           | Type     | Null | Key   | Default | Extra        |
     +-----------+---------------+-----------------+----------+------+-------+---------+--------------+
     | mv_test   | DUP_KEYS      | k1              | INT      | Yes  | true  | NULL    |              |
     |           |               | k2              | BIGINT   | Yes  | true  | NULL    |              |
     |           |               | k3              | LARGEINT | Yes  | true  | NULL    |              |
     |           |               | k4              | SMALLINT | Yes  | false | NULL    | NONE         |
     |           |               |                 |          |      |       |         |              |
     | mv_2      | AGG_KEYS      | k2              | BIGINT   | Yes  | true  | NULL    |              |
     |           |               | k4              | SMALLINT | Yes  | false | NULL    | MIN          |
     |           |               | k1              | INT      | Yes  | false | NULL    | MAX          |
     |           |               |                 |          |      |       |         |              |
     | mv_3      | AGG_KEYS      | k1              | INT      | Yes  | true  | NULL    |              |
     |           |               | to_bitmap(`k2`) | BITMAP   | No   | false |         | BITMAP_UNION |
     |           |               |                 |          |      |       |         |              |
     | mv_1      | AGG_KEYS      | k4              | SMALLINT | Yes  | true  | NULL    |              |
     |           |               | k1              | BIGINT   | Yes  | false | NULL    | SUM          |
     |           |               | k3              | LARGEINT | Yes  | false | NULL    | SUM          |
     |           |               | k2              | BIGINT   | Yes  | false | NULL    | MIN          |
     +-----------+---------------+-----------------+----------+------+-------+---------+--------------+

    可以看到当前 mv_test 表一共有三张物化视图:mv_1, mv_2 和 mv_3,以及他们的表结构。

    删除物化视图

    如果用户不再需要物化视图,则可以通过下面命令删除物化视图:DROP MATERIALIZED VIEW。

    最佳实践1

    使用物化视图一般分为一下几个步骤:

    1. 创建物化视图
    2. 异步检查物化视图是否构建完成
    3. 查询并自动匹配物化视图

    首先是第一步:创建物化视图

    假设用户有一张销售记录明细表,存储了每个交易的交易id,销售员,售卖门店,销售时间,以及金额。建表语句为:

     create table sales_records(record_id int, seller_id int, store_id int, sale_date date, sale_amt bigint) distributed by hash(record_id) properties("replication_num" = "1");

    这张 sales_records 的表结构如下:

     MySQL [test]> desc sales_records;
     +-----------+--------+------+-------+---------+-------+
     | Field     | Type   | Null | Key   | Default | Extra |
     +-----------+--------+------+-------+---------+-------+
     | record_id | INT    | Yes  | true  | NULL    |       |
     | seller_id | INT    | Yes  | true  | NULL    |       |
     | store_id  | INT    | Yes  | true  | NULL    |       |
     | sale_date | DATE   | Yes  | false | NULL    | NONE  |
     | sale_amt  | BIGINT | Yes  | false | NULL    | NONE  |
     +-----------+--------+------+-------+---------+-------+

    这时候如果用户经常对不同门店的销售量进行一个分析查询,则可以给这个 sales_records 表创建一张以售卖门店分组,对相同售卖门店的销售额求和的一个物化视图。创建语句如下:

     create materialized view store_amt as select store_id, sum(sale_amt) from sales_records group by store_id;

    返回成功,则说明创建物化视图任务提交成功。

    第二步:检查物化视图是否构建完成

    由于创建物化视图是一个异步的操作,用户在提交完创建物化视图任务后,需要异步的通过命令检查物化视图是否构建完成。命令如下:

     SHOW ALTER TABLE MATERIALIZED VIEW FROM db_name;

    该命令的更多帮助请参阅:SHOW ALTER TABLE MATERIALIZED VIEW。

    第三步:查询

    当创建完成物化视图后,用户再查询不同门店的销售量时,就会直接从刚才创建的物化视图 store_amt 中读取聚合好的数据。达到提升查询效率的效果。

    用户的查询依旧指定查询 sales_records 表,比如:

     SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;

    上面查询就能自动匹配到 store_amt。用户可以通过下面命令,检验当前查询是否匹配到了合适的物化视图。

     MySQL [test]> EXPLAIN SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;
     +-----------------------------------------------------------------------------+
     | Explain String                                                              |
     +-----------------------------------------------------------------------------+
     | PLAN FRAGMENT 0                                                             |
     |  OUTPUT EXPRS:<slot 2> `store_id` | <slot 3> sum(`sale_amt`)                |
     |   PARTITION: UNPARTITIONED                                                  |
     |                                                                             |
     |   RESULT SINK                                                               |
     |                                                                             |
     |   4:EXCHANGE                                                                |
     |                                                                             |
     | PLAN FRAGMENT 1                                                             |
     |  OUTPUT EXPRS:                                                              |
     |   PARTITION: HASH_PARTITIONED: <slot 2> `store_id`                          |
     |                                                                             |
     |   STREAM DATA SINK                                                          |
     |     EXCHANGE ID: 04                                                         |
     |     UNPARTITIONED                                                           |
     |                                                                             |
     |   3:AGGREGATE (merge finalize)                                              |
     |   |  output: sum(<slot 3> sum(`sale_amt`))                                  |
     |   |  group by: <slot 2> `store_id`                                          |
     |   |                                                                         |
     |   2:EXCHANGE                                                                |
     |                                                                             |
     | PLAN FRAGMENT 2                                                             |
     |  OUTPUT EXPRS:                                                              |
     |   PARTITION: RANDOM                                                         |
     |                                                                             |
     |   STREAM DATA SINK                                                          |
     |     EXCHANGE ID: 02                                                         |
     |     HASH_PARTITIONED: <slot 2> `store_id`                                   |
     |                                                                             |
     |   1:AGGREGATE (update serialize)                                            |
     |   |  STREAMING                                                              |
     |   |  output: sum(`sale_amt`)                                                |
     |   |  group by: `store_id`                                                   |
     |   |                                                                         |
     |   0:OlapScanNode                                                            |
     |      TABLE: sales_records                                                   |
     |      PREAGGREGATION: ON                                                     |
     |      partitions=1/1                                                         |
     |      rollup: store_amt                                                      |
     |      tabletRatio=10/10                                                      |
     |      tabletList=22038,22040,22042,22044,22046,22048,22050,22052,22054,22056 |
     |      cardinality=0                                                          |
     |      avgRowSize=0.0                                                         |
     |      numNodes=1                                                             |
     +-----------------------------------------------------------------------------+
     45 rows in set (0.006 sec)

    其中最重要的就是 OlapScanNode 中的 rollup 属性。可以看到当前查询的 rollup 显示的是 store_amt。也就是说查询已经正确匹配到物化视图 store_amt, 并直接从物化视图中读取数据了。

    最佳实践2 PV,UV

    业务场景: 计算广告的 UV,PV

    假设用户的原始广告点击数据存储在 Palo,那么针对广告 PV, UV 查询就可以通过创建 bitmap_union 的物化视图来提升查询速度。

    通过下面语句首先创建一个存储广告点击数据明细的表,包含每条点击的点击事件,点击的是什么广告,通过什么渠道点击,以及点击的用户是谁。

     MySQL [test]> create table advertiser_view_record(time date, advertiser varchar(10), channel varchar(10), user_id int) distributed by hash(time) properties("replication_num" = "1");
     Query O
     K, 0 rows affected (0.014 sec)

    原始的广告点击数据表结构为:

     MySQL [test]> desc advertiser_view_record;
     +------------+-------------+------+-------+---------+-------+
     | Field      | Type        | Null | Key   | Default | Extra |
     +------------+-------------+------+-------+---------+-------+
     | time       | DATE        | Yes  | true  | NULL    |       |
     | advertiser | VARCHAR(10) | Yes  | true  | NULL    |       |
     | channel    | VARCHAR(10) | Yes  | false | NULL    | NONE  |
     | user_id    | INT         | Yes  | false | NULL    | NONE  |
     +------------+-------------+------+-------+---------+-------+
     4 rows in set (0.001 sec)
    1. 创建物化视图
      由于用户想要查询的是广告的 UV 值,也就是需要对相同广告的用户进行一个精确去重,则查询一般为:
      SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;
      针对这种求 UV 的场景,我们就可以创建一个带 bitmap_union 的物化视图从而达到一个预先精确去重的效果。
      在 Palo 中,count(distinct) 聚合的结果和 bitmap_union_count聚合的结果是完全一致的。而bitmap_union_count 等于 bitmap_union 的结果求 count, 所以如果查询中涉及到 count(distinct) 则通过>创建带 bitmap_union 聚合的物化视图方可加快查询
      针对这个 case,则可以创建一个根据广告和渠道分组,对 user_id 进行精确去重的物化视图。
      MySQL [test]> create materialized view advertiser_uv as select advertiser, channel, bitmap_union(to_bitmap(user_id)) from advertiser_view_record group by advertiser, channel;
      Query OK, 0 rows affected (0.012 sec)
      注意:因为本身 user_id 是一个 INT 类型,所以在 Palo 中需要先将字段通过函数 to_bitmap 转换为 bitmap 类型然后才可以进行 bitmap_union 聚合。
      创建完成后, 广告点击明细表和物化视图表的表结构如下:
      MySQL [test]> desc advertiser_view_record all;
      +------------------------+---------------+----------------------+-------------+------+-------+---------+--------------+
      | IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
      +------------------------+---------------+----------------------+-------------+------+-------+---------+--------------+
      | advertiser_view_record | DUP_KEYS | time | DATE | Yes | true | NULL | |
      | | | advertiser | VARCHAR(10) | Yes | true | NULL | |
      | | | channel | VARCHAR(10) | Yes | false | NULL | NONE |
      | | | user_id | INT | Yes | false | NULL | NONE |
      | | | | | | | | |
      | advertiser_uv | AGG_KEYS | advertiser | VARCHAR(10) | Yes | true | NULL | |
      | | | channel | VARCHAR(10) | Yes | true | NULL | |
      | | | to_bitmap(`user_id`) | BITMAP | No | false | | BITMAP_UNION |
      +------------------------+---------------+----------------------+-------------+------+-------+---------+--------------+
    2. 查询自动匹配
      当物化视图表创建完成后,查询广告 UV 时,Palo 就会自动从刚才创建好的物化视图 advertiser_uv 中查询数据。比如原始的查询语句如下:
      SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;
      在选中物化视图后,实际的查询会转化为:
      SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id)) FROM advertiser_uv GROUP BY advertiser, channel;
      通过 EXPLAIN 命令可以检验到 Palo 是否匹配到了物化视图:
      MySQL [test]> explain SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Explain String |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | PLAN FRAGMENT 0 |
      | OUTPUT EXPRS:<slot 7> `advertiser` | <slot 8> `channel` | <slot 9> bitmap_union_count(`default_cluster:test`.`advertiser_view_record`.`mv_bitmap_union_user_id`) |
      | PARTITION: UNPARTITIONED |
      | |
      | RESULT SINK |
      | |
      | 4:EXCHANGE |
      | |
      | PLAN FRAGMENT 1 |
      | OUTPUT EXPRS: |
      | PARTITION: HASH_PARTITIONED: <slot 4> `advertiser`, <slot 5> `channel` |
      | |
      | STREAM DATA SINK |
      | EXCHANGE ID: 04 |
      | UNPARTITIONED |
      | |
      | 3:AGGREGATE (merge finalize) |
      | | output: bitmap_union_count(<slot 6> bitmap_union_count(`default_cluster:test`.`advertiser_view_record`.`mv_bitmap_union_user_id`)) |
      | | group by: <slot 4> `advertiser`, <slot 5> `channel` |
      | | |
      | 2:EXCHANGE |
      | |
      | PLAN FRAGMENT 2 |
      | OUTPUT EXPRS: |
      | PARTITION: RANDOM |
      | |
      | STREAM DATA SINK |
      | EXCHANGE ID: 02 |
      | HASH_PARTITIONED: <slot 4> `advertiser`, <slot 5> `channel` |
      | |
      | |
      | 1:AGGREGATE (update serialize) |
      | | STREAMING |
      | | output: bitmap_union_count(`default_cluster:test`.`advertiser_view_record`.`mv_bitmap_union_user_id`) |
      | | group by: `advertiser`, `channel` |
      | | |
      | 0:OlapScanNode |
      | TABLE: advertiser_view_record |
      | PREAGGREGATION: ON |
      | partitions=1/1 |
      | rollup: advertiser_uv |
      | tabletRatio=10/10 |
      | tabletList=22084,22086,22088,22090,22092,22094,22096,22098,22100,22102 |
      | cardinality=0 |
      | avgRowSize=0.0 |
      | numNodes=1 |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      45 rows in set (0.030 sec)
      在 EXPLAIN 的结果中,首先可以看到 OlapScanNode 的 rollup 属性值为 advertiser_uv。也就是说,查询会直接扫描物化视图的数据。说明匹配成功。
      其次对于 user_id 字段求 count(distinct) 被改写为求 bitmap_union_count(to_bitmap)。也就是通过 bitmap 的方式来达到精确去重的效果。

    最佳实践3

    业务场景:匹配更丰富的前缀索引

    用户的原始表有 (k1, k2, k3) 三列。其中 k1, k2 为前缀索引列。这时候如果用户查询条件中包含 where k1=1 and k2=2 就能通过索引加速查询。

    但是有些情况下,用户的过滤条件无法匹配到前缀索引,比如 where k3=3。则无法通过索引提升查询速度。

    创建以 k3 作为第一列的物化视图就可以解决这个问题。

    1. 创建物化视图
      CREATE MATERIALIZED VIEW mv_1 as SELECT k3, k2, k1 FROM tableA ORDER BY k3;
      通过上面语法创建完成后,物化视图中既保留了完整的明细数据,且物化视图的前缀索引为 k3 列。表结构如下:
      MySQL [test]> desc tableA all;
      +-----------+---------------+-------+------+------+-------+---------+-------+
      | IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
      +-----------+---------------+-------+------+------+-------+---------+-------+
      | tableA | DUP_KEYS | k1 | INT | Yes | true | NULL | |
      | | | k2 | INT | Yes | true | NULL | |
      | | | k3 | INT | Yes | true | NULL | |
      | | | | | | | | |
      | mv_1 | DUP_KEYS | k3 | INT | Yes | true | NULL | |
      | | | k2 | INT | Yes | false | NULL | NONE |
      | | | k1 | INT | Yes | false | NULL | NONE |
      +-----------+---------------+-------+------+------+-------+---------+-------+
    2. 查询匹配
      这时候如果用户的查询存在 k3 列的过滤条件是,比如:
      select k1, k2, k3 from table A where k3=3;
      这时候查询就会直接从刚才创建的 mv_1 物化视图中读取数据。物化视图对 k3 是存在前缀索引的,查询效率也会提升。

    局限性

    1. 物化视图的聚合函数的参数不支持表达式仅支持单列,比如: sum(a+b)不支持。
    2. 如果删除语句的条件列,在物化视图中不存在,则不能进行删除操作。如果一定要删除数据,则需要先将物化视图删除,然后方可删除数据。
    3. 单表上过多的物化视图会影响导入的效率:导入数据时,物化视图和 base 表数据是同步更新的,如果一张表的物化视图表超过10张,则有可能导致导入速度很慢。这就像单次导入需要同时导入10张表数据是一样的。
    4. 相同列,不同聚合函数,不能同时出现在一张物化视图中,比如:select sum(a), min(a) from table 不支持。

    异常错误

    1. DATA_QUALITY_ERR: "The data quality does not satisfy, please check your data"
      由于数据质量问题导致物化视图创建失败。 注意:bitmap类型仅支持正整型, 如果原始数据中存在负数,会导致物化视图创建失败
    展开全文
  • MongoDB视图测试创建脚本及测试数据创建脚本;创建物化视图的基本方式及进阶方式。如:用$unwind 、$replaceRoot分解$lookup中的as数组,使之扁平化返回;使用$project 重命名字段;使用$merge输出物化视图
  • 今天巡检数据库发现存在一张220G的数据库实体表,通过命名(MLOG$_TABLE_NAME)可以看出这是一张物化视图日志表,基表只有120M,正常情况下物化视图日志会在物化视图刷新后被数据库自动清理,这里的刷新无视刷新规则...

    【摘要】

                今天巡检数据库发现存在一张220G的数据库实体表,通过命名(MLOG$_TABLE_NAME)可以看出这是一张物化视图日志表,基表只有120M,正常情况下物化视图日志会在物化视图刷新后被数据库自动清理,这里的刷新无视刷新规则,fast、complete和force都会导致日志被清空。

    【正文】

                基表,物化视图日志和物化视图关系

                一般情况下,创建物化视图会直接从基表取数据,如果数据量比较大每次都全量获取数据会对数据库造成一定压力,为了能够增量获得基表的变化,可以考虑创建物化视图日志,物化视图日志根据基表主键产生记录,每次基表发生DML操作都会产生一条或多条记录,物化视图可以根据这些记录做到增量刷新,刷新完成后Oracle会自动清空物化视图日志。

                经过实验,发现以下注意点

    1. 创建物化视图日志必须要求基表存在主键
    2. 创建物化视图会自动刷新并清空物化视图日志
    3. user_registered_mviews视图中的can_use_log默认为yes,即使改为no也可以使用并清空物化视图日志
    4. 如果修改基表定义导致物化视图状态为INVALID,但是又改回原定义,此时的物化视图在下次执行后会自动改为VALID状态
    5. 如果物化视图日志因其他原因导致很大,需要手动清理,可以通过Oracle自带存储过程(purge_mview_from_log)进行处理

    具体语法:

    exec dbms_mview.purge_mview_from_log(21);

    --21为mviews_id,从dba_base_table_mviews得到

    物化视图未及时更新,物化视图日志又被手动清理后,再次刷新物化视图会导致报错,此时需要重建物化视图

    ORA-12034: materialized view log on "owner"."table_name" younger than last refresh

                以下为实验步骤

    create table t_mv (id int,name varchar2(20),age int);--创建测试表

    alter table t_mv add constraint  pk_id primary key(id);--添加主键

    create materialized view log on t_mv;--创建物化视图日志

    select * from MLOG$_T_MV; --空

    insert into t_mv values (1,'lpp',26);

    commit;

    select * from t_mv;--1条数据

    select * from MLOG$_T_MV;--1条数据

    select * from user_registered_mviews; --空

    create materialized view mv_t_mv refresh fast as select id,name from t_mv;

    select * from mv_t_mv;--1条数据;

    select * from MLOG$_T_MV; --空, 创建时就会清空日志表

    insert into t_mv values (2,'lppe',26);

    commit;

    select * from t_mv;--2条数据

    select * from MLOG$_T_MV;--1条数据,

    select * from user_registered_mviews; --1条数据,can_use_log字段默认是yes

    execute dbms_mview.refresh('mv_t_mv');--命令行执行

    select * from mv_t_mv;--2条数据

    select * from MLOG$_T_MV;--空

    update user_registered_mviews set can_use_log='NO';--普通用户无权限,sysdba提示不能修改虚拟列

    select * from user_registered_mviews; --1条数据,can_use_log字段默认是no,通过修改基表SYS.REG_SNAP$

    insert into t_mv values (3,'lppf',26);

    commit;

    select * from t_mv;--3条数据

    select * from MLOG$_T_MV;--1条数据

    execute dbms_mview.refresh('mv_t_mv');--命令行执行

    select * from mv_t_mv;--3条数据

    select * from MLOG$_T_MV;--空,can_use_log字段是no依然可以读取并清空物化视图日志

    alter table t_mv drop column name; --修改表定义,删除name字段

    select * from all_objects where object_name='MV_T_MV';--物化视图状态失效

    alter table t_mv add name varchar2(20);--修改表定义,添加name字段

    select * from all_objects where object_name='MV_T_MV';--物化视图状态依然失效

    select * from dba_mviews;--上次刷新时间 2020/1/5 6:38:26,staleness 字段为NEEDS_COMPILE,服务器时间有误

    select * from dba_base_table_mviews;--上次刷新时间 2020/1/5 6:38:26,mview_id 21

    insert into t_mv values (4,26,'lppg');--修改表定义导致字段顺序调整

    commit;

    select * from t_mv;--4条数据

    select * from MLOG$_T_MV;--1条数据

    execute dbms_mview.refresh('mv_t_mv');--命令行执行成功,此时发现物化视图状态为有效

    select * from mv_t_mv;--4条数据

    select * from MLOG$_T_MV;--空,此时发现物化视图状态为有效

    alter table t_mv drop column name; --修改表定义,删除name字段

    select * from all_objects where object_name='MV_T_MV';--物化视图状态失效

    insert into t_mv values (5,26);--删除了name字段

    select * from t_mv;--5条数据

    select * from MLOG$_T_MV;--1条数据

    execute dbms_mview.refresh('mv_t_mv');--命令行执行报错 ORA-00904: "T_MV"."NAME": invalid identifier

    insert into t_mv values (6,26);--删除了name字段

    select * from t_mv;--6条数据

    select * from MLOG$_T_MV;--2条数据

    insert into t_mv values (7,26);--删除了name字段

    select * from t_mv;--7条数据

    select * from MLOG$_T_MV;--3条数据,不能刷新导致日志一直增大,可能达到上百G

    exec dbms_mview.purge_mview_from_log(21);--手动在命令行清理物化视图日志,从mview_id 21开始,21是从dba_base_table_mviews得到

    select * from t_mv;--7条数据

    select * from MLOG$_T_MV;--已清空

    insert into t_mv values (8,26);--删除了name字段

    alter table t_mv add name varchar2(20);--修改表定义,添加name字段

    execute dbms_mview.refresh('mv_t_mv');--命令行执行报错 ORA-12034: materialized view log on "LPP"."T_MV" younger than last refresh

    alter materialized view mv_t_mv compile;--尝试编译物化视图,成功编译

    execute dbms_mview.refresh('mv_t_mv');--命令行执行报错 ORA-12034: materialized view log on "LPP"."T_MV" younger than last refresh

    drop materialized view mv_t_mv;--删除物化视图

    create materialized view mv_t_mv refresh fast as select id,name from t_mv;--重建物化视图

    select * from mv_t_mv;--8条数据

    select * from MLOG$_T_MV;--空

    insert into t_mv values (9,26,'lpp');

    select * from t_mv;--9条数据

    select * from MLOG$_T_MV;--1条数据

    alter materialized view mv_t_mv refresh force on demand;--修改刷新机制

    execute dbms_mview.refresh('mv_t_mv');--命令行执行

    select * from mv_t_mv;--9条数据

    select * from MLOG$_T_MV;--空

    drop materialized view mv_t_mv;--删除物化视图

    insert into t_mv values (10,26,'lppa');

    select * from t_mv;--10条数据

    select * from MLOG$_T_MV;--1条数据

    create materialized view mv_t_mv refresh force on demand as select id,name from t_mv;--重建物化视图

    select * from mv_t_mv;--9条数据

    select * from MLOG$_T_MV;--空

    drop materialized view mv_t_mv;--删除物化视图

    insert into t_mv values (11,26,'lppb');

    select * from t_mv;--11条数据

    select * from MLOG$_T_MV;--1条数据

    create materialized view mv_t_mv refresh complete on demand as select id,name from t_mv;--重建物化视图

    select * from mv_t_mv;--11条数据

    select * from MLOG$_T_MV;--空

    delete from t_mv where id=11; --1条数据

    update t_mv set age=25; --11条数据

    【总结】

                经常检查数据库无效对象,尤其是物化视图是否失效,及时排查及时处理,避免浪费过多的空间。

    展开全文
  • 1、使用物化视图中遇到的问题 一般在创建物化视图的时候,在数据量不大的时候,刷新的方式都是采用完全刷新的。 随着系统的使用一些物化视图的源表的数据量在不断的增长,原本采用完全方式几秒就能刷新完成的物化...

    1、使用物化视图中遇到的问题

    一般在创建物化视图的时候,在数据量不大的时候,刷新的方式都是采用完全刷新的。

    随着系统的使用一些物化视图的源表的数据量在不断的增长,原本采用完全方式几秒就能刷新完成的物化视图,现在需要等待很久的时间才能刷新完成。

    其实物化视图从一开始就帮我们想好了解决方法:通过物化视图日志来实现物化视图的快速刷新

    2、传统完全刷新 区分  快速刷新

    完全刷新:先把物化视图的数据全部删除,然后把基表的数据插入到 物化视图中。

                      当数据达到百万级别时,若原表更新了一条数据,完全刷新就得 插入全部数据

    快速刷新:保留物化视图的数据,然后基表的所有数据的变更记录物化视图日志

           总结: 物化视图日志就是一个数据库引擎创建的表,用来跟踪基表发生的变更

                       所以若需要进行快速刷新,则需要建立物化视图日志

    3、首先分析一下物化视图日志结构

    Oracle物化视图日志根据不同物化视图的快速刷新的需要,可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

    create table t (id number ,name varchar2(30),val number);

    create materialized view log on t with rowid,sequence (id,name) including  new values;

             删除相应日志表:drop materialized view log on dim_a;

    desc mlog$_t

    ID和NAME:  标识基表中的列,记录每次DML操作对应的ID 和 NAME的值

    m_row$$:  标识基表中ROWID信息,可以定位到发生DML操作的记录

    sequence$$:  DML操作发生的序列编号

    dmltype$$:  标识DML类型

    old_new$$:  标识物化视图日志中保存的数据是  DML操作之前的值还是之后的值

    chance_vector$$:  记录DML操作发生在哪几个段上

    总结:当刷新物化视图时,只需要根据SEQUENCE$$列给出的顺序,通过M_ROW$$定位到基表的记录,如果是UPDATE操作,通过CHANGE_VECTOR$$定位到字段,然后根据基表中的数据重复执行DML操作

    4、并不是所有的物化视图都可以进行快速刷新

    所有类型的快速刷新物化视图都必须满足的条件:

    1.物化视图不能包含对不重复表达式的引用,如SYSDATE和ROWNUM;

    2.物化视图不能包含对LONG和LONG RAW数据类型的引用。

     

    只包含连接的物化视图:

    1.必须满足所有快速刷新物化视图都满足的条件;

    2.不能包括GROUP BY语句或聚集操作;

    3.如果在WHERE语句中包含外连接,那么唯一约束必须存在于连接中内表的连接列上;

    4.如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”操作。

    5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。

    6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。

    参考:https://blog.csdn.net/demonson/article/details/82018706

     

    5、物化视图日志  解决多视图的原理

    物化视图日志——必须支持 多视图的快速刷新

    也就是在刷新时,必须判断

    1. 刷新时必须判断,哪些物化视图日志记录  是当前物化视图需要的(控制更新粒度)

    2. 刷新后必须判断,哪些日志需要清除,哪些不需要清除

    SQL> create materialized view mv_t_id refresh fast as select id, count(*) from t group by id;       

    SQL> create materialized view mv_t_name refresh fast as select name, count(*) from t group by name;

    SQL> create materialized view mv_t_id_name refresh fast as select id, name, count(*) from t group by id, name;

    SQL> insert into t values (1, 'a', 2);

    SQL> insert into t values (1, 'b', 3);

    SQL> insert into t values (2, 'a', 5);

    SQL> insert into t values (3, 'b', 7);

    SQL> update t set name = 'c' where id = 3;

    SQL> delete t where id = 2;

    SQL> commit;

    snaptime$$:  4000-01-01 00:00:00。这个值表示这条记录还没有被任何物化视图刷新过,第一个刷新这些记录的物化视图会将SNAPTIME$$的值更新为物化视图当前的刷新时间

    SQL> exec dbms_mview.refresh('MV_T_ID');

    SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

    SQL> select name, last_refresh from user_mview_refresh_times
    SQL> select mview_name, last_refresh_date, staleness from user_mviews

     

    这些视图中记录了每个物化视图上次执行刷新操作的时间,并且给出每个物化视图中的数据是否和基表同步

    由于MV_T_ID刚刚进行了刷新,因此状态是FRESH,而另外两个由于在刷新(建立)之后,基表又进行了DML操作,因此状态为NEEDS_COMPILE。

    如果这时对基表进行DML操作,则MV_T_ID的状态也会变为NEEDS_COMPILE

    SQL> insert into t values (4, 'd', 10);

    SQL> commit;

    SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;                                    【基表记录的   刷新时间】

    SQL> select mview_name, last_refresh_date, staleness from user_mviews;                               [物化视图的    刷新时间]

    下面刷新MV_T_ID_NAME(上次刷新了MV_T_ID),刷新依据是:

    1、仅刷新物化视图日志记录中   SNAPTIME$$列(基表记录的刷新时间)  大于  当前物化视图的LAST_REFRESH_DATE的记录(视图的刷新时间)

    2、对于SNAPTIME$$列的值是4000-01-01 00:00:00的记录,物化视图会把SNAPTIME$$列的值更新为当前刷新时间

    3、那些已经被更新过的SNAPTIME$$列,则保持原值

    SQL> exec dbms_mview.refresh('MV_T_ID_NAME')

    SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

    如果这时再次刷新物化视图MV_T_ID,则只有ID=4的这条记录的SNAPTIME$$的时间点大于MV_T_ID上次刷新的时间点,因此,只刷新这一条记录,且不会改变SNAPTIME$$的值。

    SQL> exec dbms_mview.refresh('MV_T_ID')
    SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;

     

    物化视图日志记录的删除

    每次进行完刷新,物化视图日志都会试图删除没有用的物化视图日志记录。物化视图日志记录的删除条件是删除那些SNAPTIME$$列(日志记录)小于等于所有物化视图的上次刷新时间===旧数据,已经刷新的数据

    SQL> insert into t values (5, 'e', 2);
    SQL> commit;
    SQL> exec dbms_mview.refresh('MV_T_NAME')

               

    SQL> drop materialized view log on t;

    SQL> drop materialized view mv_t_id;

    SQL> drop materialized view mv_t_name;

    SQL> drop materialized view mv_t_id_name;

    SQL> drop table t;

    到此 完整删除了  物化视图,物化视图日志,实体表

     

    总结:物化视图在刷新时,会刷新SNAPTIME$$(基表记录 刷新时间)大于该物化视图上次刷新时间(视图刷新时间)的记录,并将所有是4000-01-01 00:00:00的记录更新为当前刷新时间。对于其他大于上次刷新时间的记录,只刷新不更改。这样,当刷新执行完以后,数据字典中记录当前物化视图的上次刷新时间为当前时刻,这保证了物化视图日志中目前所有的记录都小于或等于刷新时间。因此,每个物化视图只要刷新大于上次刷新时间的记录,且保证每次刷新后,所有记录的时间都小于等于上次刷新时间,那么无论有多少个物化视图,就可以互不影响的使用同一个物化视图日志进行快速刷新了。当物化视图刷新完之后,会清除那些SNAPTIME$$列小于所有物化视图的上次刷新时间的记录,而这些记录已经被所有的物化视图都刷新过了,保存在物化视图日志中已经没有意义了。

    每个物化视图只要刷新大于上次刷新时间的记录,且保证每次刷新后,所有记录的时间都小于等于上次刷新时间,那么无论有多少个物化视图,就可以互不影响的使用同一个物化视图日志进行快速刷新了

     

     

     

     

     

     

     

     

     

     

     

     

     

    展开全文
  • 这是 Pequod 的源代码版本,这是一个快速的分布式键值缓存,内置支持物化视图。 Pequod 是一个研究原型,不应在任何生产环境中使用。 有关 Pequod 设计的背景,请参阅出版物: 内容 PQDIR这个目录。 PQDIR/src ...
  • Oracle 物化视图

    千次阅读 2019-06-06 08:42:10
    近来看到一些文章有关Oracle 物化视图的,整合了下,比较详细,以作memo: 物化视图(Materialized View)在9i以前的版本叫做快照(SNAPSHOT),从9i开始改名叫做物化视图。其实无论是快照还是物化视图,都能体现出这...
  • 物化视图迁移分区

    2016-01-07 14:39:28
    物化视图迁移分区,停机时间少于10分钟。
  • 无需额外的维护成本 查询的时候也可以自动匹配最优的物化视图 3.1 创建物化视图 首先你需要有一个base表,基于这个base表的数据提交一个创建物化视图的任务,任务中定义好物化视图的如何构建。然后Doris就会异步的...
  • mysql触发器实现oracle物化视图即不是基于基表的虚表,而是根据表实际存在的实表,需要的朋友可以参考下
  • 物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有...
  • mysql实现物化视图

    千次阅读 2020-01-14 10:30:33
    原项目用的是国产数据库人大金仓,因用户要求,需要迁移到mysql5.7上,在搜索解决方法之前有了大致思路就是建立物化视图表,然后使用存储过程或者触发器去更新物化视图表就可以了,但想要找有没有更优的方案,于是就...
  • Clickhouse物化视图

    2021-06-17 15:01:01
    Clickhouse物化视图讲解概念语法数据更新案例基于单机表引擎MergeTree创建download表插入数据查看数据创建物化视图查看视图模拟产生新数据查询验证试图聚合计算基于集群分布式表引擎ReplicatedMergeTree创建本地表...
  • 简介: 本文主要介绍什么是物化视图,以及如何实现基于物化视图的查询改写。 作者:阿里云数据库OLAP产品部 云曦 预计算和缓存是计算机领域提高性能以及降低成本的最常见的手段之一。对于那些经常重复的请求,如果...
  • 物化视图是一个实体的表,物化视图是相对于视图而言的,但是两者实际上并没有什么关系就如java/javaScript一样 首先mysql的视图不是一种物化视图,他相当于一个虚拟表,本身并不存储数据,当sql在操作视图时所有数据...
  • 概述物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并...
  • 物化视图的快速刷新

    2012-11-07 18:54:39
    物化视图的快速刷新
  • 物化视图的刷新方式说明 物化视图可以选择三种不同的刷新方式,根据不同的需求,选择不同的刷新方式。 Complete刷新:会删除表中的所有记录(如果是单表刷新,可能会采用Truncate的方式),然后根据物化视图中查询...
  • MySQL中实现物化视图

    2012-12-17 09:19:03
    真正值得一看的mysql知识。MySQL中实现物化视图(中文版)翻译于外文。
  • 物化视图

    2020-05-04 16:53:12
    目录1 例子1.1 物化视图的定义1.2 初始运行1.3 物化视图的刷新2 附加信息 从4.2版开始,MongoDB为聚合管道( aggregation pipeline)添加了$merge阶段(stage )。此阶段可以将管道结果合并到现有集合中,而不是完全...
  • 上节我们简要介绍了物化视图,本文补充说明创建和修改物化视图的一些选项。 创建物化视图 在了解物化视图之前,我们首先导入一些示例数据作为计算的基础: CREATE TABLE t_demo (grp int, data numeric); INSERT ...
  • 物化视图重写。 数据沿袭分析 该项目正在积极开发中,并且***尚未准备好进行生产***。 在线API 我们提供了免费的API http://sql-booster.mlsql.tech 。 您可以访问http://sql-booster.mlsql.tech/api以获得所有...
  • 物化视图是相对于视图而言的,但是两者实际上并没有什么关系就如java/javaScript一样 首先mysql的视图不是一种物化视图,他相当于一个虚拟表,本身并不存储数据,当sql在操作视图时所有数据都是从其他表中查询出来的...
  • Spark SQL 物化视图原理与实践

    千次阅读 2020-05-11 19:57:00
    导言物化视图作为一种预计算的优化方式,广泛应用于传统数据库中,如Oracle,MSSQL Server等。随着大数据技术的普及,各类数仓及查询引擎在业务中扮演着越来越重要的数据分析角色,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 15,785
精华内容 6,314
关键字:

物化视图