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

物化视图的使用（一）

SQLCC++C#Access 
阅读更多

1.1 示例

该示例运行于oracle10.2.0.1版本。示例用户为sh。

默认安装oracle10g没有sh用户及相关示例表。

示例脚本在$ORACLE_HOME/demo/schema/下。

用system用户登陆sqlplus。运行@?/demo/schema/mksample.sql

修改sh用户密码 alter user sh identified by sh;

然后用sh登陆sqlplus.

SQL> conn sh/sh

已连接。

1.1.1 建立物化视图

SQL> create materialized view sales_mv

  2  as select p.prod_category,c.country_id,

  3  sum(s.quantity_sold) as quantity_sold,

  4  sum(s.amount_sold) as amount_sold

  5  from sales s,customers c,products p

  6  where s.cust_id=c.cust_id

  7  and s.prod_id=p.prod_id

  8  group by p.prod_category,c.country_id

  9  order by p.prod_category,c.country_id;

实体化视图已创建。

1.1.2 开启sql trace

SQL> set autot traceonly

SQL>

1.1.3 全文匹配的查询重写

1.1.3.1 分析SQL语句执行计划

SQL> select p.prod_category,c.country_id,

  2  sum(s.quantity_sold) as quantity_sold,

  3  sum(s.amount_sold) as amount_sold

  4  from sales s,customers c,products p

  5  where s.cust_id=c.cust_id

  6  and s.prod_id=p.prod_id

  7  group by p.prod_category,c.country_id

  8  order by p.prod_category,c.country_id

  9  ;

 

已选择81行。

执行计划

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

Plan hash value: 1941038050

 

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

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

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

 | Pstart| Pstop |

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

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

|   0 | SELECT STATEMENT       |           |    68 |  3264 |       |  2281  (11)| 00:00:28

 |         |       |

|   1 |  SORT GROUP BY         |           |    68 |  3264 |       |  2281  (11)| 00:00:28

 |         |       |

|*  2 |   HASH JOIN            |           |   918K|    42M|       |  2140   (5)| 00:00:26

 |         |       |

|   3 |    TABLE ACCESS FULL   | PRODUCTS  |    72 |  1512 |       |     3   (0)| 00:00:01

 |         |       |

|*  4 |    HASH JOIN           |           |   918K|    23M|  1200K|  2120   (5)| 00:00:26

 |         |       |

|   5 |     TABLE ACCESS FULL  | CUSTOMERS | 55500 |   541K|       |   333   (2)| 00:00:04

 |         |       |

|   6 |     PARTITION RANGE ALL|           |   918K|    14M|       |   434  (11)| 00:00:06

 |       1 |    28 |

|   7 |      TABLE ACCESS FULL | SALES     |   918K|    14M|       |   434  (11)| 00:00:06

 |       1 |    28 |

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

通过上述执行计划可以看出，该SQL没有查询重写。

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

分析查询重写需要用到rewrite_table表，该表需要通过脚本创建。

SQL> @?/rdbms/admin/utlxrw

 

表已创建。

 

SQL> execute dbms_mview.Explain_Rewrite(QUERY =>'select p.prod_category,c.country_id,sum(s

.quantity_sold) as quantity_sold,sum(s.amount_sold) as amount_sold from sales s,customers

c,products p where s.cust_id=c.cust_id and s.prod_id=p.prod_id group by p.prod_category,c.

country_id order by p.prod_category,c.country_id',MV => 'sales_mv',STATEMENT_ID => '42');

 

PL/SQL 过程已成功完成。

 

SQL> select message from rewrite_table where statement_id='42';

 

MESSAGE

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

QSM-01150: 未重写查询

QSM-01052: 表的引用完整性约束条件 PRODUCTS 在 ENFORCED 完整性模式中无效

QSM-01026: 对 SALES_MV 禁用查询重写.

1.1.3.3 开启查询重写

分析查询重写后发现，物化视图sales_mv禁用查询重写。

n  查看系统参数query_rewrite_enabled.

SQL> show parameter query_rewrite_enabled;

 

NAME                                 TYPE        VALUE

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

query_rewrite_enabled                string      TRUE

系统查询重写已经开启。

n  查看系统参数query_rewirte_ integrity

SQL> show parameter query_rewrite_integrity;

 

NAME                                 TYPE        VALUE

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

query_rewrite_integrity              string      enforced

系统查询重写完整性为强制模式

n  查看SALES_MV物化视图是否开启查询重写

SQL> select rewrite_enabled,rewrite_capability from user_mviews where mview_name='SALES_MV';

 

R REWRITE_C

- ---------

N GENERAL

R表示rewrite_enabled。值为N，说明SALES_MV没有开启查询重写。

 

SQL> alter materialized view sales_mv enable query rewrite;

 

实体化视图已更改。

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

SQL> truncate table rewrite_table;

 

表被截断。

 

SQL> execute dbms_mview.Explain_Rewrite(QUERY =>'select p.prod_category,c.country_id,s

.quantity_sold) as quantity_sold,sum(s.amount_sold) as amount_sold from sales s,custom

c,products p where s.cust_id=c.cust_id and s.prod_id=p.prod_id group by p.prod_categor

country_id order by p.prod_category,c.country_id',MV => 'sales_mv',STATEMENT_ID => '42

 

PL/SQL 过程已成功完成。

 

SQL> select message from rewrite_table where statement_id='42';

 

MESSAGE

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

QSM-01151: 已重写查询

QSM-01209: 已通过实体化视图 SALES_MV, 采用文本匹配算法进行了查询重写

通过分析我们得知该SQL语句可以查询重写。再查看下该SQL语句的执行计划

SQL> set autot traceonly;

SQL> set linesize 10000;

SQL> select p.prod_category,c.country_id,

  2  sum(s.quantity_sold) as quantity_sold,

  3  sum(s.amount_sold) as amount_sold

  4  from sales s,customers c,products p

  5  where s.cust_id=c.cust_id

  6  and s.prod_id=p.prod_id

  7  group by p.prod_category,c.country_id

  8  order by p.prod_category,c.country_id;

 

已选择81行。

 

 

执行计划

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

Plan hash value: 562924053

 

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

 

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

 

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

 

|   0 | SELECT STATEMENT              |          |    81 |  5346 |     4  (25)| 00:00:01 |

 

|   1 |  SORT ORDER BY                |          |    81 |  5346 |     4  (25)| 00:00:01 |

 

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

 

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

上述执行计划使用了查询重写。直接访问了SALES_MV物化视图。

 

1.1.4 部分匹配的查询重写

1.1.4.1 TRUSTED模式

1.1.4.1.1 分析SQL语句执行计划

SQL> 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;

 

执行计划

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

Plan hash value: 2103170481

 

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

--------

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

 Pstop |

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

--------

|   0 | SELECT STATEMENT      |          |     5 |   150 |   595  (35)| 00:00:08 |

         |

|   1 |  SORT GROUP BY        |          |     5 |   150 |   595  (35)| 00:00:08 |

         |

|*  2 |   HASH JOIN           |          |   918K|    26M|   453  (14)| 00:00:06 |

         |

|   3 |    TABLE ACCESS FULL  | PRODUCTS |    72 |  1512 |     3   (0)| 00:00:01 |

         |

|   4 |    PARTITION RANGE ALL|          |   918K|  8075K|   434  (11)| 00:00:06 |

    28 |

|   5 |     TABLE ACCESS FULL | SALES    |   918K|  8075K|   434  (11)| 00:00:06 |

    28 |

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

--------

执行计划没有查询重写，而是直接访问了基表。这是不我们想得到的结果。

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

SQL> set autot off;

SQL> truncate table rewrite_table;

 

表被截断。

 

SQL> execute dbms_mview.Explain_Rewrite(query=>'select upper(p.prod_category) as prod_

gory,sum(s.amount_sold) as amount_sold from sales s, products p where s.prod_id = p.pr

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

 

PL/SQL 过程已成功完成。

 

SQL> select message from rewrite_table where statement_id='42';

 

MESSAGE

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

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

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

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

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

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

QSM-01150: 未重写查询

QSM-01110: 由于实体化视图 SALES_MV 包含查询中不存在的表 (SALES and CUSTOMERS) 之间的联

这可能会清除查询所需的行, 因此无法对该视图执行查询重写

QSM-01052: 表的引用完整性约束条件 PRODUCTS 在 ENFORCED 完整性模式中无效

分享到：
评论
发表评论

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

相关推荐

    Oracle物化视图应用详解

    Oracle物化视图是一种数据库对象，它存储了查询结果，以提供快速的数据访问，特别适合于需要频繁查询但计算过程复杂或涉及大量数据连接的场景。物化视图的使用可以显著提高查询性能，因为它避免了每次查询时的计算...

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

    PostgreSQL 物化视图是一种特殊的视图，它可以存储数据，如同一个表一样，但它的数据可以根据原表的变化而变化。物化视图可以分为四种类型：快照物化视图、积极物化视图、消极物化视图和非常消极物化视图。 为什么...

    Oracle物化视图创建和使用

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

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

    Oracle物化视图是数据库管理系统中的一个重要特性，尤其在处理大量数据和复杂查询的场景下，它可以极大地提升查询性能和数据一致性。物化视图与普通的视图不同，后者是逻辑上的虚表，其内容在查询时动态计算，而物化...

    ORACLE9I物化视图

    为了应对这一挑战，Oracle 9i 引入了物化视图的概念，这是一种用于快速访问和报告数据的有效手段。 #### 引言 物化视图最早在 Oracle 8i 中引入，并且作为 Summary Management 组件的一部分。许多组织可能已经在...

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

    Oracle物化视图是一种在数据库中预先计算并存储视图查询结果的数据对象，它与普通的视图不同，普通视图在查询时动态地基于基表...学习和掌握物化视图的使用，对于数据库管理员和开发人员来说，是一项非常有价值的技术。

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

    Oracle数据库中的物化视图（Materialized View，简称MV）是一种强大的优化工具，它通过预先计算并存储查询结果，提供了一种快速访问复杂查询数据的方式。这种技术在数据仓库环境中尤其有用，因为数据仓库通常涉及...

    物化视图的快速刷新

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

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

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

    SQL 优化之 oracle物化视图

    2. Rowid 物化视图只有一个单一的主表，不能包括 Distinct 或者聚合函数、Group by、子查询、连接和 SET 操作。 刷新时间： 1. START WITH 子句：通知数据库完成从主表到本地表第一次复制的时间。 2. NEXT 子句：...

    物化视图的两种实现

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

    MySQL中实现物化视图

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

    物化视图，数据仓库，Oracle

    在这篇文章中，我们将深入探讨Oracle数据库中的物化视图及其在数据仓库环境中的应用，并提出一种有效的存储空间分配算法来优化物化视图和索引的选择。 #### 二、物化视图的概念与作用 ##### 2.1 定义 物化视图是...

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

    #### 一、物化视图及其日志介绍 在Oracle数据库中，物化视图(Materialized View)是一种特殊的对象，它存储了预计算查询的结果，从而可以提高查询性能。物化视图通常用于汇总查询、复杂查询等场景下，它可以显著减少...

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

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

    表分区及物化视图

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

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

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

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

    物化视图是一种预计算的数据集合，它可以存储查询的结果集，而查询重写则允许数据库自动地使用这些物化视图来替换复杂的查询语句，从而减少实时计算的开销，提升查询速度。 #### 二、重要概念 ##### 物化视图 - **...

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

    Doris 是一个开源的分布式分析型数据库，旨在帮助用户快速处理海量数据，提供了物化视图和索引等功能，以提高查询性能和数据的一致性。下面将详细介绍 Doris 的物化视图和索引的概念、应用场景和使用方法。 物化...

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

    ORACLE物化视图是指包含一个查询结果的数据库对象，相对于普通视图而言，物化视图是一个物理表。物化视图也是一种段，具有自己的物理存储属性，并占用数据库磁盘空间。物化视图可以基于表、视图、物化视图上创建；...

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