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

物化视图的使用（二）

SQLOracleAccess 
阅读更多
1.1.1.1.1 开启查询重写

从分析结果来看，SALES_MV在TRUESTED模式下已经过时。

最简单的方法是刷新sales_mv物化视图。但这种方式不是我们这章节要讲的内容。

在前面章节已经提到过基表数据和物化视图容器表数据不一致，存在脏数据的情况下，如果还需要使用查询重写。则需修改query_rewrite_integrity为stale_tolerated.

SQL> alter system set query_rewrite_integrity=stale_tolerated;

 

系统已更改。

1.1.1.1.2 分析语句是否可以查询重写

修改完毕后，再次分析查询重写

10G>truncate table rewrite_table;

 

表被截断。

 

10G>execute dbms_mview.Explain_Rewrite(query=>'select upper(p.prod_category) as prod_c

ory,sum(s.amount_sold) as amount_sold from sales s, products p where s.prod_id = p.pro

 group by p.prod_category order by p.prod_category',mv=>'sales_mv',statement_id=>'42')

 

PL/SQL 过程已成功完成。

 

10G>select message from rewrite_table where statement_id='42';

 

MESSAGE

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

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

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

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

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

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

QSM-01151: 已重写查询

QSM-01033: 已用实体化视图 SALES_MV 进行重写查询

通过分析表明，该SQL语句支持查询重写。我们再通过语句的执行计划看一下。

SQL> l

  1  select upper(p.prod_category),

  2  sum(s.amount_sold) as amount_sold

  3  from sales s,products p

  4  where s.prod_id=p.prod_id

  5  group by p.prod_category

  6* order by p.prod_category

SQL> /

 

 

执行计划

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

Plan hash value: 3163011340

 

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

 

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

 

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

 

|   0 | SELECT STATEMENT              |          |     5 |   105 |     4  (25)| 00:00:01 |

 

|   1 |  SORT GROUP BY                |          |     5 |   105 |     4  (25)| 00:00:01 |

 

|   2 |   MAT_VIEW REWRITE ACCESS FULL| SALES_MV |    81 |  1701 |     3   (0)| 00:00:01 |

 

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

很明显，已经访问了物化视图。

 

1.2 物化视图刷新

1.2.1 手动刷新

1.2.1.1 单线程刷新

1.2.1.1.1 Refresh

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

 

PL/SQL 过程已成功完成。

1.2.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.2.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.2.1.2 并行刷新

1.2.1.2.1 Refresh

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

 

PL/SQL 过程已成功完成。

1.2.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.2.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.2.2 自动刷新

1.2.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.2.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 的原因

不能快速刷新的原因少了很多。再看第一个原因。<spa

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

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

相关推荐

    Oracle物化视图应用详解

    物化视图的使用可以显著提高查询性能，因为它避免了每次查询时的计算过程，而是直接返回预先计算好的结果。在Oracle中，物化视图的应用广泛且灵活，可以通过不同的配置选项来满足不同的需求。 首先，创建物化视图的...

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

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

    Oracle物化视图创建和使用

    Oracle 物化视图创建和使用 Oracle 物化视图是一种预先计算并保存表连接或聚集等耗时较多的操作的结果，以提高查询性能。物化视图对应用程序透明，不会影响应用程序的正确性和有效性，但需要占用存储空间。基表发生...

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

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

    ORACLE9I物化视图

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

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

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

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

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

    物化视图的快速刷新

    物化视图的快速刷新 物化视图是 Oracle 中的一种性能优化技术，它可以将复杂的查询结果存储在一个物化视图中，以便快速地检索数据。物化视图有三种刷新方式：COMPLETE、FAST 和 FORCE。 COMPLETE 刷新方式会删除表...

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

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

    物化视图的两种实现

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

    MySQL中实现物化视图

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

    SQL 优化之 oracle物化视图

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

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

    #### 二、物化视图日志结构详解 物化视图日志包含以下列： 1. **ID**: 物化视图日志中记录的基表的ID值。 2. **NAME**: 物化视图日志中记录的基表的NAME值。 3. **M_ROW$$**: 基表的ROWID信息，用于定位到发生DML...

    物化视图，数据仓库，Oracle

    #### 二、物化视图的概念与作用 ##### 2.1 定义 物化视图是指预先计算并存储的结果集，这些结果集通常是基于复杂的查询语句或聚合操作得到的。与普通的视图不同，物化视图的数据被物理地存储在磁盘上，而不是每次...

    表分区及物化视图

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

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

    #### 二、物化视图的基本类型 根据刷新机制的不同，物化视图可以分为以下几种类型： 1. **ONDEMAND**：默认情况下，物化视图采用的是ONDEMAND模式，即只有当用户显式地请求刷新时，Oracle才会更新物化视图中的数据...

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

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

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

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

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

    第二，ORACLE物化视图的创建过程和管理方法。ORACLE物化视图是指包含一个查询结果的数据库对象，相对于普通视图而言，物化视图是一个物理表。物化视图也是一种段，具有自己的物理存储属性，并占用数据库磁盘空间。...

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

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

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