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 完整性模式中无效
发表评论
相关推荐
物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建物化视图创建
物化视图 (MV)在一个段中存储查询结果，并且能够在提交查询时将结果返回给用户，从而不再需要重新执行查询 — 在查询要执行几次时，这是一个很大的好处。物化视图可以利用一个快速刷新机制从基础表中全部或增量刷新...
Oracle物化视图应用详，有实例和各属性介绍
真正值得一看的mysql知识。MySQL中实现物化视图（中文版）翻译于外文。
orace表分区及物化视图 进一步了解oracle表分区技术及物化视图技术应用
Oracle物化视图使用[文].pdf
Oracle物化视图创建和使用，设置物化视图的自动刷新时间
Oracle中物化视图很重要，物化视图优秀论文. 在数据仓库中物化视图是非常重要的。Oracle中物化视图很重要，物化视图优秀论文. 在数据仓库中物化视图是非常重要的。Oracle中物化视图很重要，物化视图优秀论文. 在数据...
ORACLE9I物化视图
物化视图的两种实现，直接在物化视图中设置设计和通过定时任务触发存储过程来更新物化视图
物化视图的快速刷新
物化视图普通视图区别.doc，很不错的资料
物化视图学习笔记 错误问题分析总结 详细讲解了物化视图的内部构造，从零基础开始学习 重点说明ORA-12034的错误解决方案
oralce创建物化视图,基本语法，基本操作
Oracle怎么根据物化视图日志快速刷新物化视图
物化视图创建
数据仓库，数据挖掘，物化视图 数据仓库，数据挖掘，物化视图 数据仓库，数据挖掘，物化视图
里面详细讲述物化视图的种种创建，物化日志的结构，group by 语句创建物化视图的处理
学习分区表,索引,物化视图的使用.pdf
介绍通过物化视图对查询进行重写的一个例子，帮助大家理解查询重写的含义