ORACLE使用物化视图和查询重写功能Word格式.docx
- 文档编号:16809772
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:47
- 大小:39.65KB
ORACLE使用物化视图和查询重写功能Word格式.docx
《ORACLE使用物化视图和查询重写功能Word格式.docx》由会员分享,可在线阅读,更多相关《ORACLE使用物化视图和查询重写功能Word格式.docx(47页珍藏版)》请在冰豆网上搜索。
此外,您还可以在下列步骤中将鼠标放在每个单独的图标上,从而仅加载和查看与该步骤相关的屏幕截图。
您可以单击单个屏幕截图将其隐藏。
2概述
通过使用概要管理(SummaryManagement)特性,您可以减轻数据库管理员的工作负荷,这是因为您无需再手动创建概要,而且最终用户也不再必须知道已经定义的概要。
一旦您创建了一个或多个物化视图(它们和概要等效),最终用户就可以查询数据库中的表和视图。
Oracle服务器中的查询重写机制自动重写SQL查询以使用概要表。
这一机制减少了从查询返回结果的响应时间。
数据仓库内的物化视图对于最终用户或数据库应用程序来说是透明的。
Oracle数据库为您提供用于更先进的重写和刷新机制的增强功能,以及全面的顾问与调整框架。
这为您的环境带来了更加优化的物化视图,在占用额外空间最小的情况下提升了性能。
本教程不是物化视图的介绍。
它假定读者对物化视图功能有基本了解。
如果您需要更多关于某些主题的背景信息,请参看Oracle数据仓库指南。
案例
您将使用SALESHISTORY(SH)示例模式创建、修改并分析物化视图和重写功能。
此研讨会依赖于在默认安装上对SH模式进行的一些小修改。
开始本教程之前,您应该:
2.1完成了教程在Windows上安装Oracle数据库10g。
2.2下载mv.zip并将其解压缩到您的工作目录(c:
\wkdir)中。
为销售历史模式实施模式更改
在您启动物化视图功能前,需要对现有的SalesHistory模式对象进行一些更改,还必须将一些额外的系统权限授予用户SH。
使用SQL文件modifySH_10gR2.sql来应用这些更改。
1.启动一个SQL*Plus会话。
选择开始>
程序>
Oracle-OraDB10g_home>
ApplicationDevelopment>
SQLPlus。
(请注意:
本教程假设您拥有c:
\wkdir文件夹。
如果没有,则需要创建此文件夹,并将mv.zip的内容解压缩到此文件夹中。
在执行脚本时,可指定路经。
2.以SH用户的身份登录。
输入SH作为UserName,并输入SH作为Password。
然后单击OK。
2.3运行modifySH_10gR2.sql脚本。
获得的输出的底部应与以下图像匹配
从SQL*Plus会话运行modifySH_10gR2.sql脚本。
@c:
\wkdir\modifySH_10gR2.sql
获得的输出的底部应与以下图像匹配。
2.4执行xrwutl.sql脚本。
\wkdir\xrwutl.sql
2.4.1启用查询重写
要启用查询重写,需要满足以下条件:
单个物化视图必须有ENABLEQUERYREWRITE子句。
必须将QUERY_REWRITE_ENABLED初始化参数设置为TRUE(在10g中是默认设置)。
或者,您可以将此参数设置为FORCE,这将停用对重写计划的任何成本评估,并在任何可能的时候重写查询。
重写完整性模式和特定的物化视图状态必须匹配,以启用对这一特定物化视图的重写。
在这一部分中,将启用查询重写。
首先,需要确保有对数据库实例的基本初始化设置。
为此,请执行下列步骤:
2.5运行set_rewrite_session.sql
在登录到SH模式的SQL*Plus会话中,运行set_rewrite_session.sql,或将以下SQL语句复制到SQL*Plus会话中:
\wkdir\set_rewrite_session
ALTERSESSIONSETquery_rewrite_integrity=TRUSTED;
ALTERSESSIONSETquery_rewrite_enabled=FORCE;
showparametersquery
您将启用查询重写,并使用"
trusted"
模式。
这是最常用的完整性级别。
在trusted模式中,优化程序相信物化视图中的数据是最新的,在维度中声明的关系和RELY约束条件是正确的。
在这一方式中,优化程序还将使用预先建立的物化视图或基于视图的物化视图,并使用未执行和已经得到执行的关系。
在这一模式中,优化程序还"
信任"
已经声明但尚未ENABLEDVALIDATED的主/唯一键约束条件和通过维度指定的数据关系。
有关query_rewrite_integrity的级别和query_rewrite_enabled的更多详细信息,请参考Oracle据仓库指南
2.4.2分析物化视图更新和重写功能
Oracle数据库10g提供用于分析现有的和潜在的物化视图的过程。
这使您能够充分利用所有强大的物化视图功能。
要分析其潜在物化视图的刷新和重写功能,请执行以下步骤:
2.2.1在登录到SH模式的SQL*Plus会话中
2.6运行create_mv1.sql,
或将以下SQL语句复制到SQL*Plus会话中:
\wkdir\create_mv1.sql
DROPMATERIALIZEDVIEWcust_sales_mv;
CREATEMATERIALIZEDVIEWcust_sales_mv
BUILDIMMEDIATE
REFRESHFASTONCOMMIT
ENABLEQUERYREWRITE
AS
SELECTc.cust_id,SUM(amount_sold)ASdollar_sales
FROMsaless,customersc
WHEREs.cust_id=c.cust_id
GROUPBYc.cust_id;
这个语句失败并引发下面的错误:
ORA-23413:
table"
SH"
."
CUSTOMERS"
doesnothaveamaterializedviewlog.
您可以尝试手动纠正这个错误并尝试再次创建此物化视图。
但是,
这是一个循环反复而且耗时的过程。
而使用带有dbms_mviewpackage的Oracle数据库10g功能,您可以轻松修复这个错误。
您马上就会看到这一点。
2.7使用dbms_mview.explain_mviewpackage
2.即便这个语句成功,您也不能了解其快速刷新功能的详细信息。
通过使用dbms_mview.explain_mviewpackage,您对潜在物化视图的功能会有
更深入的了解,从而能够在其创建之前解决所有问题。
\wkdir\explain_mv1.sql
truncatetablemv_capabilities_table;
execdbms_mview.explain_mview(-
'
SELECTc.cust_id,SUM(amount_sold)ASdollar_sales–
FROMsaless,customersc–
WHEREs.cust_id=c.cust_id–
GROUPBYc.cust_id'
);
setserveroutputon
begin
forcrecin(selectcapability_name,possible,
related_text,msgtxt
frommv_capabilities_tableorderby1)loop
dbms_output.put_line(crec.capability_name||'
:
||crec.possible);
dbms_output.put_line(crec.related_text||'
||crec.msgtxt);
endloop;
end;
/
在输出中,您会看到系统指向SALES表上的另外一个缺少的物化视图日志。
建议始终在潜在物化视图创建之前,用上述dbms_mview.explain_mview
包对其进行分析,而非对系统进行反复试验(trial-and-error)。
除了CUSTOMERS和SALES上缺少的物化视图日志,系统还检测到您需要
向此物化视图添加额外的聚合函数,来为所有类型的DML操作全面启用快速刷新功能。
系统建议的聚合函数是:
"
COUNT(*)
COUNT(amount_sold)
dbms_mview.explain_mview包的输出在此显示。
您会发现,它不仅包括
物化视图的刷新功能,还包括物化视图的重写和分区变化跟踪
(Partition-Change-Tracking,PCT)功能我们会在后面讨论这些功能。
2.8创建上面识别出的物化视图日志。
create_mv_logs1.sql
要纠正这一点,首先创建上面识别出的物化视图日志。
\wkdir\create_mv_logs1.sql
DROPMATERIALIZEDVIEWLOGONsales;
CREATEMATERIALIZEDVIEWLOGONsales
WITHROWID,SEQUENCE
(prod_id,cust_id,time_id,channel_id,promo_id,
quantity_sold,amount_sold)
INCLUDINGNEWVALUES;
DROPMATERIALIZEDVIEWLOGONcustomers;
CREATEMATERIALIZEDVIEWLOGONcustomers
(cust_id,cust_first_name,cust_last_name,cust_gender,cust_year_of_birth
cust_marital_status,cust_street_address,cust_postal_code,cust_city
cust_state_province,country_id,cust_main_phone_number,cust_income_level
cust_credit_limit,cust_email)
INCLUDINGNEWVALUES;
这一个在后面会使用。
DROPMATERIALIZEDVIEWLOGONproducts;
CREATEMATERIALIZEDVIEWLOGONproducts
(prod_id,prod_name,prod_desc,prod_subcategory,prod_subcategory_desc
prod_category,prod_category_desc,prod_weight_class,prod_unit_of_measure
prod_pack_size,supplier_id,prod_status,prod_list_price,prod_min_price)
2.9再次检查潜在物化视图的功能。
explain_mv1a.sql
\wkdir\explain_mv1a.sql
TRUNCATETABLEmv_capabilities_table;
EXECdbms_mview.explain_mview(-
SELECTc.cust_id,SUM(amount_sold)ASdollar_sales,-
COUNT(amount_sold)AScnt_dollars,COUNT(*)-
FROMsaless,customersc–
BEGIN
forcrecin(selectcapability_name,possible,
END;
此潜在物化视图的快速刷新功能已经按期望进行了更改。
2.10现在创建物化视图。
create_mv1b.sql
\wkdir\create_mv1b.sql
SELECTc.cust_id,
SUM(amount_sold)ASdollar_sales,
COUNT(amount_sold)AScnt_dollars,
COUNT(*)AScnt
2.11explain_mview过程也使用现有的物化视图。
\wkdir\explain_mv1b.sql
EXECdbms_mview.explain_mview('
cust_sales_mv'
从用于物化视图的潜在SQL语句开始,您已经能够无须创建物化视图就能对其功能进行完全的分析了。
Oracle的查询重写功能
优化程序使用许多不同方法来重写查询。
第一个,也是最重要的步骤,是确定查询所请求的全部或部分结果是否可以从存储在物化视图的预计算结果中获得。
最简单的情况是,存储在物化视图中的结果与查询所请求的结果完全匹配。
Oracle优化程序通过将查询的文本与物化视图定义的文本相比较来作出这种类型的决定。
这种方法是最简单的,但符合这种查询重写类型的查询的数量最少。
当文本比较测试失败后,Oracle优化程序将基于联接、选择、分组、聚合以及抓取的列数据执行一系列通用检查。
这是通过将查询的不同子句(如SELECT、FROM、WHERE、HAVING或GROUPBY)与物化视图的不同子句分别进行比较而完成的。
使用部分文本匹配重写
在预先建立的表上创建物化视图
使用简单的联回(JoinBack)重写
分析重写过程
使用联回和卷积重写
使用复杂的联回和卷积重写
在数据的子集上创建物化视图
使用多物化视图重写
估计物化图的大小
分析重写过程
用联回和聚合卷积重写
最简单的重写机制是文本匹配重写。
在全文本匹配中,查询的整个文本会与物化视图定义的整个文本(即整个SELECT表达式)相比较-在文本比较过程中,忽略空白。
在全文本匹配失败后,优化程序会尝试进行部分文本匹配。
在这种方法中,以查询的FROM子句开始的文本会与以物化视图定义的FROM子句开始的文本进行比较。
2.12运行explain_rewrite1.sql
1.在登录到SH模式的SQL*Plus会话中,运行explain_rewrite1.sql,或将以下SQL语句复制到SQL*Plus会话中:
\wkdir\explain_rewrite1.sql
RemREWRITE
DELETEFROMplan_table;
COMMIT;
EXPLAINPLANFOR
SUM(amount_sold)ASdollar_sales
setlinesize132
setpagesize999
select*fromtable(dbms_xplan.display);
该计划显示,使用部分文本匹配重写机制,通过cust_sales_mvmaterialized视图重写了查询。
以FROM子句开始,SQL语句和物化视图是相同的。
在重写查询的同时,以与访问普通表相同的方法对物化视图的访问计划进行了研究,以便可以使用所有现有索引。
2.执行查询。
\wkdir\do_rewrite1.sql
settimingon
SELECTc.cust_id,
FROMsaless,customersc
WHEREs.cust_id=c.cust_id
GROUPBYc.cust_id;
这一查询的执行很快就有结果,因为它只访问在cust_sales_mv中已经联接和聚合的信息。
3.可以使用NOREWRITE提示来实施针对非编写语句的计划。
这使对一个查询是否重写的控制能下至语句级别。
\wkdir\explain_norewrite.sql
SELECT/*+norewrite*/
c.cust_id,
如果没有查询重写功能,您必须从SALES和与CUSTOMERS的联接进行完整扫描。
由于时间限制,不运行此查询。
在预先构建的表上创建物化视图:
在数据仓库环境中,有已经创建好的概要表或聚合表是很普遍的。
您无须通过创建新的物化视图来重复此工作。
这个解决方案将改善物化视图的性能,但是,它不能:
为所有SQL应用程序提供透明查询重写
在一个应用程序中透明地访问在另一个应用程序中定义的物化视图
普遍支持快速并行或快速物化视图刷新
由于这些限制,而且由于现有的物化视图可能极为巨大且重建起来费用高昂,Oracle数据库为您提供了将这些已经存在的概要表注册为物化视图的功能,从而避免了上述所有缺陷。
您可以用CREATEMATERIALIZEDVIEW...ONPREBUILTTABLE语句注册物化视图。
注册之后,物化视图可以用于查询重写,由刷新方法之一或二者一起维护。
Oracle为其现有客户群实现了这一功能,以提供安全的移植路径并保护投资。
移植带有"
手工制造"
的概要表和刷新过程的现有数据仓库环境,可以用单个DDL命令来利用重写功能,不会影响任何现有代码。
例如,MyCompany最近从Oracle9i移植到了Oracle10g,而且确实有这种手动创建的聚合表,在现有的所有数据仓库系统中90%以上也是如此。
要将现有的cust_id_sales_aggr表注册为物化视图,请执行以下步骤:
1.在登录到SH模式的SQL*Plus会话中,运行create_mv2.sql,或将以SQL语句复制到SQL*Plus会话中:
\wkdir\create_mv2.sql
DROPMATERIALIZEDVIEWcust_sales_aggr;
CREATEMATERIALIZEDVIEWcust_sales_aggr
ONPREBUILTTABLE
REFRESHFORCE
c.cust_last_name,
c.cust_first_name,
GROUPBYc.cust_id,
c.cust_first_name;
这个语句相当快。
它完全不会触及任何数据。
只是创建物化视图的元信息:
哪些联接,哪些聚合,涉及到哪些表和列。
对于查询重写的最高级别的数据完整性(query_rewrite_integrity=ENFORCED)而言,在预先建立的表上使用物化视图是不可能的,因为对于数据完整性,系统"
您是创建者。
只要您开始使用Oracle的刷新功能,系统就会知晓数据的完整性。
但是,在任何情况下,第一次刷新都会是一次完整
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 使用 物化 视图 查询 重写 功能
![提示](https://static.bdocx.com/images/bang_tan.gif)