cube和物化视图

1、数据库驻留的按多维数据集组织的物化视图（无需任何特殊工具将 OLAP 多维数据集的强大功能同 SQL 的简单性集成在一起）、通过分区变化跟踪功能轻松识别刷新、新增的 Analytic Workspace Manager、扩展到子查询和远程表的查询重写以及许多其他新特性使 Oracle 数据库成为更强有力的数据仓储平台。物化视图 cube 立方体 AWM目录-· 按多维数据集组织的物化视图· 更频繁地执行查询重写· 分区中的陈旧程度检查· Analytic Workspace Manager按多维数据集组织的物化视图联机分析处

2、理 (OLAP) 概念自 20 世纪 70 年代以来一直很活跃，并在 20 世纪 90 年代中期开始成为主流，Ted Codd 在 1992 年创造了术语“OLAP”。由于有点深奥，大多数企业当时都不知道如何正确利用 OLAP。多年以后，该技术已十分完善，使得 OLAP 依靠大型数据仓库变得切实可行，从而真正将“智能”引入业务智能中。与传统关系设计截然不同，OLAP 允许以最有效的方式存储和访问数据，即最终用户可以遍历具有许多维度的假定“多维数据集”的边缘。（请参见下面的多维数据集数据示例）。多维数据集的维度与事实（也称为“量度”）相关联。用关系术语来讲，事实与维度之间具有多对一关系。例如，A

3、cme Computer Supplies 可能有一个销售数据库。维度通常包括客户、产品和时间元素（月份、季度等）。在特定的时间段（2008 年 8 月）内，特定产品（Cat5e 电缆）与特定客户 (Oracle Corp.) 之间对应的销售额是一个量度。维度和事实（例如销售额）都存储在单个表上。因此，用关系术语来讲，事实表是维度表的子表。但是，这仅仅是一个比喻而已。在关系设计中，将通过在事实表的 customer、product 或 time 列上创建的索引来访问量度。而在 OLAP 方法中，特定单元格（量度）是通过遍历多维数据集进行访问的：本示例中的访问方法如下：先转至包含时间 Aug 0

4、8 的部分，再转至产品 Cat5e，最后转至客户 Oracle。Oracle 像在数组（而不是表）中一样计算目的地，然后即可知道如何到达这些部分。例如，假定维度是按以下方式组织的：Dimension Time := 'May','Jun','Jul','Aug'Dimension Customer := 'Microsoft','IBM','Oracle','HP'Dimension Product := 'Fiber','Cat6e'

5、,'Cat5e','Serial'为了找到 Oracle + Aug + Cat5e 量度，OLAP 引擎将按如下方式执行导航：1. Aug 08 是 Time 数组的第四个元素，因此沿着多维数据集的时间维度转至第四个单元格。2. Cat5e 是 Product 数组的第三个元素，因此转至第三个元素。3. Oracle 是 Customer 数组的第三个元素，因此转至第三个元素。就是这样！现在您已经找到了所需的量度。由于维度值充当数组指针，因此在执行此操作时不必使用索引。同样，如果您要计算 2008 年 8 月份所有客户的销售总额，可以执行同样的操作，只是在第

6、3 步中将数组各元素的量度加起来，而不是转至特定单元格。以传统星型模式存储的纯关系形式数据的关系访问与上述方法不同，如下所示。在关系数据库方法中，您必须将此“事实”表与所有维度联接。每次需要数据时，都需要从事实表中选择合适的数据（可能要通过索引），然后将其与所有维度逐个联接起来（再次通过索引）。虽然此方法在技术上是可行的，但在大型数据库中完全行不通。作为替代方法，能否为所有这些选项创建物化视图 (MV) 呢？用户可以使用维度元素的任意组合：· 8 月份针对所有客户的 Cat5e 销售额· 8 月份针对 Oracle 的串行电缆销售额与 IBM 销售额（针对同一产品和月份）的

7、百分比· 针对 HP 的光缆销售额与针对 Microsoft 的串行电缆销售额的百分比等等。但是，需要创建多少个 MV 呢？理论上讲，应该为每个组合创建一个 MV (4 x 4 x 4 = 64 MV)。除了空间，您还需要足够的时间和数据库资源，以便在数据发生变化时刷新 MV，可能会涉及数千个元素。这样，要创建和管理的 MV 的数量 将变得相当庞大。相反，多维数据集是单个段，却可以同样轻松地处理任意类型的查询。虽然二者都可用于旨在加快汇总数据（与 OLTP 数据不同）处理速度的数据仓库设计中，却存在着巨大的根本区别：MV 存储预先计算的结果以避免联接和聚合，而多维数据集存储

8、原始数据并即时计算大多数汇总数据。（需要强调的是“大多数”这个词，因为某些汇总数据是创建的。多维数据集确定哪些聚合有用，然后仅创建那些聚合。在所有其他情况下，汇总数据都是即时计算的。）由于访问路径通过数组基数运算，因此多维数据集中的数据检索速度要比关系表（如 MV）快得多。OLAP 对象（如多维数据集）存储在数据库的特殊区域中，这些特殊区域称为“分析工作区”(AW)。数据库可能有一个或多个 AW。这些 AW 以 BLOB 形式存储在名称带有特别前缀 AW$ 的表上。虽然 Oracle 数据库中的多维数据集不是新特性，在 Oracle Database 11g 之前的多个版本中已经存在

9、，但它们在访问方式上稍有不同。（毕竟，Oracle 在本质上主要是关系数据库引擎。）在这些版本中，数据的多维数据集表示是非固有概念，尤其在涉及到 MV 时更是如此。MV 提供了一些非常有趣的特性：自动查询重写，用户的查询将被自动重写；增量刷新，仅刷新 MV 的某些部分，等等。另一方面，MV 是一种关系特性，而不是 OLAP 多维数据集的固有特性。现在，我们来想象一件两全其美的事情：将 MV 的重写特性与 OLAP 多维数据集的性能优势结合起来。这一点在 Oracle Database 11g 中得以实现。在这方面，也许最重要的新功能是将 OLAP 多维数据集表示成 MV（通过称为“按

10、多维数据集组织的物化视图”这一新特性）。新函数 CUBE_TABLE 可以在纯 SQL 中完成对多维数据集的搜索。由于 MV 实际上是 OLAP 多维数据集，因此查询重写特性将重写查询以使用多维数据集，这一切甚至在您不知道的情况下进行。这样，您就可以通过支持 SQL 的任意工具来利用多维数据集的性能：Oracle 业务智能企业版、Cognos、Business Objects、Oracle Apex、SQL*Plus 或自定义的 Java 程序，不胜枚举。实际上，您甚至不必了解任何特殊语法。（为了便于识别，按多维数据集组织的 MV 的名称带有 CB$ 前缀。）我将通过 Oracle 的正式示例

11、模式对这一新特性进行解释。下载并解压缩该 zip 文件：$ unzip global_11g_schema.zipArchive:  global_11g_schema.zip  inflating: global_11g_remove.sql     inflating: global_11g_source.dmp     inflating: Templates/CHANNEL.XML     inflating: Templates/CUSTOMER.XML  

12、0; inflating: Templates/GLOBAL.XML      inflating: Templates/GLOBAL_MV.XML    inflating: Templates/PRICE_CUBE.XML    inflating: Templates/PRODUCT.XML     inflating: Templates/TIME.XML        inflating: Templates/UNI

13、TS_CUBE.XML    inflating: global_11g_install.sql    inflating: global_11g_readme.html 通过从 SQL*Plus 或 SQL Developer 中运行脚本来安装全局模式。您应该知道 SYSTEM 用户的口令：SQL> global_11g_installEnter the password for the user GLOBAL: Enter the password for the user SYSTEM: Connected. User create

14、d. Grant succeeded. Connected. Import: Release .0 - Production on Sat Jun 28 17:08:22 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Minin

15、g,Oracle Database Vault and Real Application Testing options Export file created by EXPORT:V11.01.00 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses WE8MSWIN1252 character set (possible charset conversion)export client uses WE8MSWIN1252

16、character set (possible charset conversion). importing GLOBAL's objects into GLOBAL. . importing table "ACCOUNT" 24 rows imported. . importing table "CHANNEL_DIM" 3 rows imported. . importing table "CUSTOMER_DIM" 61 rows imported. . importing table "PRICE_FACT&

17、quot; 2523 rows imported. . importing table "PRODUCT_CHILD_PARENT" 48 rows imported. . importing table "PRODUCT_DIM" 36 rows imported. . importing table "TIME_DIM" 120 rows imported. . importing table "UNITS_FACT" 299446 rows importedAbout to enable constraint

18、s.Import terminated successfully without warnings. SQL> exitglobal_11g_readme.html 文件中包含有关该模式的详细信息。它还包含一个脚本，用于在测试完成后删除模式。该文件从转储文件中导入维度、事实以及所有其他相关数据。通过示例解释“按多维数据集组织的 MV”这一概念可能是最容易的。转储文件包含两个多维数据集，其中一个是 PRICE_CUBE。我们看一下如何使用纯 SQL 查询该多维数据集。我还希望显示 Oracle 要遵循的访问路径，因此我在运行查询之前使用了 autotrace 命令。SQ

19、L> set autotrace on explainSQL> select * from table(cube_table('GLOBAL.PRICE_CUBE')  2> /.                               &#

20、160;the data comes here .Execution Plan-Plan hash value: 3184667476-| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |-| 

21、60; 0 | SELECT STATEMENT        |            |  2000 |   195K|    29   (0)| 00:00:01 |   1 |  CUBE SCAN PARTIAL OUTER| PRICE_CUBE |  2000 |  

22、; 195K|    29   (0)| 00:00:01 |-                           CUBE_TABLE 是 Oracle Database 11g 中引入的新函数。它允许将 AW 中存储的多维数据集作为关系对象进行查询。注意，访问路径

23、显示了 CUBE SCAN，表明将该多维数据集作为多维数据集进行了扫描（虽然您使用了 SQL）。在本示例中，我们使用了最基本的工具；SQL*Plus 完全不了解 OLAP 或分析转换，但我们可以采用某种分析方式来提取数据。可以在表和维度上使用 CUBE_TABLE 函数。一般形式如下：select * from table(cube_table('<schema>.<dimension><hierarchy>')hierarchy 部分是可选的；可以忽略这部分。下面是您之前安装的 GLOBAL 样例模式中的一个示例，在这个示例中，您要从 PR

24、ODUCT 维度中选择层次结构 PRIMARY。select * from table(cube_table('GLOBAL.PRODUCT;PRIMARY')将为每个多维数据集和维度自动生成一个视图。例如，将为多维数据集 PRICE_CUBE 生成一个名为 PRICE_CUBE_VIEW 的视图。如果检查视图定义，您会发现其内容如下：CREATE OR REPLACE VIEW "PRICE_CUBE_VIEW" ("UNIT_PRICE", "UNIT_COST", "TIME", "

25、PRODUCT") AS SELECT "UNIT_PRICE", "UNIT_COST", "TIME", "PRODUCT" FROM TABLE(CUBE_TABLE('GLOBAL.PRICE_CUBE') )当 GUI 工具不了解 CUBE 函数并且系统提示用户选择对象类型（通常是视图）时，视图非常有用。以下是使用 Oracle SQL Developer 从视图中选择数据的一个示例。SQL Developer 显示视图，您可以像在任何其他视图中一样选择数据选项卡和筛选数据。虽然

26、是多维数据类型，但 CUBE_TABLE 表函数允许将其作为关系对象进行访问。在后台，数据库的多维引擎执行必要的计算，例如，以透明方式聚合或计算量度。这样，您就获得了两全其美的结果。您可以在熟悉的应用程序环境中（即使是 SQL*Plus）利用 Oracle 数据库中内置的 OLAP 功能。虽然您可以在纯 SQL 中管理多维数据集，但最简单的方法是通过 Analytic Workspace Manager 工具（稍后介绍）。视图 DBA_CUBES 显示了有关多维数据集的信息。现在，我们来看一下使用按多维数据集组织的 MV 有哪些好处。首先，由于对多维数据集进行分析访问，多维数据集（和按多维数据

27、集组织的 MV）可以显著提高需要汇总数据的查询的性能。其次，多维数据集在执行快速增量刷新方面非常高效。最后，由于单个多维数据集可以代替许多 MV，因而更容易管理和维护。注意，按多维数据集组织的 MV 仅仅是个元数据对象。汇总数据由多维数据集管理，按多维数据集组织的 MV 仅支持多维数据集的自动查询重写和自动刷新。数据存储在多维数据集中，而不存储在按多维数据集组织的 MV 中，因此这不是复制策略。可将其视为与预先建立的表（类似于多维数据集）上的 MV 类似。更频繁地执行查询重写您应该已经熟悉了查询重写功能，在几个版本之前的 Oracle 数据库中曾经介绍过。总而言之，当用户写入一个与 MV 的定

28、义查询相匹配的查询时，Oracle 将从 MV 中进行选择，而不在数据库中执行查询。术语“匹配”指存储的 MV 可以满足查询的部分结果集，或者可以使用 MV 中的现有数据加快查询速度。换句话说，Oracle 重写查询（或查询的某些部分）以便从 MV（而不是查询中指定的表）中进行选择。这样，数据库就不必执行访问基表和计算任务，可以更快地为用户返回数据。所有这些操作都将自动进行，用户甚至不知道存在这样一个 MV 并且查询已被重写。当然，用户必须选择接受查询中的替换内容。会话参数 query_rewrite_enabled 必须设置为 TRUE，并应基于 MV 的陈旧程度将 query_rewrit

29、e_integrity 设置为 trusted 或 stale_tolerated（该参数控制由内核提供的数据完整性实施的级别）。MV 自身也必须可用于查询重写。当用户查询与 MV 的定义查询类似时将会发生查询重写。在过去的版本中，如果查询不想死，将不会重写查询。但在 Oracle Database 11g中，这些规则将更加宽松。请看如下所示的 MV：create materialized view mv4        enable query rewrite asselect   prod_

30、id, cust_id, avg (rate) tot_qty    from (select d_id, cust_id, amount_sold / quantity_sold rate            from sales s, products p           where d_id = d_id) sqgr

31、oup by prod_id, cust_id/它使用内联查询，行源实际上是另一个查询（FROM 中的子句实际上是内联查询）。如果您写入一个与 MV 的定义查询类似的查询（使用同一个内联视图），现在您将看到正在发生重写。使用 AUTOTRACE 检查执行路径。SQL> alter session set query_rewrite_enabled = true; Session altered. SQL> alter session set query_rewrite_integrity = stale_tolerated; Session altered.SQ

32、L> set autotrace traceonly explainSQL> select pid, cid, avg(item_rate) avg_item_rate 2 from (select d_id pid, cust_id cid, amount_sold/quantity_sold item_rate 3 from sales s, products p 4 where d_id = d_id) 5 group by cid, pid;Execution Plan-Plan hash value: 3944983699 -| Id | O

33、peration | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 287K| 10M| 226 (2)| 00:00:03 | 1 | MAT_VIEW REWRITE ACCESS FULL| MV4 | 287K| 10M| 226 (2)| 00:00:03 |- Note- - dynamic sampling used for this statement注意上面 Id=1 的情况；查询使用我们之前创建的物化视图 MV4 进行了重写。即使 MV 和查询使用了内联视图（或子查询），也会发生查询

34、重写。在 Oracle Database 11g 中，远程表也会发生查询重写。分区中的陈旧程度检查如果您有一个分区的从表，并基于该表创建了 MV，则可以利用分区变化跟踪（PCT；在 Oracle Database 10g 中引入）功能仅刷新特定分区，而不是整个表。PCT 特性允许您使用 FAST 选项执行更新，即使从表上没有 MV 日志。但是，如何知道哪些分区已经更改了？Oracle Database 11g 中有一个很棒的新视图：DBA_MVIEW_DETAIL_PARTITION。此视图显示已更新的分区，并将用于后面的 PCT 刷新。FRESHNESS 列显示

35、该分区在 MV 中是否已被刷新。与任何其他视图一样，此视图也有 USER_* 和 ALL_* 版本。我们使用 Oracle Database 11g 随附光盘中提供的 SH 模式。首先检查刷新情况：SQL> select detailobj_name, detail_partition_name, detail_partition_position, freshness  2> from user_mview_detail_partition  3> where MVIEW_NAME = 'MV1'DETAILOBJ_NAME&#

36、160;                DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH- - - -SALES              &#

37、160;           SALES_1995                                     

38、60;       1 FRESHSALES                          SALES_1996              

39、60;                              2 FRESHSALES                  &#

40、160;       SALES_H1_1997                                         

41、 3 FRESHSALES                          SALES_H2_1997                    

42、60;                     4 FRESHSALES                          SALES_Q1_1998

43、                                          5 FRESHSALES       

44、;                   SALES_Q2_1998                             

45、60;            6 FRESHSALES                          SALES_Q3_1998         

46、                                 7 FRESHSALES                

47、;          SALES_Q4_1998                                      

48、60;   8 FRESHSALES                          SALES_Q1_1999                  

49、                        9 FRESHSALES                         

50、; SALES_Q2_1999                                         10 FRESHSALES     &

51、#160;                    SALES_Q3_1999                            

52、;             11 FRESHSALES                          SALES_Q4_1999        &

53、#160;                                12 FRESHSALES                

54、;          SALES_Q1_2000                                      

55、60;  13 FRESHSALES                          SALES_Q2_2000                   

56、;                      14 FRESHSALES                          SALES_Q3

57、_2000                                         15 FRESHSALES       

58、;                   SALES_Q4_2000                             

59、60;           16 FRESHSALES                          SALES_Q1_2001          

60、;                               17 FRESHSALES                 

61、60;        SALES_Q2_2001                                        

62、18 FRESHSALES                          SALES_Q3_2001                    

63、60;                    19 FRESHSALES                          SALES_Q4_2001 

64、;                                        20 FRESHSALES        

65、60;                 SALES_Q1_2002                               &

66、#160;         21 FRESHSALES                          SALES_Q2_2002           

67、60;                             22 FRESHSALES                   &

68、#160;      SALES_Q3_2002                                         23 FRESHSA

69、LES                          SALES_Q4_2002                      &

70、#160;                  24 FRESHSALES                          SALES_Q1_2003  

71、60;                                      25 FRESHSALES          &

72、#160;               SALES_Q2_2003                                 

73、;        26 FRESHSALES                          SALES_Q3_2003             &

74、#160;                           27 FRESHSALES                     

75、;     SALES_Q4_2003                                         28 FRESH现在，在表中插入一行：i

76、nsert into sales values (13,987,'1-jan-98',3,999,1,1232.16)现在，如果您执行同一查询以检查刷新：DETAILOBJ_NAME DETAIL_PARTITION_NAME DETAIL_PARTITION_POSITION FRESH- - - -SALES SALES_1995 1 FRESHSALES SALES_1996 2 FRESHSALES SALES_H1_1997 3 FRESHSALES SALES_H2_1997 4 FRESHSALES SALES_Q1_1998 5 STALESALES SALES

77、_Q2_1998 6 FRESH. and so on .该行所指的分区 SALES_Q1_1998 现在列为 STALE。当您快速刷新 MV 时，它将使用 PCT 功能仅刷新该分区，而不刷新整个表。而且，即使表上没有 MV 日志，也会发生 FAST 刷新。现在，如果您使用 FAST 选项刷新 MV：SQL> exec dbms_mview.refresh('MV1','F')PL/SQL procedure successfully completed.现在再次检查刷新情况：DETAILOBJ_NAME DETAIL_PARTITION_NAME DET

78、AIL_PARTITION_POSITION FRESH- - - -SALES SALES_1995 1 FRESHSALES SALES_1996 2 FRESHSALES SALES_H1_1997 3 FRESHSALES SALES_H2_1997 4 FRESHSALES SALES_Q1_1998 5 FRESHSALES SALES_Q2_1998 6 FRESH该分区被标记为 FRESH；后续的快速刷新将不会选择该分区。通过此视图，您可以轻松地确定在 PCT 刷新中将刷新哪些分区，并可估计相关工作。此特性也适用于按多维数据集组织的 MV。Analytic Workspace Manager正如我先前提到的，存储 OLAP 对象的特殊区域称为“分析工作区”。OLAP 对象