`
kiki
  • 浏览: 4181 次
  • 性别: Icon_minigender_1
  • 来自: 北京
最近访客 更多访客>>
社区版块
存档分类
最新评论

物化视图的使用（三）

SQLOracleXPOSIBM 
阅读更多

1.1 物化视图刷新

1.1.1 手动刷新

1.1.1.1 单线程刷新

1.1.1.1.1 Refresh

SQL> exec dbms_mview.refresh(list=>'SH.SALES_MV');

 

PL/SQL 过程已成功完成。

1.1.1.1.2 refresh_all_mviews

SQL>set serveroutput on;

SQL> declare

  2  i number;

  3  begin

  4  dbms_mview.refresh_all_mviews(number_of_failures=>i);

  5  dbms_output.put_line(‘number_of_failures=>’||i);

  6  end;

  7  /

number_of_failures=>0

删除当前用户下的所有物化视图，除非对应的物化视图被标记为永不刷新。

Number_of_failures表示刷新物化视图失败个数。因为要刷新数据库中所有的物化视图，所以在生产环境上不建议使用。这将消耗大量的CPU及IO以及影响生产环境的正常使用。

1.1.1.1.3 refresh_dependent

SQL> declare

  2    i binary_integer;

  3  begin

  4    dbms_mview.refresh_dependent(number_of_failures => i,list=>'sh.sales_mv');

  5    dbms_output.put_line('number_of_failures=>'||i);

  6  end;

  7  /

number_of_failures=>0

Number_of_failures表示刷新物化视图失败个数。

1.1.1.2 并行刷新

1.1.1.2.1 Refresh

SQL> exec dbms_mview.refresh(list=>'SH.SALES_MV',parallelism => 2);

 

PL/SQL 过程已成功完成。

1.1.1.2.2 refresh_all_mviews

SQL>set serveroutput on;

SQL> declare

  2  i number;

  3  begin

  4  dbms_mview.refresh_all_mviews(number_of_failures=>i,parallelism => 2);

  5  dbms_output.put_line(‘number_of_failures=>’||i);

  6  end;

  7  /

number_of_failures=>0

删除当前用户下的所有物化视图，除非对应的物化视图被标记为永不刷新。

Number_of_failures表示刷新物化视图失败个数。在生产环境上不建议使用。这将消耗大量的CPU及IO以及影响生产环境的正常使用。

1.1.1.2.3 refresh_dependent

SQL> declare

  2    i binary_integer;

  3  begin

  4    dbms_mview.refresh_dependent(number_of_failures => i,list=>'sh.sales_mv', ,parallelism => 2);

  5    dbms_output.put_line('number_of_failures=>'||i);

  6  end;

  7  /

number_of_failures=>0

Number_of_failures表示刷新物化视图失败个数。

1.1.2 自动刷新

1.1.2.1 定时刷新

根据业务的需要，物化视图可以提供定时刷新的功能。其原理是Oracle的job任务调用对应的物化视图名称。根据Job触发的时间刷新物化视图。

SQL> alter materialized view sales_mv refresh complete on demand

  2  start with sysdate next sysdate+to_dsinterval('0 00:10:00');

 

实体化视图已更改。

每10分钟刷新sales_mv物化视图。

1.1.2.2 快速刷新

让物化视图支持快速刷新必须在对应的基表上建立物化视图日志。因为快速刷新需要日志来记录数据增量，这里可以设置基于ROWID来将增量同步到容器表。

在三个基表上建立物化视图日志

SQL> create materialized view log on sales with rowid;

 

实体化视图日志已创建。

 

SQL> create materialized view log on customers with rowid;

 

实体化视图日志已创建。

 

SQL> create materialized view log on products with rowid;

 

实体化视图日志已创建。

分析是否可以支持快速刷新

在分析前需要导入@?/rdbms/admin/utlxmv.sql.则会建立MV_CAPABILITIES_TABLE表。该表用于记录分析日志。

SQL> @?/rdbms/admin/utlxmv.sql

 

表已创建。

 

SQL> exec dbms_mview.explain_mview(mv=>'sh.sales_mv',stmt_id=>'42');

PL/SQL 过程已成功完成。

 

SQL> select capability_name,possible,msgtxt,related_text from mv_capabilities_table

  2  where statement_id='42'

  3  and capability_name like 'REFRESH_FAST_AFTER%'

  4  order by seq;

 

CAPABILITY_NAME                                    POSSIBLE   MSGTXT

                                               RELATED_TEXT

-------------------------------------------------- ---------- ----------------------------

---------------------------------------------------- --------------------

REFRESH_FAST_AFTER_INSERT                          N          实体化视图日志必须具有新值

                                               SH.PRODUCTS

REFRESH_FAST_AFTER_INSERT                          N          实体化视图日志不具有某些必需

的列                                                   SH.PRODUCTS

REFRESH_FAST_AFTER_INSERT                          N          实体化视图日志必须具有新值

                                               SH.CUSTOMERS

REFRESH_FAST_AFTER_INSERT                          N          实体化视图日志不具有某些必需

的列                                                   SH.CUSTOMERS

REFRESH_FAST_AFTER_INSERT                          N          实体化视图日志必须具有新值

                                               SH.SALES

REFRESH_FAST_AFTER_INSERT                          N          实体化视图日志不具有某些必需

的列                                                   SH.SALES

REFRESH_FAST_AFTER_ONETAB_DML                      N          使用 SUM(expr) 时, 未提供 CO

UNT(expr)                                              AMOUNT_SOLD

REFRESH_FAST_AFTER_ONETAB_DML                      N          使用 SUM(expr) 时, 未提供 CO

UNT(expr)                                              QUANTITY_SOLD

REFRESH_FAST_AFTER_ONETAB_DML                      N          查看禁用 REFRESH_FAST_AFTER_

INSERT 的原因

REFRESH_FAST_AFTER_ONETAB_DML                      N          在选择列表中不存在 COUNT(*)

REFRESH_FAST_AFTER_ONETAB_DML                      N          使用 SUM(expr) 时, 未提供 CO

UNT(expr)

REFRESH_FAST_AFTER_ANY_DML                         N          mv 日志没有序列号

                                               SH.PRODUCTS

REFRESH_FAST_AFTER_ANY_DML                         N          mv 日志没有序列号

                                               SH.CUSTOMERS

REFRESH_FAST_AFTER_ANY_DML                         N          mv 日志没有序列号

                                               SH.SALES

REFRESH_FAST_AFTER_ANY_DML                         N          查看禁用 REFRESH_FAST_AFTER_

ONETAB_DML 的原因

 

已选择15行。

 

不能快速刷新的原因有很多，我们先看第一个原因。“实体化视图日志必须具有新值”

而目前物化视图日志默认只有老值。所以要设置物化视图日志属性包括新值。

重建物化视图日志之前，先删除它。

SQL> drop materialized view log on sales;

 

实体化视图日志已删除。

 

SQL> drop materialized view log on customers;

 

实体化视图日志已删除。

 

SQL> drop materialized view log on products;

 

实体化视图日志已删除。

重建物化视图日志

SQL> create materialized view log on sales with rowid,sequence

  2  (cust_id,prod_id,quantity_sold,amount_sold) including new values;

 

实体化视图日志已创建。

 

SQL> create materialized view log on customers with rowid,sequence

  2  (cust_id,country_id) including new values;

 

实体化视图日志已创建。

 

SQL> create materialized view log on products with rowid,sequence

  2  (prod_id,prod_category) including new values;

 

实体化视图日志已创建。

重新分析快速刷新

SQL> exec dbms_mview.refresh(list=>'sh.sales_mv');

 

PL/SQL 过程已成功完成。

 

SQL> drop table mv_capabilities_table;

 

表已删除。

 

SQL> @?/rdbms/admin/utlxmv.sql

 

表已创建。

 

SQL> exec dbms_mview.explain_mview(mv=>'sh.sales_mv',stmt_id=>'42');

 

PL/SQL 过程已成功完成。

 

SQL> select capability_name,possible,msgtxt,related_text from mv_capabilities_table

  2  where statement_id='42'

  3  and capability_name like 'REFRESH_FAST_AFTER%'

  4  order by seq;

 

CAPABILITY_NAME                                    POSSIBLE   MSGTXT

                                               RELATED_TEXT

-------------------------------------------------- ---------- ----------------------------

---------------------------------------------------- --------------------

REFRESH_FAST_AFTER_INSERT                          Y

REFRESH_FAST_AFTER_ONETAB_DML                      N          使用 SUM(expr) 时, 未提供 CO

UNT(expr)                                              AMOUNT_SOLD

REFRESH_FAST_AFTER_ONETAB_DML                      N          使用 SUM(expr) 时, 未提供 CO

UNT(expr)                                              QUANTITY_SOLD

REFRESH_FAST_AFTER_ONETAB_DML                      N          在选择列表中不存在 COUNT(*)

REFRESH_FAST_AFTER_ANY_DML                         N          查看禁用 REFRESH_FAST_AFTER_

ONETAB_DML 的原因

不能快速刷新的原因少了很多。再看第一个原因。”使用 SUM(expr) 时, 未提供 CO

UNT(expr)”。意思是你在建物化视图时，要把count给加上。

先删除sales_mv.

SQL> drop materialized view sales_mv;

 

实体化视图已删除。

 

SQL> create materialized view SALES_MV

  2  refresh complete on demand

  3  enable query rewrite

  4  as

  5  select p.prod_category,c.country_id,

  6  sum(s.quantity_sold) as quantity_sold,

  7  count(s.quantity_sold) as quantity_sold_count,

  8  sum(s.amount_sold) as amount_sold,

  9  count(s.amount_sold) as amount_sold_count,

 10  count(*) as starX

 11  from sales s,customers c,products p

 12  where s.cust_id=c.cust_id

 13  and s.prod_id=p.prod_id

 14  group by p.prod_category,c.country_id;

实体化视图已创建。

再分析下

SQL> drop table mv_capabilities_table;

 

表已删除。

 

SQL> @?/rdbms/admin/utlxmv.sql

 

表已创建。

 

SQL> exec dbms_mview.explain_mview(mv=>'sh.sales_mv',stmt_id=>'42');

 

PL/SQL 过程已成功完成。

 

SQL> select capability_name,possible,msgtxt,related_text from mv_capabilities_table

  2  where statement_id='42'

  3  and capability_name like 'REFRESH_FAST_AFTER%'

  4  order by seq;

 

CAPABILITY_NAME                P MSGTXT                                             RELATE

D_TE

------------------------------ - -------------------------------------------------- ------

----

REFRESH_FAST_AFTER_INSERT      Y

REFRESH_FAST_AFTER_ONETAB_DML  Y

REFRESH_FAST_AFTER_ANY_DML     Y

通过上面的分析，sales_mv已经可以支持快速刷新了。

我们做下测试

SQL> exec dbms_mview.refresh(list=>'sh.sales_mv',method=>'f');

 

PL/SQL 过程已成功完成。

1. 物化视图刷新效率

根据测试，比如对100万记录的表，N并行刷新需要的时间。

拿Oracle10g sh用户下的sales表和customers为例

Sales表数据量为918843

Customers数据量为55500

测试环境为IBM ThinkPad X61 Duo CPU T8300 @2.40GHz.内存3G。OS环境为:Win XP Sp3

创建物化视图sales1_mv

10GEN-U

分享到：
物化视图的使用（二）
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle物化视图应用详解

    物化视图有三种类型： 1. **包含聚集的物化视图**：如包含SUM、COUNT等聚合函数的结果。 2. **只包含连接的物化视图**：存储了多表连接后的结果。 3. **嵌套物化视图**：物化视图中包含了其他物化视图。 对于快速...

    Oracle物化视图创建和使用

    物化视图有三种：聚集物化视图、包含连接物化视图、嵌套物化视图。每种物化视图都有其快速刷新的限制条件，但其他方面则区别不大。 在学习 Oracle 物化视图时，需要了解物化视图的概念、创建和删除物化视图、设置...

    PostgreSQL物化视图的刷新机制.pptx

    为什么使用物化视图？在执行某些查询时，效率较低，传统方法（例如，索引或查询优化）无法显著提高效率。这时使用的方法是将需要查询的数据事先进行查询（pre-querying）并储存起来，这样每次查询时就不需要都从头...

    Oracle物化视图使用[文].pdf

    Oracle物化视图是数据库管理系统中的一个重要特性，尤其在处理大量数据和复杂查询的场景下，它可以极大地提升查询性能和数据一致性。...理解并掌握物化视图的使用，对于开发高效能的OLAP和OLTP系统具有重要意义。

    ORACLE9I物化视图

    - **汇总连接返回**：当查询部分匹配物化视图时，可以使用物化视图和其他表进行连接操作。 - **汇总汇总与聚合到所有**：在多层汇总的情况下，可以从下级物化视图向上级物化视图进行汇总。 - **数据子集**：如果...

    物化视图的快速刷新

    物化视图有三种刷新方式：COMPLETE、FAST 和 FORCE。 COMPLETE 刷新方式会删除表中所有的记录，然后根据物化视图中查询语句的定义重新生成物化视图。FAST 刷新方式采用增量刷新的机制，只将自上次刷新以后对基表进行...

    Oracle数据库中物化视图的原理剖析

    在Oracle 10g之前，要检查物化视图是否配置正确，需要使用DBMS_MVIEW包的EXPLAIN_MVIEW和EXPLAIN_REWRITE过程，这些过程只能简单地表明某个特性（如快速刷新或查询重写）可能适用于物化视图，但不会提供具体实现建议...

    oracle物化视图_循序渐进学习笔记

    例如，为了提高性能，可以启用物化视图的并行刷新，或者使用物化视图日志来追踪基表的更改，以便进行精确的增量刷新。同时，需要注意物化视图与基表之间的依赖关系，以及物化视图在数据库中的空间占用。 总之，...

    物化视图普通视图区别，很不错的资料

    物化视图与普通视图是数据库中两种不同的视图类型，它们在功能和使用上有显著的区别。普通视图，也称为虚拟视图，是一种逻辑上的表，它并不实际存储数据，而是根据定义的SQL查询在运行时动态生成结果。这意味着每次...

    物化视图的两种实现

    在使用物化视图时，还需要注意一些问题。首先，物化视图会占用额外的存储空间，因此需要考虑数据库的整体容量规划。其次，物化视图的维护可能会增加数据库的复杂性，特别是当有多个物化视图或复杂的更新逻辑时。最后...

    MySQL中实现物化视图

    真正值得一看的mysql知识。MySQL中实现物化视图（中文版）翻译于外文。

    Oracle怎么根据物化视图日志快速刷新物化视图

    #### 三、物化视图日志如何支持多物化视图的快速刷新 物化视图日志不仅可以支持单个物化视图的快速刷新，还可以支持多个物化视图的快速刷新。这主要依赖于**SNAPTIME$$**列的作用。 1. **SNAPTIME$$的作用**：每当...

    SQL 优化之 oracle物化视图

    Oracle 物化视图 Oracle 物化视图是数据库对象，存储远程表的数据副本，也可以称为快照。物化视图可以查询表、视图和其他物化视图。通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。 创建...

    物化视图，数据仓库，Oracle

    #### 三、Oracle中的物化视图管理 Oracle数据库提供了丰富的功能来支持物化视图的创建和管理，包括但不限于： - **创建物化视图**：使用CREATE MATERIALIZED VIEW语句创建物化视图。 - **刷新物化视图**：定期或按...

    表分区及物化视图

    orace表分区及物化视图 进一步了解oracle表分区技术及物化视图技术应用

    oracle 物化视图详解(内含例子)

    下面通过具体的步骤来展示如何创建和使用物化视图。 ##### 示例1：创建ONDEMAND物化视图 1. **创建表**： ```sql CREATE TABLE test1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30)); ``` 这里创建了一个名为`...

    物化视图创建脚本物化视图创建脚本

    物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建

    ORACLE使用物化视图和查询重写功能

    ### ORACLE使用物化视图和查询重写功能 #### 一、概述 在Oracle数据库中，物化视图和查询重写功能是提高查询效率和简化数据仓库管理的重要工具。物化视图是一种预计算的数据集合，它可以存储查询的结果集，而查询...

    1-5+Doris物化视图、索引的典型应用案例.pdf

    下面将详细介绍 Doris 的物化视图和索引的概念、应用场景和使用方法。 物化视图 Doris 的物化视图是一种特殊的表，存储了预先计算好的数据集，使得用户可以快速查询数据。物化视图适用于以下场景： * 需要覆盖...

    利用ORACLE物化视图建立报表数据库.pdf

    第三，物化视图在建立报表数据库中的应用。物化视图建立报表数据库是利用物化视图的复制功能。我们生产数据库中的表称为基表，在报表数据库中建立物化视图完整复制生产数据库中的基表。我们有两个数据库，一个是生产...

Magicbox
Global site tag (gtag.js) - Google Analytics