物化视图使用手册(一)

葛文余,现就职于北京海天起点技术服务股份有限公司,专家组成员,从业8年以上,在数据库领域有丰富的实践经验。 主要涉及金融、电信、制造业等核心系统运维经验,获得11g OCM、12c OCM、MySQL OCP、Exadata、Goldengate等相关技术认证,擅长高可用、高性能数据库的部署实施、 故障诊断、性能优化等工作。

引言:

随着现代经济的飞速发展,信息量的不断膨胀,企业各项业务中,需要处理的数据量也不断增长。同时企业间的竞争日趋激烈,时间就是效益。对大数据量操作的快速响应已经成为企业发展的新挑战。而面对这样的挑战,数据库系统将如何面对?

数据仓库技术就是这样应运而生的。数据仓库系统是一个信息提供平台,他从业务处理系统获得数据,经过抽取,加工形成。他能够为用户提供大数据量的快速查询响应,并为企业的决策分析提供数据依据。在诸多的数据仓库技术中,物化视图是一颗闪耀的明星。下面全面的讲解物化视图的基本知识,并以Topa系统为例,详细讲解物化视图如何在真实的场景中实现对大数据量查询操作的快速响应。

基本概念简介

物化视图的概念

物化视图是包括一个查询结果的数据库对象,它的基本创建方法类似于普通视图,但可以比普通视图更复杂。它与普通视图的不同之处在于,它存储真实的结果集数据,能够为复杂查询提供特殊的优化方案,提高查询速度。

物化视图的特点

物化视图也是一张特殊的表。它基于表存在。当创建一个物化视图后,数据库中多了两个同名的对象,一个是物化视图,一个是其对应的同名表。物化视图的数据与普通表数据一样,存储在段中。在dba_segments中我们可以找到对应的段。物化视图也拥有表的功能,可以分区,可以drop分区,也可以交换分区,可以创建索引等等。

物化视图的优势

普通的视图只是一个定义,并不存储真实的数据,对普通视图的查询,oracle会转化为对基表的查询。普通视图不会加快查询的速度,而且会随着基表数据量的增长,查询会越来越慢。但物化视图由于其提前初始化了结果集数据,所以直接查询会很快。物化视图还提供快速刷新功能,即使基表越来越大,只要设置了适当的刷新间隔,查询速度、刷新速度也不会受影响。除此之外,物化视图还提供查询重写功能,这一功能可以使物化视图对业务,对开发人员完全透明,甚至不需要知道它的存在。

很多大的查询是没有办法用常规方法优化的,由于查询的数据量很大,且没有办法消除全表扫描。试想当业务人员看到这样的查询语句,经过优化后,瞬间就出结果的时候,会觉得多么的神奇。

使用场景介绍

用于远程数据复制

可以借助数据库链接(dblink),在远程数据库中建立一个本地表的副本。用该方式实现表的定时同步。这种方式在早期使用的较多,随着各种复制技术的发展,已经逐渐被取代。尽管如此,物化视图在特定的场合,尤其是对同步数据的实时性要求不高时 ,仍然是一种最简单,有效的同步方式。

用于数据仓库中加快聚合查询的速度

在数据仓库类系统中,有很多查询,涉及的数据量很大,且耗时较长。这种情况下,我们没有办法使用常规的优化方法加快查询的速度。这时候,我们可以使用物化视图,使查询的结果集提前初始化出来。后期再针对新的数据做增量的更新。当我们需要查询结果时,可以直接查询物化视图,或者通过查询重写定位到物化视图,来加快速度。当基表发生改变时,物化视图可以根据物化视图日志做增量的刷新。使快速得到全量的,最新的数据成为可能。

物化视图的技术要点

物化视图的创建

物化视图的创建语句比较复杂,这里我先创建一个简单的物化视图,只指定必要的几部分内容,其他的都采用默认值:

该物化视图的功能是查询每个部门员工的总人数和平均工资。

此时我们可以从user_objects视图中看到如下两个对象:

正如我前面所讲的,该视图创建了一个同名的表。

我们可以再user_segments中找到该物化视图相应的段:

我们可以使用dbms _metadata.get_ddl命令,得到存储在数据库中的该物化视图的定义:

我们可以看到,物化视图的很多属性,包括组织形式、压缩、logging等都与普通表类似。除此之外还有更丰富的内容,如下,这些都是默认属性:

BUILD IMMEDIATE

REFRESH FORCE

ON DEMAND

DISABLE QUERY REWRITE

下面会对这些属性做更详细的介绍。

物化视图的初始化

物化视图是存储数据的,存储的是查询语句的结果集。但结果集是什么时候填充的呢?这就是物化视图的初始化。默认情况下,是创建的时候就初始化了,也就是BUILD IMMEDIATE。

物化视图有三种初始化数据的方式:

  • BUILD IMMEDIATE

这一项,刚刚讲过了,就是创建物化视图的时候初始化,该选项是默认值。

  • BUILD DEFER

该选项指定物化视图创建时不初始化数据,而是当第一次使用该物化视图时才初始化,即延迟数据初始化。

  • PREBUILT

该选项比较特殊,是指在创建物化视图之前就已经初始化了数据。即提前创建了与物化视图同名,同结构的表。这点类似提前创建了一个唯一索引,之后又在该索引的基础上创建了主键。当删除该主键时,唯一索引不会被删除。PREBUILT方式创建的物化视图也是一样,如果删除了该物化视图,其对应的表还会存在。主键与对应索引的名称可以不同,但物化视图与其底层表必须同名。

物化视图的刷新方式

物化视图的数据来源于底层表,其首次初始化时,数据与底层表是一致的,即当查询物化视图时得到的数据,与同样的sql查询底层表时得到的数据完全一样。数据初始化之后,如果没有任何干预措施,物化视图的数据默认就不再变化。但底层的表是会随着业务而变化的。为了保证物化视图的数据是新鲜的,是与底层表一致的,就需要对物化视图进行刷新。

物化视图的刷新从刷新方式上有以下三种方式:

  • Refresh Complete

该选项为全量刷新,即当底层表发生任何改变时,如果想让物化视图与底层表一致,需要重新基于底层表来构造数据。相当于把旧的数据做truncate操作,然后重新生成新数据。全量刷新代价较大,即使底层表只增加了一条记录,或者更新、删除了一条记录,都会对底层表进行全量分析来构造新数据。

  • Refresh Fast

该选项为快速刷新,即当底层表发生改变时,不需要对旧的数据做truncate操作,而是在旧数据的基础上,只对新的,修改的数据做增量的刷新。从名称上就能看出,快速刷新的速度要优于全量刷新,因为该方式不需要基于全量的底层表进行数据的构造,而只是针对改变的部分。但快速刷新不是说快速就能快速的,需要一定的代价,需要物化视图日志的支持。而且即使有物化视图日志的支持,也不是所有的视图,或者底层表进行了任何操作后都能快速刷新。这些复杂的知识后面还会继续讲解。

  • Refresh Force

该选项为强制刷新,是物化视图的默认刷新方式。强制刷新是指首先会尝试快速刷新,当快速刷新无法完成时,就会使用全量刷新。该方式保证物化视图一定会被刷新。

物化视图的刷新时间

物化视图的刷新需要指定时间。如果不指定,物化视图不会刷新。物化视图的刷新时间有以下几种方式指定:

  • On demand

该方式不指定刷新时间,需要人为控制物化视图的刷新。即根据需要,手工执行物化视图的刷新操作。

  • On commit

该方式指定当底层表发生改变,并提交时,自动对物化视图进行刷新。这就表示每次当底层表发生改变时,都会导致物化视图的刷新。如果底层表修改频繁,该动作代价会非常大。

  • Start with。。。next。。。

该方式指定第一次刷新的时间,并指定一个间隔。这样oracle会根据这个设定的时间来自动刷新物化视图。可以创建时指定,也可以创建后修改。该功能依赖于传统job。

  • Never

该选项指定物化视图不要刷新。即数据初始化之后就是静态的,不需要根据基表去刷新。

物化视图的状态

物化视图有几种状态,每个状态都显示了物化视图或者其基表的当前状况。基表的数据更新、定义改变,物化视图的刷新、编译都会对物化视图的状态造成影响。物化视图有如下几种状态:

  • FRESH

新鲜状态。该状态表明物化视图的数据与底层表是一致的,即查询物化视图和相同sql查询底层表得到的结果是一样的。物化视图在初次初始化数据,基表还没有任何数据修改时,是FRESH状态。该状态是我们最乐于见到的状态。

  • STALE

陈旧状态。该状态表明物化视图的数据是不新鲜的,物化视图的数据与底层表不一致。或者说数据初始化或刷新后,底层表发生了修改操作,物化视图没有进行同步刷新。当然在某些特定的场合下,陈旧的数据也是有价值的。例如决策支持类系统,该类系统通常分析一笔交易或者一组数字的增长趋势,这时候是否差一两天的数据是不影响决策的。这种时候如果得到最新的数据代价比较大,也可以使用陈旧的数据来代替。

  • NEEDS_COMPILE

需要编译状态。当物化视图处于该状态时,物化视图作为一个对象(OBJECT),它也是无效状态(INVALID)的。当一个对象变成无效状态时,我们一般需要对其进行重新编译,以保证对象是正常可用的。但当物化视图处于需要编译状态,或者无效状态时,并不一定是真的需要编译了。这个状态在很多时候是一个中间状态,是物化视图由FRESH状态转化成STALE状态的必经中间状态。底层表的定义发生改变会使物化视图失效,需要编译,就如同底层表定义改变会导致其相关的存储过程失效一样。但底层表的dml操作同样也会导致物化视图失效,这时候物化视图的失效,需要编译实际上是一种假象,只要物化视图进行一次刷新,就会使其状态变成正常的。

  • COMPILATION_ERROR

编译错误状态。该状态通常是由于底层表结构发生改变,且改变的字段被物化视图所引用导致。例如物化视图所引用的列在基表中被删除。这时候物化视图首先会变成NEEDS_COMPILE状态,当我们对其进行编译后,就会变成COMPILATION_ERROR状态。物化视图对象同样也会保持无效状态(INVALID)。尽管如此,此时查询物化视图仍然可以正常使用。只是数据还是陈旧的,已经不一致,甚至跟基表没有任何关系了。

  • UNUSABLE

不可用状态。该状态说明物化视图的数据已经不可用了,类似于第四种状态。实际上当物化视图所引用的列被删除后,又重新加回来的时候,物化视图会变成不可用状态。因为此时该列的值都是新的,或者是空的。尽管对物化视图的查询仍然可以继续,但基表与物化视图的数据是不一致的。此时需要重新构造该物化视图。

  • UNKNOWN

未知状态。该状态只有在使用PREBUILT方式创建的物化视图上才会出现。该状态说明物化视图在创建时,其对应的同名表已经存在,且无法确定该表的数据是否与底层表一致。无论该表的数据是否是新鲜的,或者是否有数据都不会影响物化视图的创建。该物化视图刷新后,会变成FRESH状态。底层表发生修改,也会变成STALE,NEEDS_COMPILE状态。

  • UNDEFINED

未定义状态。该状态表明Oracle不知道该物化视图的定义是否是正确的,因为其对应的底层表不在与之相同的数据库内。通常情况下,通过数据库链接(dblink)查询语句创建的物化视图是未定义状态。

下面探讨一下各种操作对物化视图状态的影响:

  • 物化视图的创建

一般的物化视图在创建时是FRESH状态;以PREBUILT方式创建的是UNKNOWN状态;通过数据库链接创建的为UNDEFINED状态。

  • 底层表的ddl操作

底层表的ddl操作,例如增加删除列,改变列长度等,都会导致物化视图失效(INVALID),变成需要编译状态(NEEDS_COMPILE).

  • 底层表的dml操作

底层表的dml操作,例如增加、删除、更新数据,同样也可能会导致物化视图失效(INVALID),且需要编译(NEEDS_COMPILE)。另外,并不是任何时候的dml操作都会导致物化视图失效,需要编译。有些情况下,物化视图会由于底层表的dml操作,直接变成STALE状态,而不经过需要编译的中间状态。

  • 物化视图的编译

物化视图变成无效状态(INVALID),需要编译(NEEDS_COMPILE)时,对物化视图执行编译操作,根据基表操作内容的不同,编译后的状态也有多种:

  1. STALE,基表进行了dml操作。
  2. FRESH,基表只是进行了ddl操作,且改变的相关列没有被物化视图所引用
  3. COMPILATION_ERROR,基表进行了ddl操作,且改变的相关列被物化视图所引用。例如删除了引用的列。
  4. UNUSABLE,当物化视图变成COMPILATION_ERROR状态后,解决了编译错误后,重新编译物化视图时,其会变成UNUSABLE状态,例如重新添加被删除的引用列。
  • 物化视图的刷新

如果物化视图能够成功刷新,那么物化视图必然会变成FRESH状态。

解析:为什么有时候dml操作会导致物化视图失效,有时候却不会导致其失效?

通过大量的测试,我们不难发现,当物化视图是Fresh状态时,对其进行dml操作一般会导致物化视图失效,而当物化视图本身就是Stale状态时则不会。根据这个初步结论,我们也可以推导得出下面的结论:

当物化视图刷新时,该刷新操作会对底层表加一个看不见的锁,这个锁是null锁,类似一个触发器,当底层表发生dml操作时会触发该触发器,使相关的物化视图置为失效状态。这一点类似存储过程的失效。当底层表的结构发生改变时同样会导致相关的存储过程失效。

而当物化视图编译时,该编译操作不会对底层表加锁,所以继续对底层表的dml操作则不会导致物化视图失效。

物化视图日志及其作用

所谓的物化视图日志,就是为了物化视图而存在的,记录了基表数据变化的一种数据结构,它也是一种特殊表,且也占用存储空间。它用于物化视图的快速增量刷新。物化视图日志有如下几个特点:

  1. 物化视图日志创建后,数据是自动生成的,它记录基表的任何dml操作。
  2. 即使dml操作不提交,物化视图日志也会记录。回滚后会清除。
  3. 物化视图刷新后,物化视图日志的内容会自动清除。
  4. 对于update操作,物化视图日志会根据需要记录数据的旧值。比如当涉及到聚合查询(例avg)的快速刷新时,即便只更新一条记录,物化视图日志却会记录两条记录。

物化视图日志创建时需要指定一个标识,该标识是物化视图日志与基表记录对应的依据。通俗的讲,就是通过这个标识,找到基表哪条记录被修改了。这个标识可以是基表的主键,也可以是rowid。二者必须选择其一,默认是以主键作为标示符,当基表不存在主键时,如果也没有指定rowid,则创建物化视图日志会失败。

下面创建一个物化视图日志,语法如下:

创建完后,我们可以发现数据库中多了一个表对象MLOG$_EMP:

该表的结构如下:


物化视图日志创建时不需要指定名称,其标准名称为:MLOG$_表名,如上面创建的基于emp表的物化视图日志为MLOG$_EMP。下面讲解每个列的作用。

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

SNAPTIME$$:用于表示刷新时间,该值默认是很大的值,当该值大于物化视图上次刷新时间时,该记录会用于下次物化视图的刷新。

DMLTYPE$$:用于表示DML操作类型,I表示INSERT,D表示DELETE,U表示UPDATE。

OLD_NEW$$:用于表示这个值是新值还是旧值。N(EW)表示新值,O(LD)表示旧值,U表示UPDATE操作。

CHANGE_VECTOR$$:表示修改矢量,用来表示被修改的是哪个或哪几个字段。

除此之外,物化视图日志还包含其他列信息:

  1. M_ROW$$:如果WITH后面跟了ROWID,则物化视图日志中会包含该列,用来存储发生变化的记录的ROWID。
  2. SEQUENCE$$:如果WITH后面跟了SEQUENCE,则物化视图日子中会包含该列,给每个操作一个SEQUENCE号,从而保证刷新时按照顺序进行刷新。
  3. 主键列:如果WITH后面跟了PRIMARY KEY,则物化视图日志中会包含该列。
  4. 其他列:如果WITH后面跟了一个或多个COLUMN名称,则物化视图日志中会包含这些列。

以下是物化视图日志的典型内容:


最后注意一点:

物化视图日志是为了物化视图而存在的,但是当系统中没有物化视图,却存在物化视图日志时,当基表发生改变时,改变的内容也会被物化视图日志记录下来,这个开销就是没有任何意义了。应当避免这种情况的发生。

与物化视图相关的常用对象及解析

1)常用的数据字典user_mviews

该字典表记录了物化视图的各种信息,包括其所有者,刷新类型,最新刷新时间等。下面对常用且比较重要的字段做一些简单介绍:

QUERY:该物化视图对应的查询语句;

UPDATABLE:该物化视图是否可更新,物化视图在创建时可以指定是否可以更新;

REFRESH_MODE:刷新类型,默认为DEMAND,手工刷新;

REFRESH_METHOD:刷新方式,默认为Force,强制刷新;

BUILD_MODE:初始化方式,默认为IMMEDIATE;

LAST_REFRESH_TYPE:上一次刷新的类型,FAST or COMPLETE;

LAST_REFRESH_DATE:上一次刷新的时间;

STALENESS:物化视图的状态;

STALE_SINCE:物化视图自刷新以来,第一次发生dml操作的时间;

以下是与快速刷新及查询重写相关的字段,后面会继续讲解:

FAST_REFRESHABLE:该字段反应了物化视图的快速刷新的能力;

REWRITE_ENABLED:是否支持查询重写;

REWRITE_CAPABILITY:该字段反应了物化视图的查询重写的能力。

2)与物化视图相关的常用过程

1> DBMS_MVIEW.REFRESH

该过程是最常用的刷新操作,使用方法也很简单,例如:

第一个参数为物化视图名,当前用户下可以省略所有者;

第二个为刷新方式(f:快速,c:全量,?:强制)。

2> DBMS_MVIEW.PURGE_LOG

该过程能够清空物化视图日志的内容,谨慎使用。例如:

3> DBMS_MVIEW.EXPLAIN_MVIEW

通过该过程可以深入了解物化视图的各种信息,该过程后面会讲到

4> DBMS_MVIEW.EXPLAIN_REWRITE

该过程可以分析一条语句能否实现物化视图的查询重写,后面会讲到。

5> DBMS_ADVISOR.TUNE_MVIEW

可以通过该过程,得到oracle的建议,实现物化视图的快速刷新,后面也会讲到。

 

未经允许不得转载:Oracle一体机用户组 » 物化视图使用手册(一)

相关推荐