物化视图使用手册(三)

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

查询重写及实现

查询重写的概念

物化视图的查询重写,就是Oracle自动将对基表的查询转化为对物化视图的查询,以提高查询速度。

数据库对查询重写的控制

※要实现查询重写,首先物化视图本身要是enable query rewrite的。

※此外,系统级别或者会话级别需要开启允许查询重写功能:

※默认情况下,物化视图的查询重写,必需满足一致性。即当对物化视图的查询与对基表的查询得到相同结果时,也就是物化视图为Fresh状态时,才会查询重写。这一功能由query_rewrite_integrity这个参数控制。该参数有以下三个值:

1) ENFORCED

强制Oracle保证一致性和完整性,必须完全一致。也就是只有物化视图是Fresh状态时才允许重写。

2)STALE_TOLERATED

容忍不新鲜的数据,即使物化视图与基础数据不一致, 也仍可以进行重写。通常是当不新鲜的数据也有价值时才使用。只要存在物化视图,就对能够对SQL语句进行查询重写。

3)TRUSTED

信任数据库的一致性,完整性。我们可以通过设置一些错误的信息让优化器以为物化视图的约束规则是完整的,SQL语句是可以查询重写的,但是有可能真正的数据是有问题的。这一项比较复杂,通常需要创造一些违反约束的数据。一般用于测试环境。

查询重写的容量

查询重写的容量,也可以说是物化视图查询重写的能力,即REWRITE_CAPABILITY,Oracle能否将对一条语句的查询,转化为对物化视图的查询,不仅仅取决于上面的控制,还与物化视图的创建方式有关。不同的创建方式,会导致不同的重写容量。通常有以下三种情况:

  • NONE

这一项最简单,顾名思义就是无法实现查询重写。

  • TEXTMATCH

这一项从字面理解,是文本匹配。也就是说当查询sql,与创建物化视图的语句完全一致时才能够查询重写。完全一致是每个字符,空格都必须一致。如下所示:

上面创建的就是TEXTMATCH方式的查询重写,我们可以看看它的查询重写能力:

我们可以看到第一条语句与物化视图的创建语句完全一致,所以可以实现查询重写,而第二条语句,由于指定别名时少用了AS所以无法实现查询重写。

  • GENERAL

常规查询重写。这种重写方式是我们愿意见到的。当查询语句与物化视图创建语句不一致时也能够实现查询重写,如下所示:

这里我们创建了一个常规重写的物化视图,下面我们看看它是否能够实现常规的查询重写。

我们可以看到,查询的内容有所变化,它仍然能够实现查询重写。这就为查询语句提供了更多的灵活性,这样就能够实现,一个物化视图为多条语句提供重写功能。

判断语句是否能够查询重写

判断一条语句能否查询重写有以下两种方式:

  • 第一种就是查看语句的执行计划,如果执行计划中出现了物化视图MAT_VIEW REWRITE这一项,则该语句能够进行重写;
  • 除此之外,Oracle提供了一项功能,查询语句是否能够重写,使用该功能需要执行以下脚本生成rewrite_table表:

该功能使用方式如下:

不能查询重写的原因总结

这里总结几点常见的无法实现查询重写的原因.

  • 参数query_rewrite_enabled的设置。只有为true时才允许重写
  • 物化视图的定义,本身是否允许查询重写
  • 参数,query_rewrite_integrity设置为ENFORCED,但物化视图的状态不是Fresh。
  • 物化视图创建为TEXTMATCH类型,无法实现常规的重写。

当创建语句中包含group by语句时,group by后面的字段,也必须存在于select语句后,否则创建的物化视图为TEXTMATCH类型,如上面的例子就是;此外,当创建语句中包含union all语句时,该物化视图是TEXTMATCH类型。

疑难问题解析

物化视图日志为多个物化视图服务时的使用

如果只有一个物化视图使用基表的物化视图日志,日志的内容会在物化视图刷新后清空,当发生dml操作时,又会产生新的日志。但如果有两个甚至更多的物化视图使用基表的物化视图日志,日志的内容就不能在某一个物化视图刷新后清空,因为日志的内容还需要被其他的物化视图使用。那么当一个已经刷新过的物化视图发生第二次刷新时是怎么保证刷新的内容不重复呢?

物化视图日志中有一个SNAPTIME$$这一列,这已经在前面讲到过了。这一列表示的是物化视图的刷新时间,默认是一个很大的值。当该记录的这一列大于一个物化视图的上次刷新时间时,下一次该物化视图刷新将会使用这条记录,由于默认是很大的值,所有都会被使用。但如果物化视图日志为多个物化视图服务,那么该值在第一次被某个物化视图使用时,会被修改为该物化视图刷新的时间。而同时物化视图的最后刷新时间也会更改。Oracle就是通过这两个时间字段的比较来判断,刷新时是否使用这条记录。当所有物化视图的最后刷新时间都不小于该记录的这一时间字段时,记录再自动清理。

下面是一个物化视图日志为4个物化视图服务的例子。我们可以看到,只有当物化视图日志记录的SNAPTIME$$时间为默认的最大值时,刷新物化视图才会改变这一列的值为物化视图刷新的时间。
物化视图日志的使用

物化视图fresh状态时,数据却不是fresh的

通常当物化视图处于Fresh状态时,我们认为物化视图的数据与基表的数据是一致的。但某些时候,会出现特殊的情况,物化视图的状态为Fresh时,数据却是不新鲜的,甚至执行刷新操作,也无法让物化视图达到与基表一致的状态。

首先我们要识别出物化视图的不新鲜。这时候不能借助user_mviews视图,因为里面的状态是不准确的。我们可以让sql语句实现物化视图的重写(query_rewrite_integrity参数设置成ENFORCED,即只有在Fresh状态时才重写),把得到的结果与不重写的结果比较。会发现两者是不同的。如下面的例子:


Fresh状态的不新鲜举例

从上面的例子中,可以看到,当物化视图日志被某些原因清理之后,Oracle并不知道这个情况,刷新的时候跳过了这些记录。从而刷新得到Fresh状态的物化视图。也就是说ORACLE判断物化视图的新鲜与否,是依赖于是否应用了物化视图日志中所有符合刷新时间的记录来判断的。当所有符合刷新时间的记录被刷新操作所应用后,则刷新成功,该物化视图是Fresh状态。

当然通常我们不会手工去truncate物化视图日志,Oracle提供了专门的物化视图日志清理工具来完成这个工作。这部分内容已经在前部分讲过。但该功能要谨慎使用。


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

相关推荐