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

功能在本教程中，您将了解如何利用强大的物化视图和查询重写功能。所需时间约2个小时1.2主题为销售历史模式实施模式更改分析物化视图更新和重写功能Oracle的查询重写功能GROUPBY项的增强的重写功能物化视图PMARKER的物化视图TUNEMVIEW快速刷新图将鼠标置于此图标上以加载和查看本教程的所有屏幕截图。(警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。)屏幕截图将其隐藏。2概述通过使用概要管理(SummaryManagement)特性，您可以减轻数据库管理员的工作负荷，这是因为您无需再手动创建概要，而且最终用户也不再必须知道已经定义的概要。一旦您创建了一个或多个物化视图(它们和概要等效)，最终用户就可以查询数据库中的表和视图。Oracle服务器中的查询重写机制自动重写SQL查询以使用概要表。这一机制减少了从查询返回结果的响应时间。数据仓库内的物化视图对于最终用户或数据库应用程序来说是透明的。Oracle数据库为您提供用于更先进的重写和刷新机制的增强功能，以及全面的顾问与调整框架。这为您的环境带来了更加优化的物化视图，在占用额外空间最小的情况下提升了性能。注意：本教程不是物化视图的介绍。它假定读者对物化视图功能有基本了解。如果您需要更多关于您将使用SALESHISTORY(SH)示例模式创建、修改并分析物化视图和重写功能。此研讨会依赖于在默认安装上对SH模式进行的一些小修改。前提条件2.1完成了教程在Windows上安装Oracle数据库10g。2.2下载mv.zip并将其解压缩到您的工作目录(c:\wkdir)中。为销售历史模式实施模式更改在您启动物化视图功能前，需要对现有的SalesHistory模式对象进行一些更改，还必须将一些额外(请注意：本教程假设您拥有c:\wkdir文件夹。如果没有，则需要创建此文件夹，并将mv.zip的内容解压缩到此文件夹中。在执行脚本时，可指定路经。)OK。2.3运行modifySH_10gR2.sql脚本。获得的输出的底部应与以下图像匹配获得的输出的底部应与以下图像匹配。2.4执行xrwutl.sql脚本。获得的输出的底部应与以下图像匹配获得的输出的底部应与以下图像匹配。2.4.1启用查询重写要启用查询重写，需要满足以下条件：单个物化视图必须有ENABLEQUERYREWRITE子句。您可以将此参数设置为FORCE，这将停用对重写计划的任何成本评估，并在任何可能的时候重写查询。重写完整性模式和特定的物化视图状态必须匹配，以启用对这一特定物化视图的重写。在这一部分中，将启用查询重写。首先，需要确保有对数据库实例的基本初始化设置。为此，请执行下列步骤：2.5运行set_rewrite_session.sqlSHSQLPlussetrewritesessionsql或将以下SQL语句复制到SQL*Plus会话中：E您将启用查询重写，并使用"trusted"模式。这是最常用的完整性级别。在trusted模式中，优化程序相信物化视图中的数据是最新的，在维度中声明的关系和RELY约束条件是正确的。在这一方式中，优化程序还将使用预先建立的物化视图或基于视图的物化视图，并使用未执行和已经得到执行的关系。在这一模式中，优化程序还"信任"已经声明但尚未ENABLEDVALIDATED的主/唯一键约束条件和通过维度指定的数据关系。指南2.4.2分析物化视图更新和重写功能Oracle数据库10g提供用于分析现有的和潜在的物化视图的过程。这使您能够充分利用所有强大的要分析其潜在物化视图的刷新和重写功能，请执行以下步骤：2.2.1在登录到SH模式的SQL*Plus会话中2.6运行create_mv1.sql，或将以下SQL语句复制到SQL*Plus会话中：DROPMATERIALIZEDVIEWcust_sales_mv;CREATEMATERIALIZEDVIEWcust_sales_mvBUILDIMMEDIATEREFRESHFASTONCOMMITENABLEQUERYREWRITE这个语句失败并引发下面的错误：您可以尝试手动纠正这个错误并尝试再次创建此物化视图。但是，eg您可以轻松修复这个错误。您马上就会看到这一点。2.7使用dbms_mview.explain_mviewpackage2.即便这个语句成功，您也不能了解其快速刷新功能的详细信息。更深入的了解，从而能够在其创建之前解决所有问题。/在输出中，您会看到系统指向SALES表上的另外一个缺少的物化视图日志。w包对其进行分析，而非对系统进行反复试验(trial-and-error)。除了CUSTOMERS和SALES上缺少的物化视图日志，系统还检测到您需要向此物化视图添加额外的聚合函数，来为所有类型的DML操作全面启用快速刷新功能。"COUNT(*)物化视图的刷新功能，还包括物化视图的重写和分区变化跟踪 T2.8创建上面识别出的物化视图日志。create_mv_logs1.sql要纠正这一点，首先创建上面识别出的物化视图日志。DROPMATERIALIZEDVIEWLOGONsales;CREATEMATERIALIZEDVIEWLOGONsalesWITHROWID,SEQUENCEINCLUDINGNEWVALUES;DROPMATERIALIZEDVIEWLOGONcustomers;CREATEMATERIALIZEDVIEWLOGONcustomersWITHROWID,SEQUENCEINCLUDINGNEWVALUES;使用。DROPMATERIALIZEDVIEWLOGONproducts;CREATEMATERIALIZEDVIEWLOGONproductsWITHROWID,SEQUENCEeINCLUDINGNEWVALUES;2.9再次检查潜在物化视图的功能。explain_mv1a.sqlBEGINEND;/此潜在物化视图的快速刷新功能已经按期望进行了更改。DROPMATERIALIZEDVIEWcust_sales_mv;CREATEMATERIALIZEDVIEWcust_sales_mvBUILDIMMEDIATEREFRESHFASTONCOMMITENABLEQUERYREWRITECOUNT(*)AScnt2.11explain_mview过程也使用现有的物化视图。/从用于物化视图的潜在SQL语句开始，您已经能够无须创建物化视图就能对其功能进行完全优化程序使用许多不同方法来重写查询。第一个，也是最重要的步骤，是确定查询所请求的全部或部分结果是否可以从存储在物化视图的预计算结果中获得。EXPLAINPLANFOR最简单的情况是，存储在物化视图中的结果与查询所请求的结果完全匹配。Oracle优化程序通过将查询的文本与物化视图定义的文本相比较来作出这种类型的决定。这种方法是最简单的，但符合这种查询重写类型的查询的数量最少。当文本比较测试失败后，Oracle优化程序将基于联接、选择、分组、聚合以及抓取的列数据执行一系列通用检查。这是通过将查询的不同子句(如SELECT、FROM、WHERE、HAVING或GROUPBY)与物化视图的不同子句分别进行比较而完成的。使用部分文本匹配重写在预先建立的表上创建物化视图使用简单的联回(JoinBack)重写分析重写过程使用联回和卷积重写使用复杂的联回和卷积重写在数据的子集上创建物化视图使用多物化视图重写估计物化图的大小分析重写过程用联回和聚合卷积重写使用部分文本匹配重写最简单的重写机制是文本匹配重写。在全文本匹配中，查询的整个文本会与物化视图定义的整SELECT全文本匹配失败后，优化程序会尝试进行部分文本匹配。在这种方法中，以查询的FROM子句开始的文本会与以物化视图定义的FROM子句开始的文本进行比较。2.12运行explain_rewrite1.sqlSHSQLPlus行explain_rewrite1.sql，或将以下SQL语句复制到SQL*Plus会话中：RemREWRITELETEFROMplantableCOMMIT;rialized以FROM子句开始，SQL语句和物化视图是相同的。在重写查询的同时，以与访问普通表相同的方法对物化视图的访问计划进行了研究，以便可以2.执行查询。3.可以使用NOREWRITE提示来实施针对非编写语句的计划。这使对一个查询是否重写的语句级别。LETEFROMplantableCOMMIT;EXPLAINPLANFORSELECT/*+norewrite*/FROMWHERE如果没有查询重写功能，您必须从SALES和与CUSTOMERS的联接进行完整扫描。注意：由于时间限制，不运行此查询。表上创建物化视图：在数据仓库环境中，有已经创建好的概要表或聚合表是很普遍的。您无须通过创建新的物化视为所有SQL应用程序提供透明查询重写在一个应用程序中透明地访问在另一个应用程序中定义的物化视图普遍支持快速并行或快速物化视图刷新制，而且由于现有的物化视图可能极为巨大且重建起来费用高昂，Oracle数据库为您提供了将这些已经存在的概要表注册为物化视图的功能，从而避免了上述所有缺陷。您可以用CREATEMATERIALIZEDVIEW...ONPREBUILTTABLE语句注册物化视图。注册之后，物化视图可以用于查询重写，由刷新方法之一或二者一起维护。何现有代码。到SH模式的SQL*Plus会话中，运行create_mv2.sql，或将以SQL语句复制到SQL*Plus会话中：DROPMATERIALIZEDVIEWcust_sales_aggr;CREATEMATERIALIZEDVIEWcust_sales_aggrONPREBUILTTABLEREFRESHFORCEENABLEQUERYREWRITECOUNT(*)AScnt合，涉及到哪些表和列。对于查询重写的最高级别的数据完整性(query_rewrite_integrity=ENFORCED)而言，在预先建立的表上使用物化视图是不可能的，因为对于数据完整性，系统"信任"您是创建者。只要您开始使e使用简单的联回重写当文本比较测试失败后，Oracle优化程序将基于联接、选择、分组、聚合以及抓取的列数据执BY)与物化视图的不同子句分别进行比较而完成的。要使查询重写发生，查询不必始终都完全匹custidsales1.在登录到SH模式的SQL*Plus会话中，运行explain_rewrite2.sql，或将以下SQL语句复制到SQL*Plus会话中：LETEFROMplantableCOMMIT;EXPLAINPLANFORORDERBY1;SELECTFROMTABLEdbmsxplan.display);在此计划中，您可以看到，通过使用cust_id列(它是物化视图的组成部分，并表示sales表与customers表之间的主键-外键关系)，Oracle使用了cust_sales_mv物化视图并将其联接回统知道不必在维点上进行额外聚合。它只须将物化视图联回到customers表。关于层次结构和确定属性的信息是customers_dim(用于customers维的Oracle维对象)的组成部分。维定义…ATTRIBUTEcustomerDETERMINES注意：关于维对象的更多信息，请参看Oracle数据仓库指南。2.执行查询。请注意，只计算结果，而且实际上并不输出(spoolout)所有返回的记录。SELECTCOUNT(*)ORDERBY1;3.用于非重写查询的计划可以通过以下语句显示：DELETEFROMplan_table;COMMIT;EXPLAINPLANFORORDERBY1;分析重写过程若要了解系统中查询重写的可能候选对象的详细信息，可以使用dbms_mview.explain_rewrite过程。要分析之前执行的查询并更深入地了解重写过程，请执行以下步骤：1.在登录到SH模式的SQL*Plus会话中，运行analyze_rewrite2.sql，或将以下SQL语句复制到SQL*Plus会话中：TRUNCATETABLErewrite_table;DECLAREBEGINEND;/rewritetable视图也可进行重写了。在这种情况下，优化程序会基于成本做出决策。使用联回和卷积重写除了物化视图的简单联回(请求相同聚合级的信息)，物化视图还可以聚合到一个更高的级别，1.在登录到SH模式的SQL*Plus会话中，运行explain_rewrite3.sql，或将以下SQL语句复制到SQL*Plus会话中：LETEFROMplantableCOMMIT;EXPLAINPLANFORFROMWHERE在此计划中，您可以看到，通过使用cust_id列(它是物化视图的组成部分，并表示sales表与customers表之间的主键-外键关系)，Oracle使用cust_sales_mv物化视图并将其联接回到但是，可能的联接键列的存在并不一定代表了查询重写机制保证数据完整性所需的全部信息。yHIERARCHYgeog_rollup(CHILDOFCHILDOFCHILDOF所请求的cust_state_province属性表示州级，这是比用户级(由cust_id表示)更高的聚合级。级别和层次代表一种声明方法，用来表示一个表内的1:n关系。在这里，它表示在不影响数据完整性的情况下，将所有客户信息汇总到州级的有效性。每个不同的客户值都将获得一个唯一的州值。2.现在提交查询。其运行速度相当快。SELECTCOUNT(*)FROMWHERE使用复杂的联回和卷积重写下面的示例演示了查询重写功能的强大能力和灵活性。以下示例不但进行了联回，而且还使用1.在登录到SH模式的SQL*Plus会话中，运行explain_rewrite4.sql，或将以下SQL语句复制到SQL*Plus会话中：LETEFROMplantableCOMMIT;EXPLAINPLANFORORDERBY1,2;视图。必须进行各种数据完整性检查，以保证使用此物化视图的有效性。除了检查丢失或非复制联接外，对维信息的评估在重写过程中也扮演着重要角色。检查优化程使用什么来重写这个查询。以下是customers_dim的维定义摘录。它显示了这个查LEVEL...LEVELstateLEVELcountryHIERARCHYgeog_rollup(FCHILDOFCHILDOF)Oracle数据库必须确定，它是否可以基于存储在该物化视图中的信息导出所有被请求的属性。ststateprovincee确定。它表示维中比客户级更高的聚合级。户级更高的聚合级。im联接条件是维信息的组成部分。rs与countries联接，以获得查询的结果，并保证查询结果正确。2.重写的SQL语句如下所示：3.现在执行该查询。重写即可获得此计划，可以运行explain_norewrite4.sql，或将以下SQL语句复制到SQL*Plus会话中。COMMIT;EXPLAINPLANFOR请注意，在这个计划中未出现物化视图重写。在数据的子集上创建物化视图很多时候，只是考虑对大型事实表中的信息子集进行更多的分析。以前，在这种情况下，为了利用物化视图，您需要为事实表创建一个包含所有信息的物化视图。现在，您可以在物化视图中并入一个谓词条件，以允许TEXTMATCH只重写功能。要在数据子集上创建一个物化视图，并将其创建间和大小与不带谓词的、包含相同联接、聚合以及完整数据集的物化视图进行比较，请执行以下步骤：1.在登录到SH模式的SQL*Plus会话中，运行create_mv3.sql，或将以下SQL语句复制BUILDIMMEDIATEd记录下创建物化视图所耗费的时间。2.现在，创建相同的物化视图，只是其中无需谓词来限制结果集。vBUILDIMMEDIATE这样创建物化视图用了更长的时间，这是由于所有数据都必须被触及、联接、聚合。使用多物化视图重写Oracle数据库10g第2版中的新增内容是，为了解决查询，可使用多个物化视图进行查询重1.创建以下两个物化视图，其中只包含2001年11月和12月的数据在登录到SH模式的SQL*Plus会话中，运行cr_qw_mmv.sql，或将以下SQL语句复制到vND2.现在执行一个查询，获取在11月5日至12月15日期间按客户分类的销售额。运行COMMIT;EXPLAINPLANFOR估计物化视图的大小除了创建时间加快以外，存储物化视图所使用的空间也减少了。您可以查询数据字典来获得关于物化视图大小的信息。遗憾的是，这只有在已经创建了物化视图后才能进行。理论上，您会希望在创建物化视图之前就能获得这些信息，尤其是在非常大的环境要使用Oracle数据库来估计两个已创建的物化视图的大小，并将其与它们的真实大小相比较，lusestimatemvsizesqlSQL句复制到SQL*Plus会话中。这将为您提供包含所有数据的物化视图的估计大小。AND/记录下创建物化视图所耗费的时间。2.确定包含数据子集的物化视图的大小：l/3.现在，查看数据字典以获取两个新物化视图的实际大小。EXPLAINPLANFOR只包含数据子集的物化视图与包含所有数据的视图相比，前者的大小是后者的1/9。特别是在大型环境中，这会使用户获益匪浅，并简化了物化视图针对"特殊分析"的使用，只触及数据仓库中分析重写过程对于能通过只包含数据子集的物化视图满足的查询，要检查其查询重写机制的决策过程，请执语句复制到SQL*Plus会话中：/您可以看到，选择子集物化视图的原因在于，较之包含所有数据的视图，其成本更低。用联回和聚合卷积重写像任何其他物化视图一样，包含数据子集的物化视图可以用于查询重写。只须通过数据容量检查，这些物化视图就能够用于重写。以下示例是将子集物化视图用于查询，其中，联回和聚合卷积是必不可少的。语句复制到SQL*Plus会话中：COMMIT;ANDANDANDAND带有GROUPBY扩展项的增强的重写功能BYGROUPBY视图必须满足两个附加条件后，方可用于重写：b),(a,b))将使物化视图不能进行通用重写。并将其用于重写。1.创建包含扩展GROUPBY表达式的物化视图：在登录到SH模式的SQL*Plus会话中，执行以下SQL语句：vd。2.在登录到SH模式的SQL*Plus会话中，执行以下SQL语句：COMMIT;EXPLAINPLANFOR当物化视图和查询都包含GROUPBY扩展时，Oracle将两个策略用于重写：分组匹配和UNIONALL重写。首先，Oracle尝试分组匹配。查询中的分组与物化视图中的分组进行匹配，如筛选条件的情况下，对物化视图进行全表扫描，以满足我们的查询。以下查询包含了与物化视图定3.在登录到SH模式的SQL*Plus会话中，执行以下SQL语句：COMMIT;EXPLAINPLANFORAND被放置在独立的UNIONALL分支中。这些分支都将具有一个简单的GROUPBY子句。LL分支的重写功能。每个分支都可能由包含一个简单(或扩展)GROUPBY条件的物化视图改写。所有基本重写机制(如JOINBACK)都会用到。4.在登录到SH模式的SQL*Plus会话中，执行以下SQL语句：COMMIT;EXPLAINPLANFORAND使用重写或错误来控制语句的执行可能会有这种情况，即如果查询不重写，则希望停止执行该查询。这种情况之一是，您预期未名为REWRITE_OR_ERROR的新提示。这是一个查询块级提示。例如，如果SELECT语句没有进行重写，则会引发显示在消息中的错误。REWRITE_OR_ERROR提示使您能够在查询中运行DBMS_MVIEW.EXPLAIN_REWRITE()，解决导致重写失败的问题，并再次运行查询。1.准备环境以确保重新可能的查询。ALTERMATERIALIZEDVIEWALTERMATERIALIZEDVIEWALTERMATERIALIZEDVIEWALTERMATERIALIZEDVIEWALTERMATERIALIZEDVIEW2.以下语句通过任何物化视图都不会进行重写，如计划输出所示。EXPLAINPLANFOR分区与物化视图具备已分区的事实表会为物化视图带来两个额外的好处。如果只有某些分区发生了变化(由于DML或分区维护操作)，则有助于：查询重写：只要物化视图的陈旧区域未被触及，它就可用于重写。刷新：将分区信息用于改善物化视图的刷新分区和查询重写当更新某一从表的分区时，物化视图只有特定部分会被标记为陈旧。物化视图必须具有这样的信息-能够识别与物化视图的特定行或组相对应的表的分区。最简单情况是，在物化视图的SELECT列表中提供了分区键(partitioningkey)，因为这是将行映射到陈旧分区最简单的方法。在使用部分陈旧的物化视图时，要点是：或TRUSTED模式使用物化视图。物化视图中的新行是通过将选择谓词添加到物化视图的WHERE子句来识别的。如果一个查询的回答是包含在这个(受限制的)物化视图内的，那么，可用这个物化视图将其重写。请注意，支持带有选择谓词的物化视图是这种重写类型的先决条件。分区和刷新EXCHANGEMERGEADDPARTITION。要在此类操作后维护物化视图，您可以使用一种称作分区变化跟踪(PCT)的刷s1.确保环境干净2.创建包含分区键的物化视图3.分析包含分区键的物化视图4.对costs表执行分区维护操作，并检查物化视图的状态5.添加数据并通过分区执行快速刷新6.执行另一个分区维护操作1.确保环境干净scleanupforpmopsqlSQLSQLPlus会话中：我们需要物化视图日志，用于下一个物化视图的快速刷新功能。INCLUDINGNEWVALUES;ORAORASQL2.创建包含分区键的物化视图要利用物化视图基于分区表的增强功能，最简单的方法是将分区键并入物化视图定义中。qlSQLBUILDIMMEDIATE图的状态为FRESH。ostsmvCOMPILE3.分析包含分区键的物化视图t您可以看到，其中启用了分区变化跟踪(PCT)，用于COSTS表和查询重写。4.对COSTS表执行分区维护操作，并检查物化视图的状态要将一些空分区添加到costs表，并察看其如何影响物化视图的陈旧程度，请执行下列步骤：SH模式的SQL*Plus会话中，运行add_part_to_cost.sql，或将以下SQL语句Y图的状态为FRESH。ostsmvCOMPILE5.添加数据并通过分区执行快速刷新要将一些数据添加到第二个事实表(costs)，请执行下面的步骤。请注意，您只会将数据插入到最新添加的分区中。表中的其他数据没有被更改。1.在登录到SH模式的SQL*Plus会话中，运行insert_costs.sql，或将以下SQL语句复制COMMIT;2.查询插入的数据。qqostsmvCOMPILE6.执行另一个分区维护操作您已经看到，物化视图通常是陈旧的。但是，只要陈旧数据未被触及，它就可以被用于重写。变化跟踪使您能够快速刷新costs_mv物化视图。1.为了控制刷新的过程，我们可以从物化视图查询所有单元销售的SUM。2.进行一次快速刷新。分区，刷新所需时间仅为其初始创建时间的一小部分。初始创建时间是进行一次完整更新所需的时4.显然，物化视图的状态又是新的了。ostsmvCOMPILE带有PMARKER信息的物化视图PCT图行关联回源分区从表中其相应的从表。这可以通过将从表分区键列包含在选择列表中(而且，如果使用了GROUPBY，也要包含在GROUPBY列表中)来实现，如上例中所示。取决于所需的聚合级和分区键列的不同基数，这会造成不利的影响-大幅度提高物化视图的基数。例如，假定一个常用指标是一种产品在给定的一年期间产生的收入。如果sales表是按time_id分区的，那么，它将是物化视图中的SELECT子句和GROUPBY子句中的必需字段。如果每天销售1000种不同产品，这会大大增加物化视图中的行数。在很多情况下，PCT的优势被这种对高聚合物化视图的限制抵消了。标识符唯一地标识特定分区表内的特定行的分区。DBMS_MVIEW.PMARKER函数用于替代制到SQL*Plus会话中。这将会创建一个带有PMARKER信息的物化视图：vBUILDIMMEDIATE2.与存储分区键不同，现在每个分区只有一个值，这极大地降低了基数。t/使用查询重写和部分陈旧的物化视图您需要在COSTS表上使用一些DML语句，这会导致物化视图变得陈旧。但是，只要不触及物化视图中的任何陈旧数据，查询重写仍可进行。为此，执行以下步骤：插入数据并显示状态。2.解释部分陈旧重写。3.刷新并显示实际数据。1.插入数据并显示状态1.在登录到SH模式的SQL*Plus会话中，运行insert_costs.sql，或将以下SQL语句复制COMMIT;2.关于最新更改的信息也显示在COSTS表的物化视图日志中。3.与预期一样，基于COSTS表的两个物化视图都呈现陈旧状态。ostsmvCOMPILE2.解释部