Oracle物化视图

 

oracle物化视图
                                      

物化视图对于前台数据库使用者来说如同一个实际的表,具有和一般表相通的如select等操作,而其实际上是一个视图,一个由系统实现定期刷新其数据的视图(具体刷新时间在定义物化视图的时候已有定义),使用物化视图更可以实现视图的所有功能,而物化视图确不是在使用时才读取,大大提高了读取速度,特别适用抽取大数据量表某些信息以及数据链连接表使用.具体语法如下:

 

物化视图对于前台数据库使用者来说如同一个实际的表,具有和表相通的一般select操作,而其实际上是一个视图,一个定期刷新数据的视图(具体刷新时间在定义物化视图的时候已有定义),使用物化视图可以实现视图的所有功能,而物化视图确不是在使用时才读取,大大提高了读取速度,特别适用抽取大数据量表某些信息以及数据链连接表使用.具体语法如下:

CREATE MATERIALIZED VIEW an_user_base_file_no_charge
             REFRESH COMPLETE START WITH SYSDATE 
            NEXT  TRUNC(SYSDATE+29)+5.5/24
   as
select distinct user_no
  from cw_arrearage t
 where (t.mon = dbms_tianjin.getLastMonth or
       t.mon = add_months(dbms_tianjin.getLastMonth, -1))

drop materialized view an_user_base_file_no_charge;

 

第 13 章 物化视图

　　8.1.5企业版/个人版开始支持

　　需要权限：GRANT CREATE MATERIALIZED VIEW，还必须直接赋予GRANT QUERY REWRITE.为实现查询重写，必须使用CBO.

　　13.1 物化视图如何工作

　　设置

　　COMPATIBLE参数必须高于8.1.0

QUERY_REWRITE_ENABLED = TRUE

QUERY_REWRITE_INTEGRETY =

  ENFORCED - 查询仅用Oracle强制与保证的约束、规则重写；

  TRUSTED – 查询除用Oracle强制与保证的约束、规则，也可用用户设定的数据间的任何关系来重写；

  STALE_TOLERATED – 即便Oracle知道物化视图中数据过期（与事实表等不同步），也重写查询。

　　创建物化视图的用户必须具有直接赋予的GRANT QUERY REWRITE权限，不能通过角色继承。

　　内部机制

　　全文匹配

　　部分匹配：从FROM子句开始，优化器比较之后的文本，然后比较SELECT列表

　　一般重写方法：

　　数据充分

　　关联兼容

　　分组兼容

　　聚集兼容

　　13.2 确保使用物化视图

　　约束

　　考虑到现实环境的数据量，可以将主键、外键、非空等约束置为NOVALIDATE，并调整QUERY_REWRITE_INTEGRITY为TRUSTED，这样可以达到“欺骗”数据库的目的，但必须注意如果无法保证此类约束的真实有效，查询改写后可能造成结果不精确。

　　维度

　　实际就是指明已存在的表中各列的归并关系，从而关联事实表后形成的物化视图可用于向“上”归并（相当于用表中代表更高归并关系的列关联事实表）。标准语法：

CREATE DIMENSION time_hierarchy_dim

  LEVEL day      IS time_hierarchy.day

  LEVEL mmyyyy   IS time_hierarchy.mmyyyy

  LEVEL yyyy     IS time_hierarchy.yyyy

HIERARCHY time_rollup

(day CHILD OF mmyyyy CHILD OF yyyy)

ATTRIBUTE mmyyyy

DETERMINES mon_yyyy;

　　13.3 DBMS_OLAP

　　估计（物化视图）大小

　　DBMS_OLAP.ESTIMATE_SUMMARY_SIZE（视图名， 视图定义， 估计行数， 估计字节数）；

　　其中后两个参数为NUMBER型输出参数。

　　维度有效性检查

　　DBMS_OLAP.VALIDATE_DIMENSION（视图名， 用户名， FALSE， FALSE）；

　　SELECT * FROM 维度表名

　　WHERE ROWIN IN （SEELCT bad_rowid FROM MVIEW$_EXCEPTION）；

　　所选出行即为不符合维度定义的行。

　　推荐物化视图

　　首先必须添加合适的外键，包通过外键来判定表之间的关系而不是维度。

　　DBMS_OLAP.RECOMMEND_MV（事实表名， 1000000000， ‘’）；

　　第二个参数表示物化视图可用的空间大小，可传入一个较大的数。第三个参数传入需要保留的特定物化视图，传入空即为不考虑其他物化视图。

执行C：\oracle\RDBMS\demo\sadvdemo后执行：

　　DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS

　　13.4 最后说明

　　物化视图不为OLTP系统设计

　　在事实表等更新时会导致物化视图行锁，从而影响系统并发性。