Oracle数据库11g新特性

1、Oracle数据库11g新特性：数据仓库和OLAPOracle数据库11g面向DBA和开发人员的重要新特性：数据仓库和OLAP    数据库驻留的按多维数据集组织的物化视图（无需任何特殊工具将 OLAP 多维数据集的强大功能同 SQL 的简单性集成在一起）、通过分区变化跟踪功能轻松识别刷新、新增的分析工作区管理器、扩展到子查询和远程表的查询重写以及许多其他新特性使 Oracle 数据库成为更强有力的数据仓库平台。    按多维数据集组织的物化视图    联机分析处理 （OLAP） 概念自 20 世纪 70

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

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

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

5、 Product := 'Fiber','Cat6e','Cat5e','Serial'    为了找到 Oracle + Aug + Cat5e 量度，OLAP 引擎将按如下方式执行导航：    1、Aug 08 是 Time 数组的第四个元素，因此沿着多维数据集的时间维度转至第四个单元格。    2、Cat5e 是 Product 数组的第三个元素，因此转至第三个元素。    3、Oracle 是 Custo

6、mer 数组的第三个元素，因此转至第三个元素。    就是这样！现在您已经找到了所需的量度。由于维度值充当数组指针，因此在执行此操作时不必使用索引。同样，如果您要计算 2008 年 8 月份所有客户的销售总额，可以执行同样的操作，只是在第 3 步中将数组各元素的量度加起来，而不是转至特定单元格。    以传统星型模式存储的纯关系形式数据的关系访问与上述方法不同，如下所示。在关系数据库方法中，您必须将此“事实”表与所有维度联接。每次需要数据时，都需要从事实表中选择合适的数据（可能要通过索引），然后将其与所有维度逐个联接起来（再次通过索引

7、）。虽然此方法在技术上是可行的，但在大型数据库中完全行不通。    作为替代方法，能否为所有这些选项创建物化视图 （MV） 呢？用户可以使用维度元素的任意组合：      8 月份针对所有客户的 Cat5e 销售额      8 月份针对 Oracle 的串行电缆销售额与 IBM 销售额（针对同一产品和月份）的百分比      针对 HP 的光缆销售额与针对 Microsoft 的串行电缆销售额的百分比    等等。

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

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

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

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

12、织的 MV 的名称带有 CB$ 前缀。）    我将通过 Oracle 的正式示例模式对这一新特性进行解释。下载并解压缩该 zip 文件：$ unzip global_11g_schema.zipArchive:  global_11g_schema.zip  inflating: global_11g_remove.sql     inflating: global_11g_source.dmp     inflating: Templates/CHANNEL.XML &#

13、160;   inflating: Templates/CUSTOMER.XML    inflating: Templates/GLOBAL.XML      inflating: Templates/GLOBAL_MV.XML    inflating: Templates/PRICE_CUBE.XML    inflating: Templates/PRODUCT.XML     inflating: Templates/TIME.XML 

14、       inflating: Templates/UNITS_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 G

15、LOBAL: Enter the password for the user SYSTEM: Connected. User created. 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 - Pro

16、ductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,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 charac

17、ter set (possible charset conversion)export client uses WE8MSWIN1252 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 "CUS

18、TOMER_DIM" 61 rows imported. . importing table "PRICE_FACT" 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

19、"UNITS_FACT" 299446 rows importedAbout to enable constraints.Import terminated successfully without warnings. SQL> exit    global_11g_readme.html 文件中包含有关该模式的详细信息。它还包含一个脚本，用于在测试完成后删除模式。该文件从转储文件中导入维度、事实以及所有其他相关数据。通过示例解释“按多维数据集组织的 MV”这一概念可能是最容易的。转储文件包含两个多维数据集，其中一个是 PRICE_CUB

20、E.我们看一下如何使用纯 SQL 查询该多维数据集。我还希望显示 Oracle 要遵循的访问路径，因此我在运行查询之前使用了 autotrace 命令。SQL> set autotrace on explainSQL> select * from table(cube_table('GLOBAL.PRICE_CUBE') 2> / . the data comes here .Execution Plan-Plan hash value: 3184667476 -| Id | Operation | Name | Rows | Bytes | Cost (%C

21、PU)| Time |-| 0 | SELECT STATEMENT | | 2000 | 195K| 29 (0)| 00:00:01 | 1 | CUBE SCAN PARTIAL OUTER| PRICE_CUBE | 2000 | 195K| 29 (0)| 00:00:01 |-    CUBE_TABLE 是 Oracle 数据库 11g 中引入的新函数。它允许将 AW 中存储的多维数据集作为关系对象进行查询。注意，访问路径显示了 CUBE SCAN，表明将该多维数据集作为多维数据集进行了扫描（虽然您使用了 SQL）。在本示例中，我们使用了最基本的工具

22、；SQL*Plus 完全不了解 OLAP 或分析转换，但我们可以采用某种分析方式来提取数据。    可以在表和维度上使用 CUBE_TABLE 函数。一般形式如下：select * from table(cube_table('<schema>.<dimension><hierarchy>')    hierarchy 部分是可选的；可以忽略这部分。下面是您之前安装的 GLOBAL 样例模式中的一个示例，在这个示例中，您要从 PRODUCT 维度中选择层次结构 PRIMARY.sel

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

24、quot;) AS   SELECT     "UNIT_PRICE",     "UNIT_COST",     "TIME",     "PRODUCT"  FROM TABLE(CUBE_TABLE('GLOBAL.PRICE_CUBE') )    当 GUI 工具不了解 CUBE 函数并且系统提示用户选择对象类型（通常是视

25、图）时，视图非常有用。以下是使用 Oracle SQL Developer 从视图中选择数据的一个示例。SQL Developer 显示视图，您可以像在任何其他视图中一样选择数据选项卡和筛选数据。虽然是多维数据类型，但 CUBE_TABLE 表函数允许将其作为关系对象进行访问。在后台，数据库的多维引擎执行必要的计算，例如，以透明方式聚合或计算量度。这样，您就获得了两全其美的结果。您可以在熟悉的应用程序环境中（即使是 SQL*Plus）利用 Oracle 数据库中内置的 OLAP 功能。    虽然您可以在纯 SQL 中管理多维数据集，但最简单的方法是通过分析工作区

26、管理器工具（稍后介绍）。视图 DBA_CUBES 显示了有关多维数据集的信息。    现在，我们来看一下使用按多维数据集组织的 MV 有哪些好处。首先，由于对多维数据集进行分析访问，多维数据集（和按多维数据集组织的 MV）可以显著提高需要汇总数据的查询的性能。其次，多维数据集在执行快速增量刷新方面非常高效。最后，由于单个多维数据集可以代替许多 MV，因而更容易管理和维护。    注意，按多维数据集组织的 MV 仅仅是个元数据对象。汇总数据由多维数据集管理，按多维数据集组织的 MV 仅支持多维数据集的自动查询重写和自动刷新。数据存储在多

27、维数据集中，而不存储在按多维数据集组织的 MV 中，因此这不是复制策略。可将其视为与预先建立的表（类似于多维数据集）上的 MV 类似。    您应该已经熟悉了查询重写功能，在几个版本之前的 Oracle 数据库中曾经介绍过。总而言之，当用户写入一个与 MV 的定义查询相匹配的查询时，Oracle 将从 MV 中进行选择，而不在数据库中执行查询。术语“匹配”指存储的 MV 可以满足查询的部分结果集，或者可以使用 MV 中的现有数据加快查询速度。换句话说，Oracle 重写查询（或查询的某些部分）以便从 MV（而不是查询中指定的表）中进行选择。这样，数据库就不必执行访

28、问基表和计算任务，可以更快地为用户返回数据。所有这些操作都将自动进行，用户甚至不知道存在这样一个 MV 并且查询已被重写。    当然，用户必须选择接受查询中的替换内容。会话参数 query_rewrite_enabled 必须设置为 TRUE，并应基于 MV 的陈旧程度将 query_rewrite_integrity 设置为 trusted 或 stale_tolerated（该参数控制由内核提供的数据完整性实施的级别）。MV 自身也必须可用于查询重写。    当用户查询与 MV 的定义查询类似时将会发生查询重写。在过去的版本中，

29、如果查询不想死，将不会重写查询。但在 Oracle 数据库 11g 中，这些规则将更加宽松。请看如下所示的 MV：create materialized view mv4        enable query rewrite asselect   prod_id, cust_id, avg (rate) tot_qty    from (select d_id, cust_id, amount_sold / quantity_sold rate 

30、0;          from sales s, products p           where d_id = d_id) sqgroup by prod_id, cust_id/    它使用内联查询，行源实际上是另一个查询（FROM 中的子句实际上是内联查询）。如果您写入一个与 MV 的定义查询类似的查询（使用同一个内联视图），现在您将看到正在发

31、生重写。使用 AUTOTRACE 检查执行路径。SQL> alter session set query_rewrite_enabled = true; Session altered. SQL> alter session set query_rewrite_integrity = stale_tolerated; Session altered.SQL> set autotrace traceonly explainSQL> select pid, cid, avg(item_rate) avg_item_rate 2 from (select d_id

32、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 | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 287K| 10M| 226 (2)| 00:00:03 | 1 | M

33、AT_VIEW REWRITE ACCESS FULL| MV4 | 287K| 10M| 226 (2)| 00:00:03 |- Note- - dynamic sampling used for this statement    注意上面 Id=1 的情况；查询使用我们之前创建的物化视图 MV4 进行了重写。即使 MV 和查询使用了内联视图（或子查询），也会发生查询重写。    在 Oracle 数据库 11g 中，远程表也会发生查询重写。   分区中的陈旧程度检查    如果

34、您有一个分区的从表，并基于该表创建了 MV，则可以利用分区变化跟踪（PCT；在 Oracle 数据库 10g 中引入）功能仅刷新特定分区，而不是整个表。PCT 特性允许您使用 FAST 选项执行更新，即使从表上没有 MV 日志。但是，如何知道哪些分区已经更改了？    Oracle 数据库 11g 中有一个很棒的新视图：DBA_MVIEW_DETAIL_PARTITION.此视图显示已更新的分区，并将用于后面的 PCT 刷新。FRESHNESS 列显示该分区在 MV 中是否已被刷新。与任何其他视图一样，此视图也有 USER_* 和 ALL_* 版本。 &

35、#160;  我们使用 Oracle 数据库 11g 随附光盘中提供的 SH 模式。首先检查刷新情况：别存查看全图    现在，在表中插入一行：insert into sales values （13，987，'1-jan-98'，3，999，1，1232.16）    现在，如果您执行同一查询以检查刷新：别存查看全图    该行所指的分区 SALES_Q1_1998 现在列为 STALE.当您快速刷新 MV 时，它将使用 PCT 功能仅刷新该分区，而不刷新整个表。而且，即使表上

36、没有 MV 日志，也会发生 FAST 刷新。    现在，如果您使用 FAST 选项刷新 MV：SQL> exec dbms_mview.refresh('MV1','F')PL/SQL procedure successfully completed.    现在再次检查刷新情况：别存查看全图    该分区被标记为 FRESH；后续的快速刷新将不会选择该分区。通过此视图，您可以轻松地确定在 PCT 刷新中将刷新哪些分区，并可估计相关工作。此特性也适用于按多维数据集组

37、织的 MV   分析工作区管理器    正如我先前提到的，存储 OLAP 对象的特殊区域称为“分析工作区”。OLAP 对象是通过一个被称作“分析工作区管理器”（AWM） 的特殊工具进行操作的，可以在 OTN 的 Oracle OLAP 选件主页中下载此工具。    有两种形式的 AWM：独立版本和 DBClient.独立版本是可从 Java 命令行运行的完整 JAR 文件。DBClient 包含几个 DLL，应将其复制到 Oracle 主目录中的相应目录。在本部分中，我们将研究此工具的独立版本 （A

38、）。    将文件解压缩之后，请转至解压缩 AWM 所在的目录并执行以下命令来启动 AWM：    java -mx512m -jar awm.0A.jar    GUI 打开后，单击 File -> Connect Database，然后选择要连接的数据库。选择 GLOBAL 作为用户名。完成数据库连接后，单击 Schemas 前面的 + 号展开所有元素。结果屏幕将如下所示。    我们来尝试执行一个常见操作：刷新多维数据集。右键单击左侧面板中的 PR

39、ICE_CUBE。将出现如下所示的弹出菜单：     从菜单中选择 Maintain Cube PRICE_CUBE。将出现如下所示的小窗口：     单击 Next 几次，最后将出现类似如下所示的屏幕：     此时您可以决定立即执行任务还是安排以后执行。单击 Finish 完成任务。最后将出现如下所示的确认屏幕：     注意名为 OUTPUT 的列，该列显示了 SQL 语句。双击该列将显示完整输出。PRICE_CUBE 的输出内容如下所示：<CommandOutp

40、ut><CubeLoadName="MAP1"SQL="SELECT /*+  bypass_recursive_check  cursor_sharing_exact  no_expand */   T16_MONTH_ID ALIAS_114,   T13_ITEM_ID ALIAS_115,   SUM(T19_UNIT_PRICE)  ALIAS_116,   SUM(T19_UNIT_COST)  ALIAS_117 FROM   ( 

41、; SELECT     T1.ITEM_ID T19_ITEM_ID,     T1.MONTH_ID T19_MONTH_ID,     T1.UNIT_PRICE T19_UNIT_PRICE,     T1.UNIT_COST T19_UNIT_COST   FROM     GLOBAL.PRICE_FACT T1   )   T19,   (  SELECT  &#

42、160;  T1.MONTH_ID T16_MONTH_ID   FROM     GLOBAL.TIME_DIM T1   )   T16,   (  SELECT     T1.ITEM_ID T13_ITEM_ID   FROM     GLOBAL.PRODUCT_DIM T1   )   T13 WHERE   (T16_MONTH_ID = T19_MONTH_ID)

43、60;   AND (T13_ITEM_ID = T19_ITEM_ID)     AND (T16_MONTH_ID = T19_MONTH_ID)     AND (T13_ITEM_ID = T19_ITEM_ID) )  GROUP BY   (T13_ITEM_ID, T16_MONTH_ID)  ORDER BY   T13_ITEM_ID ASC NULLS LAST ,   T16_MONTH_ID ASC NULLS LAST "LOAD

44、ED="2523"REJECTED="0"</CommandOutput>    另一个重要任务是从多维数据集中创建 MV。我们看一下如何对 UNITS_CUBE 多维数据集执行此操作。单击 UNITS_CUBE，将在右侧面板中显示该多维数据集的详细信息。您会在详细信息面板上看到几个选项卡。单击 Materialized Views 选项卡，如下所示：     此面板显示了将多维数据集存储为 MV 之前需要执行的各种检查。例如，第一行显示“User must have create

45、Materialized View Privilege”（用户必须具有创建物化视图的权限）。这句话的含义显而易见。用户必须具有创建 MV 的权限才能创建 MV。此检查已执行，并发现用户确实具有该权限。状态显示为绿色对勾，表明满足条件。     注意面板左上角的以下信息：    选中该框将启用多维数据集的 MV 样式刷新。然后，选中位于底部的 Enable Query Rewrite 复选框。接下来，按 Apply 按钮。该多维数据集现在已成为按多维数据集组织的 MV。    既然此多维数据集是常规 MV，

46、就可以采用以下方法进行刷新。begin   dbms_mview.refresh (       list => 'CB$PRICE_CUBE',       method => 'c'   );end;    还可以使用多维数据集重建方法：begin   dbms_cube.build('PRICE_CUBE');end;Af

47、ter populating (or rebuilding), you should collect stats:begin   dbms_aw_stats.analyze (       'PRICE_CUBE'   )end;    正如我先前提到的，AW 作为 BLOB 存储在数据库中。在本示例中：    SQL> desc aw$global    Name Null? Typ

48、e    - - -    PS# NUMBER(10)     GEN# NUMBER(10)    EXTNUM NUMBER(8)     AWLOB BLOB     OBJNAME VARCHAR2(256)    PARTNAME VARCHAR2(256)    BLOB 列包含多维数据集上的数据。因此，就像任何其他表一样，多维数据集存储在位于某个表空间的某个关系表中。就像任何其他表空间一样，此空间是在表空间中进行管理的，因此 DBA 在完成最初的 AW 创建任务后不必执行任何特殊操作。按多维数据集组织的 MV 与任何其他 MV 一样，因此大多数 DBA 都对刷新过程很熟悉   在别处介绍的其他新特性    Oracle 数据库 11g 中还有其他几个新特性，将有助于对数据仓库进行高效的设计和操作。在本系列的其他部分中