Oracle10g中的主要数据仓库功能：性能与比较分析

Oracle10g中的主要数据仓库功能：性能比较分析Oracle白皮书2005年4月Oracle10g中的主要数据仓库功能：性能比较分析2Oracle10g中的主要数据仓库功能：性能比较分析执行概要.........................................................................................3引言.....................................................................................................3系统和模式模型配置.............................................................4模式模型属性...............................................................................4数据属性.........................................................................................5硬件配置.........................................................................................5数据仓库负载..............................................................................5初始加载.........................................................................................5增量加载.........................................................................................6添加新数据.................................................................................7删除老数据.................................................................................9增量加载结果对比....................................................................10查询性能.......................................................................................10星型查询：示例#1..................................................................11星型查询：示例#2..................................................................11星型查询性能对比....................................................................12额外的简单查询........................................................................13结论...................................................................................................14附录A：测试模式模型.....................................................15附录B：查询执行计划.....................................................18星型查询示例#1：Oracle10g（星型转换）..................18星型查询示例#1：普通数据库（动态位图索引）.......18星型查询示例#1：普通数据库（散列联接）................19星型查询示例#2：Oracle10g（星型转换）..................19星型查询示例#2：普通数据库（动态位图索引）.......20星型查询示例#2：普通数据库（散列联接）................20附录C：INIT.ORA参数..................................................21附录D：星型查询中的表选择性...............................22Oracle10g中的主要数据仓库功能：性能比较分析3Oracle10g中的主要数据仓库功能：性能比较分析执行概要和同类数据库相比，Oracle10g的数据仓库特性提供了显著的性能优势。本白皮书将着重介绍Oracle10g的两个主要功能—范围分区和位图索引，以及这两个功能如何为数据仓库中典型的加载和查询操作提供独一无二的数量级性能优势。引言将多个供应商的数据库技术进行比较充满挑战。各个数据库供应商都能列举出其自身产品的多个特性和功能，并声称就是这些特性使他们的产品更胜于其他供应商。本白皮书反其道而行之，没有描述Oracle10g的数据仓库特性，而是对Oracle10g的两个主要功能（即范围分区和位图索引）的性能优势进行了阐述。其中还对两个数据库的基本数据仓库加载和查询操作的性能进行了对比。一个数据库利用了Oracle10g的范围分区和位图索引功能，而另一个数据库使用的是较为普通的关系数据库功能（散列分区和b树索引）。在其它方面，这两个数据库没有任何区别。本白皮书的目的是对Oracle的主要数据仓库功能相较于其它技术的性能优势进行量化说明。负载包括基本的数据仓库操作：创建、维护以及查询数据仓库。在这些步骤中，位图索引和范围分区的优势是比较明显的。虽然本文的负载比现实复杂的数据仓库要简单得多，但是这些功能的核心优势完全可以扩展到更加复杂的环境中。现在，大多数Oracle数据仓库客户在实际应用中同时使用范围分区和位图索引。该白皮书鼓励潜在客户试用这些主要特性以了解其真正价值所在。为了支持这种试用活动，本文中使用的模式模型和所有SQL操作均有文档提供。Oracle10g中的主要数据仓库功能：性能比较分析4系统和模式模型配置该测试的开展要用到两个数据库。除在分区和索引策略上不同以外，这两个数据库在其它方面都是相同的。尤其是这两个数据库：使用了相同的软件（Oracle10g第2版）具有相同的调整参数（参见附录C）使用同一模式模型（分区和索引方法除外），模式模型属性下文有述使用相同的硬件（硬件配置下文有述）使用相同的存储方式1使用相同的数据（数据属性下文有述）。本白皮书后文中用Oracle10g表示使用专门的Oracle10g功能的数据库，普通数据库表示另一个数据库。模式模型属性该性能试验中使用的模式模型是一个包含销售数据的星型模式模型。该模式模型包含一个事实表、SALES表和五个维表：PRODUCTS、CUSTOMERS、CHANNELS、PROMOTIONS和TIMES。附录A中提供了完整的模式模型。SALES表约有3亿行，包含2002年到2004年3年的销售数据。每个数据库都有5个有关该SALES表的索引：一个索引对应一个外键列。SALES表的分区和索引如下表所示：SALES表属性Oracle10g普通数据库分区方法根据TIME_ID进行范围分区根据CUST_ID进行散列分区分区数36（每月一个）32索引类型局部位图索引局部B*Tree索引表1：分区和索引策略比较1在这些测试中使用的是内部磁盘。对数据仓库而言，这不是最佳的解决方案。对于进行充分大小调整的附加存储配置，执行某些操作效果可能更好。Oracle10g中的主要数据仓库功能：性能比较分析5数据属性数据生成器用于为初始和增量数据加载创建数据。为了使数据尽可能有价值，生成的数据必须有以下特征：完善SALES表及其维表之间的引用完整性。销售量数据每年以20%的速度递增。每年各个月份的数据存在差异，其中11、12和1月的销售量高于平均水平，而4、6和8月的销售量低于平均水平。数据和该模式模型描述的业务模型一致。例如，SALES表中某个特定客户的购买量不能超过CUSTOMERS表中该客户的信用额。硬件配置这些性能测试中使用的硬件是HPProliantDL380G3，一个6GB的RAM、两个3GHz的CPU，运行的软件为RedhatEnterpriseLinux3.0。数据仓库负载为该测试选择的工作负载包括3个阶段。第一阶段是初步加载三年的数据，包括建立索引。第二阶段是最近月份数据的增量加载，包括维护索引、清除最早月份的数据。第三阶段（也是最后阶段）主要是执行典型查询。初始加载您可以使用Oracle10g的外部表功能将三年（2002年1月到2004年11月）的数据加载到数据仓库中。因为将行从文件加载到数据库很简单，所以Oracle10g数据库和普通数据库在性能上就没有明显的区别。最耗时的还是加载SALES表，将该表加载到两个数据库中大约需要27分钟。初始加载的下一步是创建所有必要索引。为事实表的所有外键列建立索引，这在许多星型模式模型数据仓库中是很普遍的。为此，Oracle10g数据库将使用位图索引，而普通库使用b树索引。和b树索引相比，位图索引创建时间短，空间占用少。如下表所示，创建5个b树索引花费的时间更长，占用的空间也大约是位图索引的9倍：22某些操作会受困于非最优化的存储配置，创建b树索引就是其中一个。请注意：差异是如此之大，即使使用最佳的存储，创建b树索引仍需花费很长时间。Oracle10g中的主要数据仓库功能：性能比较分析6列名花费时间(hh:mi:ss)空间(MB)位图索引B树位图索引B树CUST_ID0:06:092:45:121,0545,177PROD_ID0:05:432:43:371,2024,796TIME_ID0:04:002:38:003485,862CHANNEL_ID0:03:432:36:452304,009PROMO_ID0:03:362:34:481494,621总计0:23:1113:18:222,98324,465表2：位图索引和b树索引的创建时间与空间占用对比下图显示了这种差异：无论从哪个方面来说，Oracle10g的位图索引都是真正的位图索引。在其它具有“动态位图索引”功能的数据库中，索引如同b树索引一样存储在磁盘上。带动态位图索引的数据库无法像Oracle10g那样通过真正位图索引来节省任何空间或索引创建时间。增量加载在初步创建之后，可通过加载新数据、清除老数据来更新数据仓库。在本例中，需要加载最近一个月的数据，清除最早的一个月的数据。这一般称作“滚动窗口”操作，因为维护工作的持续进行，所以数据仓库中一直保存着最近3年的在线数据。在月度加载周期中，那个3年窗口每个月都“向前滚动”。Oracle10g中实现有效增量加载的主要功能是范围分区。因为该Oracle10g中的主要数据仓库功能：性能比较分析7数据仓库是按月添加新数据，所以SALES表已按月份进行了分区。因此，添加月度新数据时需要添加一个新分区，而清除以前某个月份的数据时需要清除一个现有分区。该分区策略具有很多性能优势，以下测试描述中有述。一些数据仓库的加载频率高于每月一次。虽然该白皮书说明的是月度数据加载，但此处概括的技术可以应用到任何基于时间进行加载的情况。Oracl