Oracle9i数据仓库执行性能增强

　　Oracle9i 数据仓库执行性能增强
　　简介
　　Oracle9i 显著改进了基本数据仓库(DWH) 功能的执行性能。由于无需更改任何模式或应用程序代码来利用绝大多数性能上的改进成果，因此几乎所有的客户都可以仅通过升级Oracle 安装而从中获益。本白皮书通过在测试数据仓库中执行典型的数据仓库任务和比较Oracle8i 和Oracle9i 的结果，说明了Oracle9i 对于执行性能的增强。
　　所有任务都是在受到严密控制的单个系统中进行的，确保了对Oracle8i 和Oracle9i 进行公正的比较。从加载数据、在整个转换过程中更改数据、创建实体化视图并对其刷新到最后访问数据，我们发现Oracle9i 显著改善了执行性能和系统利用率。
　　资源需求和相关性
　　本测试在相同硬件系统中，使用相同容量的内存、I/O 子系统和相同数目的CPU 对Oracle8i 和Oracle9i 进行了比较。每个测试都受到监视，以此证明结果的正确，且没有受到在同一时刻运行的其他工作负载的不利影响。所有时间信息都用mi:ss 格式来表示。
　　硬件
　　系统配置：Sun Microsystems sun4u 8 槽Sun Enterprise 4000/5000
　　　12 x 400 MHZ CPU
　　　10 GB 内存
　　　128 x 磁盘，原始设备，64KB Stripe Unit Size，最大吞吐量90 MB/秒
　　数据库
　　建立两个带有等价初始化参数(init.ora) 的基于TPC-H 模式的30 GB 数据库，启用新特性所需的参数除外。
　　　Oracle9i (9iopt)，整个系统全局范围662995316 字节
　　　Oracle8i (8iopt)，整个系统全局范围616460448 字节
　　基本执行性能增强
　　本白皮书说明了通过Oracle9i 带来的执行性能增强并非专门与那些需要明确激活的新特性有关。许多执行性能增强不需要对应用程序代码或数据库方案进行任何改动。因此，许多运行于现有的Oracle8i DWH 环境的语句在移植到Oracle9i 后可自动重新使用。我们将贯穿数据仓库进程，从加载、转换和构建阶段开始，然后将数据汇集到实体化视图中，刷新这些视图，使用典型查询语句来访问数据。最后，我们将查看在Oracle8i 和Oracle9i 中完成整个进程所需的时间，展示每种增强功能为执行性能带来的改进。
　　合并/更新插入
　　合并/更新插入增强
　　在一个数据仓库环境中，表（典型情况为事实表）需要使用来自在线系统的新数据进行周期性刷新。这些新数据可能包括数据仓库表中现有行的改变和/或需要插入的新行。如果新数据中的某一行对应于表中已经存在的某一项，则执行一次UPDATE；
　　如果此行的主键没有存在于表中，就执行一次INSERT。在Oracle9i 之前，无论是插入还是更新数据，这些操作都是针对每一行以一个DML (INSERT/UPDATE) 序列或者一个PL/SQL 循环判决来表达。但是两种方法都在执行性能和可用性上存在不足。
　　Oracle9i 通过MERGE 语句这个新语法对SQL 进行了扩展，也就是说，将条件型INSERT 和UPDATE 序列包含在一条单一的基本语句中，从而克服了这些不足，使得数据仓库应用程序的实现更加简单和直观。
　　‘IF ROW EXISTS -THEN-UPDATE ELSE-INSERT’ 条件序列也称为UPSERT。
　　合并/更新插入结果
　　在Oracle9i 中，对于更新插入进程使用新的基本MERGE 语句就能够改善执行性能，而不是通过相同语义使用一个条件INSERT 和UPDATE 语句序列，这主要取决于含有要被合并的新行的源数据大小；这是在与目的表的大小相比较的情况下，在该表中新行会被合并。与只改动目的表中的几行相比，改动多行会从更新插入中受益更多。如果更新插入通过一个MERGE 的语句来执行，源数据只需被扫描一次。然而，通过一个INSERT/UPDATE 序列，源数据表则需被扫描两次。
　　假如需要额外程序的检查或转换等进一步的约束，而这些约束又无法以标准的SQL来表达，这时从执行的角度来讲通过PL/SQL 进行一次合并至少是最不可取的选择。PL/SQL 方法明显比MERGE 或UPSERT/INSERT 的方法慢许多，而后两者都采用了面向集合的处理逻辑。
　　如果ETL （提取/转换/加载）进程要求在插入之前进行一次程序的转换，以流模式使用全新的可并行的PL/SQL 表函数，并且将结果传送给后继的合并操作，会显著地改善执行性能。
　　
　　在测试中，我们分析了一个三百万新客户的记录更新插入到一个包含四百五十万行的客户表中。运行在Oracle9i 中的全新的MERGE 语句与运行在Oracle8i 中的条件式UPDATE 和INSERT 语句序列相比较。在两个测试里，分别有一百五十万个记录被插入和更新。在这两个方案中，都采用了Oracle 的并行DML 功能来执行并行度为16的语句。执行性能的改善是由于源表的最小化扫描，除此之外，对于通过并行DML插入的行，内部优化的索引维护操作也对执行性能的改善起到了一定的作用。与Oracle8i 中同样的INSERT 语句相比，在Oracle9i 中通过‘普通的’并行化INSERT，同样的内部优化也能提高执行性能。
　　执行性能测试Oracle8i Oracle9i
　　差异百分比改进
　　更新插入更新/插入03:28 合并02:24 01:04 30.7%
　　　
　　图1：更新插入测试结果
　　外部表
　　外部表增强
　　在Oracle9i 中，外部表新特性能以‘视觉表’的形式利用外部数据。通过该界面，外部表可被并行查询并且直接连接到内部的常规表，无需事先将外部数据加载到数据库中。
　　使用外部表能使ETL 过程流水线化，从而转换阶段不会因加载阶段而停滞不前。转换进程可与加载进程合并，却不会中断数据的连续性。不再需要为对比或转换而在存储区域存储数据。
　　外部表与常规表之间的重大区别在于外部编排表是只读的。任何DML 操作都不可行，而且无法对它们创建任何索引。在Oracle9i 中，外部表补充了现有SQL Loader 的功能。它们在特定的环境下很有用，一种情况是完整的外部资源必须连接到现有的数据库对象上并以复杂的方式转换；一种情况是外部数据量大且只使用过一次。
　　另一方面，在需要存储表的额外索引时，SQL*Loader 对于加载数据来说仍不失为一个较好的选择。对于数据在独立综合的转换中使用的操作或数据在进一步处理中只部分采用的操作来说，情况就是如此。
　　外部表结果
　　对外部表所做测试是对更新插入测试的一个补充。
　　在Oracle8i 中，一个仓库模式需要三个步骤加载新记录：
　　1. 将外部文件的数据加载到数据库的存储表中（在本例中为CUSTOMER_MERGE）
　　2. 在升级表中创建独特的索引（更新所需）
　　3. 通过UPDATE/INSERT 将数据合并到目的表中(CUSTOMER)
　　在Oracle9i 中，整个过程可缩短为两步：
　　1. 在数据库中创建元数据（CREATE TABLE …ORGANIZATION external）
　　2. 直接运用外部表（CUSTOMER_MERGE）作为Oracle9i MERGE 语句的来源。
　　上面所提的Oracle9i 的第一步很简单，因为SQL Loader 在基于现有的Oracle8i Loader控制文件上生成了完整的CREATE TABLE 语法。Oracle9i 方法的运用使Oracle8i 中两个必要的维护任务可以省略：数据加载和索引创建。与UPDATE 语句不同，MERGE语句对源表的索引不作特别的要求。
　　在本例中，Oracle8i 加载和索引的步骤所需时间如下：
　　1. 加载（三百万行，16 倍直接并行处理） 19 秒
　　2. 索引存储表（16 倍并行处理） 14秒
　　因此使用Oracle9i 外部表将节省33 秒。该测试同时证明，使用将外部表而不是内部表作为MERGE 的一种直接源仅仅会让执行性能略有下降：外部表需2:27 完成MERGE，而内部表需2:24 完成。
　　　
　　图2：外部表测试结果(*)：公平起见，结果取自于Oracle9i-MERGE
　　Group By
　　可变长度聚合
　　在DWH 的应用中，Group By 是个很关键的基本操作。Group By 在Oracle9i 中已经提升到运用可变长度聚合的高度。
　　在Group By、Cube 和Rollup 处理过程中，Oracle8i 的执行引擎把Oracle 数字作为聚合的累加器。分类引擎中填写了许多记录，这些记录由一连串的Group By 键和一系列聚合工作区域组成。当Group By 操作通过同样的分组键使用行时，聚合就会发生。Oracle8i 的聚合中SUM 占用了23 个字节、AVG 占用了31 个字节、VARIANCE 占用了54 个字节来累加所聚合的数值。分类记录的数量可以不断地增长，能反过来促使内存分类区域的快速消耗。
　　对于大多数应用程序来讲，为聚合而使用全长的Oracle 数字很显然是浪费空间。假如知道一个特定的Oracle 数字只需7 个字节而不是22 个字节的话，我们就能压缩聚合工作区域从而多得到3 倍空间。这可以直接转化为执行性能的改善。如此更加充分地利用空间，分类引擎几乎不会占用磁盘的空间；即使确实发生这样的情形，分类引擎读写的数据也只会是该示例中读写数据的四分之一。
　　聚合测试结果
　　随着Group By 查询中处理的组数不断增加，Oracle9i 对于Oracle8i 的优势也不断加强。针对组数很少的一组查询，两次释放的区别并不太明显。为了测试，我们选定了8 个聚合操作符（GroupBy.sql）并且通过设定SORT_AREA_SIZE 规定了所用内存为20MB 和30MB。具有20MB 内存的条件下，Oracle8i 和Oracle9i 都占用了磁盘空间。此时，Oracle9i 所占用的磁盘空间几乎只是Oracle8i 所占用的一半而且速度还快10％。具有30MB 的内存条件下，Oracle9i 正好达到内存要求而Oracle8i 仍需占用磁盘空间。在该测试中，Oracle9i 的速度要快20％。两个测试中所用的并行度均为24，这表明在第一个测试中，24 个进程的每一个进程都使用了20MB 的内存；在第二个测试中使用的内存则为30MB。
　　
　　　
　　图3：Group By 测试结果DOP 为并行度，
　　Sort = SORT_AREA_SIZE
　　动态内存管理
　　专用服务器的PGA 内存
　　在Oracle9i 之前，诸如SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE 和CREATE_BITMAP_AREA_SIZE 的内存工作区域必须手动设置。根据系统运行的应用程序和工作负载，这些参数的固定设置会引起问题。当设置太低时，Oracle 不能在内存中执行费时操作而且不得不写入磁盘中。当设置太高且过多的进程使用过多的内存时，操作系统最终会将内存转换到磁盘。在这两例中用户必须面对执行性能下降的问题。由于数据库的应用程序在内存的使用上可以有不同的概要文件，因此，正确地设置这些参数的工作变得十分困难。
　　Oracle9i 引入了动态内存管理。为采用该模式，DBA 只需为Oracle 实例（针对专用服务器而言）指定PGA 内存的大小。这可以通过设置新的初始化参数PGA_AGGREGATE_TARGET 来实现。指定的数字（如2G）为Oracle 实例的全局目标。Oracle 控制所有数据库服务器进程之间的内存使用，从而保证不会超过该目标。在该目标内，当工作量低时，工作进程能获得最佳的PGA 内存支持。
　　动态内存管理结果
　　基于连接(HASH_AREA_SIZE) 和Group By 聚合，我们创建不同的语句。第一个语句Dyn_01.sql 与Group By （参考GroupBy.sql）的测试情况非常相似，只是它从Linetem 读取了更多的分区。其他的语句则基于单个和多个合并的实体化视图(Mav10.sql, Mav12.sql, Mav.30.sql)。以上测试中，我们注意到可变长度聚合这个新特性经过了内存中的分类操作的完全加工，从动态内存管理获益不少。我们发现，在更多采用这两个特性的示例中，Oracle8i 必须将分类信息写到磁盘上而Oracle9i 却不需要。Mav10.sql 是一个合并的实体化联接视图，连接了Lineitem 和Parts（散列联接），具有大量的AVG、SUM 和VARIANCE 的聚合，同时带有Parts 和Lineitem 的谓词，产生了二百四十万行。Mav12.sql 也是关于Lineitem 和Part 的合并的实体化联接视图，不带有谓词，产生了二百五十万行。Mav30.sql 是关于定单的单个表合并的实体化视图，包含三百万行。
　　　
　　图4：动态内存管理测试结果DOP 为并行度，
　　Sort＝SORT_AREA_SIZE（只用于Oracle8i）
　　实体化视图，快速刷新
　　快速刷新的优化执行方案
　　各种实体化视图的快速刷新在Oracle9i 中得到了改进。受益最大的是实体化联接视图和合并的实体化联接视图。优化执行方案对快速刷新的执行时间有很大的影响。
　　在Oracle9i 之前，刷新的例程含有难懂的代码提示。根据需要刷新的数据量，执行方案在某些时候可能不会是优化的。问题在于缺少了关于快速刷新操作所访问的大多数对象（如all_sumdelta、实体化视图或快照）的统计数据。执行快速刷新前，快速统计基数并将结果交由优化程序使用，Oracle9i 便可以智能地优化操作过程并得到最佳效果。
　　快速刷新结果
　　大量的优化和改进已体现在Oracle9i 中。现在有可能对各类实体化视图的所有操作进行快速刷新。在Oracle8i 中，存在一些限制，如当对合并的实体化联接视图快速刷新时只允许‘插入附录’或‘直接加载’。为了公平比较，MAV12 只用于小型插入操作，第二次结果（179000 行的插入）来自于合并的单个表实体化视图的刷新(MAV40)。
　　　
　　图5：快速刷新结果
　　PL/SQL
　　PL/SQL 增强
　　Oracle9i 对PL/SQL 的执行性能做出了重大的改进。PL/SQL 执行性能的增强要求改变现有的应用程序代码，如表函数、扩展的批操作等，除此之外，还有一些显著的执行性能的改进，对现有的应用程序则是透明的。最重要的是：
　　　用SQL 语句优化执行用户定义的函数
　　　对PL/SQL 的本地编辑
　　用SQL 语句优化执行用户定义的函数：
　　当用户定义的PL/SQL 函数内嵌在一个SQL 语句中时，SQL 语句执行程序和PL/SQL引擎会用于查询的每一个匹配行。如果有众多匹配行的话，函数的调用时间就会成为总执行时间中最重要的部分。在Oracle9i 中，这个调用机制已经完全得到优化，显著地减少了指令的数量。
　　对PL/SQL 的本地编辑：
　　Oracle8i 的PL/SQL 程序单元在编辑过程中被编为字节代码该字节代码在执行时必须由PL/SQL 引擎解释。这种机制依然可用于Oracle9i 中。然而，如今PL/SQL 程序单元可以进行本地编辑本地编辑过程首先引入到Oracle8i 的存储Java 程序单元并已应用于Oracle9i 中的PL/SQL。本地编辑PL/SQL 库单元后，将其以分享的程序库的形式存储于PL/SQL 文件系统中。执行期间，分享的程序库会动态地链接到Oracle 执行程序。本地编辑特别有利于处理复杂编程逻辑（复杂循环、复杂运算等）的PL/SQL程序。在这种情况下，执行性能提高2－5 个系数是可以实现的。
　　PL/SQL 结果
　　我们的案例分析表明，对于PL/SQL 函数，Oracle9i 在执行性能上的改善已经内嵌在了SQL 中。一种在扩展的价格和折扣存储在已检索的行中时计算折扣定价的简单函数（function.sql）应用于2 万和4 万记录语句的SELECT 列表中。
　　PL/SQL 函数的调用时间是整个执行时间中最关键的部分，原因是该函数简单，只含有一个指示命令。测试表明了41％ 的执行性能改进来自于该语句。采用了默认字节代码编辑本地编辑在这个测试里只小小改进了整体执行性能是因为该函数的简单性。
　　　
　　图6：PL/SQL 测试结果
　　索引跳扫
　　索引跳扫增强
　　在Oracle9i 之前的版本中，只有在主要的索引前缀列包括于语句的谓词里或索引的完全扫描已执行的条件下，才可以使用复合索引。通过Oracle9i，即使前缀列的值并未在检索中采用优化程序也能使用复合索引。优化程序通过一种名为‘索引跳扫’的算法为不使用前缀列的值检索ROWID。跳扫减少了添加索引的需要，来支持不参照现有索引的前缀列的临时查询。由于太多索引的存在导致了DML 操作执行性能的降低，从而需要许多更改操作，这时跳扫就能派上用场。在没有发现明确策略来选择在复合索引中作为前缀列的列时，该算法也有用。
　　前缀列在查询时应该识别力最强，同时其参照率也要最高。有时候，这两个要求由复合索引中的两个不同的列所满足，从而被迫进行折中或使用多个索引。在跳扫时，对于前缀列中的每个特定值都要探察B 树。对于每个前缀列的值，都进行常规搜索算法。结果表现为对索引子集的一系列搜索，每个搜索看上去都来自于使用前缀列的特定值查询的结果。
　　索引跳扫结果
　　在ORDERS 表上创建了连接的索引I_ORDERS_SKIP，其中包括的列有O_ORDERPRIORITY、O_ORDERSTATUS、O_TOTALPRICE (index_skip.sql)。表ORDERS 的基数为45,000,000。每个索引列的特定数值是：
　　O_ORDERPRIORITY : 5
　　O_ORDERSTATUS : 3
　　O_ORDERTOTAL_PRICE : 31078592
　　针对表测试了下列检索：
　　SELECT o_orderpriority,
　　o_orderstatus,
　　SUM(o_totalprice)
　　FROM orders
　　WHERE o_orderstatus = ’F’ AND
　　o_totalprice BETWEEN 0 AND 2000
　　GROUP BY o_orderpriority, o_orderstatus
　　检索过滤了64,771 行（0.14％）。以下的结果表明了新访问路径的巨大值。
　　　
　　图7：索引跳扫测试结果
　　位图联接索引
　　位图联接索引增强
　　自从Oracle 7.3 以来，Oracle 一直支持一致的位图索引和B 树索引。从发行到现在，位图索引得到了显著的改进。Oracle9i 主要的增强在于有能力在另一个表的基础上建立表的位图索引。这种索引类型叫做位图联接索引。位图联接索引可以是单个或者多个列索引，能够合并不同表的列。位图联接索引以一种非常有效的方式实体化了预先计算的联接结果。在数据仓库中的典型应用是在一维或者多维的表中的一个或者多个列中以星型或雪花模式创建对真值表的位图联接索引。这会极大地减少星型查询处理时间，特别是当一个星型查询具有对不同维度表的低基数属性的过滤谓词以及这些属性的组合对真值表具有高度的选择性时。
　　与Oracle8i 的实体化联接视图比较，位图联接索引有更有效的空间，因为它们用一种压缩的形式来存储ROWID。另一个可能的优势是位图联接索引能够和其他在同一个表中的位图索引组合在一起来处理一个综合的查询，它们像其他的索引一样自动维持（但不同于实体化视图，它要求更新）。因此，在许多情况下位图联接索引的使用在Oracle9i 中是一个比较好的选择。
　　位图联接索引结果
　　在我们的测试案例中，我们在基于维度表列PARTS (P_TYPE) 和SUPPLIER(S_NATIONKEY) 创建了对表PARTSUPP 的一个组合位图联接索引。这个表的基数是：
　　SUPPLIER : 6000000 行
　　PARTS : 300000 行
　　PARTSUPP : 24000000 行
　　列的基数是：
　　PARTS(P_TYPE) : 150 个不同值
　　SUPPLIER(S_NATIONKEY) : 25 个不同值
　　请求的查询：
　　SELECT COUNT (DISTINCT ps_suppkey),
　　AVG (ps_supplycost),
　　MAX (ps_supplycost),
　　MIN (ps_supplycost)
　　FROM partsupp ps, supplier s, parts p
　　WHERE ps.ps_suppkey = s.s_suppkey AND
　　ps.ps_partkey=p.p_partkey AND
　　s.s_nationkey = 24 AND
　　p.p_type=’medium burnished steel’;
　　查询返回关于PARTSUPP 的6475 行。它运行的并行度为16。Oracle9i 使用了位图联接索引访问路径。该访问路径在Oracle8i 中给出的最好结果是全部三个表的一个散列联接，由优化程序默认选择。注意，在Oracle9i 中取得的00：07 秒是读取磁盘上所有块时的结果；所有的块都被缓存后，查询在00：02 中结束。
　　　
　　图8：位图联接索引测试结果
　　结论
　　Oracle9i 对Oracle8i 在本文中的比较结果显示了Oracle9i 在所有的测试案例中都提高了执行性能。测试任务非常普通，一般会在在数据仓库中加载、转换、聚合、刷新和选择数据时执行。客户可以从一些基本执行性能的改进中获益，体现在可变长度聚合、动态内存管理、快速刷新和PL/SQL 执行性能增强，并且无需改变模式或应用程序代码。采用诸如索引跳扫、外部表、合并和位图联接索引等特性将进一步提升执行性能。
　　通过我们的测试数据集和硬件使用Oracle8i，数据加载、聚合和刷新周期将需要55 分14 秒。下面是执行的操作：
　　- 外部表/直接更新插入04:01
　　- 可变长度聚合05:15 （两次测试的时间总和）
　　- 创建实体化视图(mav12) 35:02
　　- 创建实体化视图(mav30) 05:53
　　- 快速刷新02:17 （两次测试的时间总和）
　　- PL/SQL 01:21 （第二个测试案例）
　　- 索引跳扫00:23（并行索引完全扫描）
　　- 联接（无位图联接索引） 01:02
　　在Oracle9i 中，相同的任务需要35 分18 秒。
　　- 外部表/直接更新插入02:27
　　- 可变长度聚合04:25
　　-创建实体化视图(mav12) 23:04
　　-创建实体化视图(mav30) 03:50
　　-快速刷新00:42（两次测试的时间总和）
　　- PL/SQL 00:48（第二个测试案例）
　　- 索引跳扫00:02
　　- 联接（有位图联接索引） 00:07
　　
　　图9： Oracle9i 在所有测试结果中执行性能增强
　　
　　我们确信，对于比我们这个案例研究中所使用的30 GB 数据库规模更大的数据仓库，Oracle9i 相对Oracle8i 的执行性能优势应该是相似的，或者更胜一筹。对于实体化视图创建等长期运行的操作而言，执行性能的增强表现在超大型数据仓库运行时间的显著降低，每个周期很可能缩短数小时。由于长期运行的操作时间明显减少，就可以减少刷新处理的窗口，从而为用户加强了可用性。此外，数据可以更加频繁地刷新，加强了数据流通，有利于作出更好的商业决策。通过位图联接索引和索引跳扫，超大型数据仓库还可以节省磁盘容量。
　　Oracle 已成为数据仓库行业的领头羊。自Oracle 7.3 以来Oracle 在数据仓库执行性能方面的数十项增强功能为决策支持任务创建了丰富和稳健的特性集。Oracle9 i 在执行性能上的重大改进巩固了Oracle 的领先地位，使其成为数据仓库领域中更加健全的平台。
　　
　　附录
　　初始化文件
　　init8iopt.ora
　　********************************************************************
　　db_file_multiblock_read_count = 64
　　audit_trail = FALSE
　　compatible = 8.1.7
　　control_files = ("/private5/mbender/dbs/control8iopt1.ora",
　　"/private5/mbender/dbs/control8iopt2.ora")
　　db_block_buffers = 20000
　　db_block_size = 8192
　　db_files = 1023
　　db_file_multiblock_read_count = 64
　　db_name = 8iopt
　　distributed_transactions = 20
　　dml_locks = 100000
　　enqueue_resources = 50000
　　hash_area_size = 10000000
　　job_queue_processes = 2
　　large_pool_size = 200000000
　　log_buffer = 8388608
　　max_dump_file_size = 500000
　　max_rollback_segments = 650
　　nls_date_format = YYYY-MM-DD
　　open_cursors = 1024
　　optimizer_index_cost_adj = 20
　　parallel_broadcast_enabled = true
　　parallel_execution_message_size = 8192
　　parallel_max_servers = 200
　　partition_view_enabled = true
　　processes = 400
　　query_rewrite_enabled = true
　　query_rewrite_integrity = trusted
　　sessions = 400
　　shared_pool_size = 200000000
　　sort_area_size = 10000000
　　transactions = 512
　　transactions_per_rollback_segment = 20
　　user_dump_dest = "/private5/mbender/udump8i"
　　
　　audit_trail = FALSE
　　compatible = 8.2.0
　　control_files = ("/private5/mbender/dbs/control9iopt1.ora",
　　"/private5/mbender/dbs/control9iopt2.ora")
　　db_block_buffers = 20000
　　db_block_size = 8192
　　db_files = 1023
　　_disable_multiple_block_sizes=true
　　db_file_multiblock_read_count = 64
　　db_name = 9iopt
　　distributed_transactions = 20
　　dml_locks = 100000
　　enqueue_resources = 50000
　　hash_area_size = 10000000
　　# hash_area_size : needed only for comparison to 8i
　　# (Group By tests), with Oracle9i Dynamic Memory
　　# Management, the setting is not necessary
　　large_pool_size = 200000000
　　log_buffer = 8388608
　　max_dump_file_size = 500000
　　max_rollback_segments = 650
　　nls_date_format = YYYY-MM-DD
　　open_cursors = 1024
　　optimizer_index_cost_adj = 20
　　parallel_broadcast_enabled = true
　　parallel_execution_message_size = 8192
　　parallel_max_servers = 200
　　partition_view_enabled =true
　　pga_aggregate_target = 4g
　　processes = 400
　　query_rewrite_enabled = true
　　query_rewrite_integrity = trusted
　　sessions = 400
　　shared_pool_size = 200000000
　　sort_area_size = 10000000
　　# sort_area_size : needed only for comparison to 8i
　　# (Group By tests), with Oracle9i Dynamic Memory
　　# Management, the setting is not necessary
　　transactions = 512
　　transactions_per_rollback_segment = 20
　　user_dump_dest = "/private5/mbender/udump9i"
　　
　　
　　生成语句
　　合并/插入更新
　　
　　Rem upsert9i.sql
　　Rem
　　Rem NAME
　　Rem upsert9i.sql
　　Rem
　　Rem DESCRIPTION
　　Rem Modifies the table customer using the new 9i MERGE-statement
　　Rem Destination table: CUSTOMER : 4.5 M. Rows
　　Rem Source table: CUSTOMER_MERGE : 3 M. Rows
　　Rem 1.5 M. New Entries (Insert) 1.5 / 1.5 M. existing (Update)
　　Rem Parallel Degree: 16
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem batzenbe 03/24/01 - Created
　　Rem
　　alter session enable parallel dml;
　　MERGE /*+ PARALLEL(O,16) */ INTO CUSTOMER O
　　USING CUSTOMER_MERGE N
　　ON (O.C_CUSTKEY=N.C_CUSTKEY)
　　WHEN MATCHED THEN
　　UPDATE SET
　　O.C_COMMENT = N.C_COMMENT
　　,O.C_NAME = N.C_NAME
　　,O.C_ADDRESS = N.C_ADDRESS
　　,O.C_PHONE = N.C_PHONE
　　WHEN NOT MATCHED THEN
　　INSERT
　　( C_CUSTKEY
　　,C_MKTSEGMENT
　　,C_NATIONKEY
　　,C_NAME
　　,C_ADDRESS
　　,C_PHONE
　　,C_ACCTBAL
　　,C_COMMENT )
　　VALUES
　　( N.C_CUSTKEY
　　,N.C_MKTSEGMENT
　　,N.C_NATIONKEY
　　,N.C_NAME
　　,N.C_ADDRESS
　　,N.C_PHONE
　　,N.C_ACCTBAL
　　,N.C_COMMENT );
　　commit;
　　
　　Rem upsert8i.sql
　　Rem
　　Rem NAME
　　Rem upsert8i.sql
　　Rem
　　Rem DESCRIPTION
　　Rem Modifies the table customer using 8i UPDATE/INSERT-statements
　　Rem Destination table: CUSTOMER : 4.5 M. Rows
　　Rem Source table: CUSTOMER_MERGE : 3 M. Rows
　　Rem 1.5 M. New Entries (Insert) 1.5 / 1.5 M. existing (Update)
　　Rem Parallel Degree: 16
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem batzenbe 03/24/01 - Created
　　Rem
　　alter session enable parallel dml;
　　UPDATE /*+ PARALLEL(N,16) */
　　(
　　SELECT /*+ PARALLEL(N,16) PARALLEL(O,16) */
　　O.C_COMMENT AS OLD_C_COMMENT
　　,N.C_COMMENT AS NEW_C_COMMENT
　　,O.C_NAME AS OLD_C_NAME
　　,N.C_NAME AS NEW_C_NAME
　　,O.C_ADDRESS AS OLD_C_ADDRESS
　　,N.C_ADDRESS AS NEW_C_ADDRESS
　　,O.C_PHONE AS OLD_C_PHONE
　　,N.C_PHONE AS NEW_C_PHONE
　　FROM CUSTOMER_MERGE N
　　,CUSTOMER O
　　WHERE O.C_CUSTKEY=N.C_CUSTKEY
　　)
　　SET
　　OLD_C_COMMENT = NEW_C_COMMENT
　　,OLD_C_NAME = NEW_C_NAME
　　,OLD_C_ADDRESS = NEW_C_ADDRESS
　　,OLD_C_PHONE = NEW_C_PHONE;
　　INSERT /*+ PARALLEL(CUSTOMER,16) */ INTO CUSTOMER
　　SELECT /*+ PARALLEL(N,16) */ * FROM CUSTOMER_MERGE N
　　WHERE NOT EXISTS
　　(SELECT ’’ FROM CUSTOMER O
　　WHERE N.C_CUSTKEY=O.C_CUSTKEY);
　　commit;
　　
　　外部表
　　
　　Rem upsert_external.sql
　　Rem
　　Rem NAME
　　Rem upsert_external.sql
　　Rem
　　Rem DESCRIPTION
　　Rem Modifies the table customer using the new 9i MERGE- and External Table feature
　　Rem Destination table: CUSTOMER : 4.5 M. Rows
　　Rem Source table: CUSTOMER_EXTERNAL: 3 M. Rows (External Table)
　　Rem 1.5 M. New Entries (Insert) 1.5 / 1.5 M. existing (Update)
　　Rem Parallel Degree: 16
　　Rem
　　Rem Consists of two steps:
　　Rem 1.) Creating External Table
　　Rem 2.) Merge using External Table directly
　　Rem MODIFIED (MM/DD/YY)
　　Rem batzenbe 03/27/01 - Created
　　Rem
　　DROP DIRECTORY CUSTOMER_DIR ;
　　CREATE DIRECTORY CUSTOMER_DIR AS ’/private/test/ext_load_dat’ ;
　　DROP TABLE CUSTOMER_EXTERNAL;
　　CREATE TABLE CUSTOMER_EXTERNAL
　　(
　　C_CUSTKEY NUMBER,
　　C_MKTSEGMENT CHAR(10),
　　C_NATIONKEY NUMBER,
　　C_NAME VARCHAR2(25),
　　C_ADDRESS VARCHAR2(40),
　　C_PHONE CHAR(15),
　　C_ACCTBAL NUMBER,
　　C_COMMENT VARCHAR2(117)
　　)
　　ORGANIZATION external
　　(
　　TYPE oracle_loader
　　DEFAULT DIRECTORY CUSTOMER_DIR
　　ACCESS PARAMETERS
　　(
　　RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
　　BADFILE ’CUSTOMER_DIR’:’cust_external.bad’
　　LOGFILE ’CUSTOMER_DIR’:’cust_external.log’
　　FIELDS LDRTRIM
　　(C_CUSTKEY POSITION (1:10) CHAR (10)
　　,C_MKTSEGMENT POSITION (11:20) CHAR (10)
　　,C_NATIONKEY POSITION (21:23) CHAR (3)
　　,C_NAME POSITION (24:41) CHAR (18)
　　,C_ADDRESS POSITION (42:81) CHAR (40)
　　,C_PHONE POSITION (82:96) CHAR (15)
　　,C_ACCTBAL POSITION (97:104) CHAR (8)
　　,C_COMMENT POSITION (105:134) CHAR(30))
　　) location
　　
　　(
　　’TPCD.CUST_MERGE_VAR_PAR_SYS_P8653.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8654.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8655.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8656.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8657.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8658.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8659.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8660.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8661.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8662.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8663.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8664.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8665.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8666.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8667.DAT’
　　,’TPCD.CUST_MERGE_VAR_PAR_SYS_P8668.DAT’
　　)
　　)REJECT LIMIT UNLIMITED;
　　alter session enable parallel dml;
　　MERGE /*+ PARALLEL(O,16) */ INTO CUSTOMER O
　　USING CUSTOMER_EXTERNAL N
　　ON (O.C_CUSTKEY=N.C_CUSTKEY)
　　WHEN MATCHED THEN
　　UPDATE SET
　　O.C_COMMENT = N.C_COMMENT
　　,O.C_NAME = N.C_NAME
　　,O.C_ADDRESS = N.C_ADDRESS
　　,O.C_PHONE = N.C_PHONE
　　WHEN NOT MATCHED THEN
　　INSERT
　　( C_CUSTKEY
　　,C_MKTSEGMENT
　　,C_NATIONKEY
　　,C_NAME
　　,C_ADDRESS
　　,C_PHONE
　　,C_ACCTBAL
　　,C_COMMENT )
　　VALUES
　　( N.C_CUSTKEY
　　,N.C_MKTSEGMENT
　　,N.C_NATIONKEY
　　,N.C_NAME
　　,N.C_ADDRESS
　　,N.C_PHONE
　　,N.C_ACCTBAL
　　,N.C_COMMENT );
　　commit;
　　
　　group by
　　
　　Rem GroupBy.sql
　　Rem
　　Rem NAME
　　Rem GroupBy.sql
　　Rem
　　Rem DESCRIPTION
　　Rem This query joins lineitem and parts with a large number of
　　Rem GROUP BY records as output.
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem mbender 03/07/01 - Created
　　Rem
　　alter session set sort_area_size = 20000000;
　　alter table lineitem parallel 24;
　　alter table parts parallel 24;
　　select count(*) from
　　(
　　select
　　p_brand
　　,p_container
　　,p_size
　　,l_shipmode
　　,l_shipinstruct
　　,p_name
　　,sum(l_quantity) as sum_qty
　　,sum(l_extendedprice) as sum_base_price
　　,variance(l_extendedprice) as var_base_price
　　,sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
　　,variance(l_extendedprice * (1 - l_discount)) as var_disc_price
　　,sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
　　,variance(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as var_charge
　　,avg(l_quantity) as avg_qty
　　,avg(l_extendedprice) as avg_price
　　,avg(l_discount) as avg_disc
　　,avg(l_extendedprice * (1 - l_discount)) as avg_disc_price
　　,avg(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as avg_charge
　　,count(*) as count#
　　from
　　lineitem,
　　parts
　　where
　　p_partkey = l_partkey
　　and p_container in (’SM CASE’, ’SM BOX’, ’SM PACK’, ’SM PKG’)
　　and l_shipdate between to_date( ’1995-01-01’, ’YYYY-MM-DD’)
　　and to_date( ’1997-06-28’, ’YYYY-MM-DD’)
　　group by p_name
　　,p_brand
　　,p_container
　　,p_size
　　,l_shipmode
　　,l_shipinstruct);
　　
　　
　　实体化视图
　　
　　Rem mav10.sql
　　Rem
　　Rem NAME
　　Rem mav10.sql
　　Rem
　　Rem DESCRIPTION
　　Rem This create statement creates an aggregated Materialized View
　　Rem an index on the GROUP BY keys is not created automatically
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem mbender 03/07/01 - Created
　　Rem
　　create materialized view mav10
　　pctfree 1
　　pctused 99
　　initrans 10
　　tablespace ts_ind
　　storage (initial 1m next 1m freelists 12 freelist groups 2 maxextents unlimited pctincrease 0)
　　parallel (degree 12 )
　　nologging
　　using no index
　　enable query rewrite
　　as
　　-- statement continues
　　
　　select
　　p_brand
　　,p_size
　　,p_container
　　,l_shipmode
　　,l_shipinstruct
　　,l_quantity
　　,l_shipdate
　　,count(l_quantity) as countqty
　　,count(l_discount) as countdisc
　　,count(l_extendedprice) as count_price
　　,count(l_extendedprice * (1 - l_discount)) as cou_disc_price
　　,count(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as cou_charge
　　,sum(l_quantity) as sumqty
　　,sum(l_discount) as sumdisc
　　,sum(l_extendedprice) as sum_base_price
　　,sum(l_extendedprice * (1 - l_discount)) as revenue
　　,sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
　　,variance(l_extendedprice) as var_base_price
　　,variance(l_extendedprice * (1 - l_discount)) as var_disc_price
　　,variance(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as var_charge
　　,avg(l_quantity) as avg_qty
　　,avg(l_discount) as avg_disc
　　,avg(l_extendedprice) as avg_price
　　,avg(l_extendedprice * (1 - l_discount)) as avg_disc_price
　　,avg(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as avg_charge
　　from
　　lineitem,
　　parts
　　where
　　p_partkey = l_partkey
　　and p_container in (’SM CASE’, ’SM BOX’, ’SM PACK’, ’SM PKG’)
　　and l_shipdate between to_date( ’1995-01-01’, ’YYYY-MM-DD’)
　　and to_date( ’1995-12-26’, ’YYYY-MM-DD’)
　　group by
　　p_brand
　　,p_size
　　,p_container
　　,l_shipmode
　　,l_shipinstruct
　　,l_quantity
　　,l_shipdate;
　　
　　Rem mav12.sql
　　Rem
　　Rem NAME
　　Rem mav12.sql
　　Rem
　　Rem DESCRIPTION
　　Rem This statement creates an Aggregated Materialized View,
　　Rem an index on the GROUP BY keys is not created automatically
　　Rem since no predicates on lineitem or parts are provided. A lot of
　　Rem hash and sort information has to be written down to temp
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem mbender 03/07/01 - Created
　　Rem
　　create materialized view mav12
　　pctfree 1
　　pctused 99
　　initrans 10
　　tablespace ts_ind
　　storage (initial 1m next 1m freelists 12 freelist groups 2 maxextents unlimited pctincrease 0)
　　parallel (degree 12 )
　　nologging
　　using no index
　　enable query rewrite
　　as
　　select
　　p_brand
　　,p_container
　　,l_shipdate
　　,count(*) cnt
　　,count(l_quantity) as countqty
　　,count(l_discount) as countdisc
　　,count(l_extendedprice) as count_price
　　,count(l_extendedprice * (1 - l_discount)) as cou_disc_price
　　,count(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as cou_charge
　　,sum(l_quantity) as sumqty
　　,sum(l_discount) as sumdisc
　　,sum(l_extendedprice) as sum_base_price
　　,sum(l_extendedprice * (1 - l_discount)) as revenue
　　,sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
　　,avg(l_extendedprice * (1 - l_discount)) as avg_disc_price
　　from
　　lineitem,
　　parts
　　where
　　p_partkey = l_partkey
　　group by
　　p_brand
　　,p_container
　　,l_shipdate;
　　
　　Rem mav30.sql
　　Rem
　　Rem NAME
　　Rem mav30.sql
　　Rem
　　Rem DESCRIPTION
　　Rem This is an aggregated materialized view on the table orders
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem batzenbe 03/22/01 - Created
　　Rem
　　create materialized view mav30
　　pctfree 1
　　initrans 10
　　tablespace ts_ind
　　storage (initial 1m next 1m freelists 12 freelist groups 2 maxextents unlimited pctincrease 0)
　　parallel (degree 12 )
　　nologging
　　enable query rewrite
　　as
　　select
　　o_custkey
　　,count(*) as count_ord_rev_cust
　　,sum(o_totalprice) as sum_ord_rev_cust
　　,max(o_totalprice) as max_ord_rev_cust
　　,min(o_totalprice) as min_ord_rev_cust
　　,avg(o_totalprice) as avg_ord_rev_cust
　　,variance(o_totalprice) as var_ord_rev_cust
　　,max(o_orderdate) as max_order_date
　　,min(o_orderdate) as min_order_date
　　from
　　orders
　　group by
　　o_custkey;
　　
　　Rem mav40.sql
　　Rem
　　Rem NAME
　　Rem mav30.sql
　　Rem
　　Rem DESCRIPTION
　　Rem This is an aggregated materialized view on the table lineitem
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem mbender 03/22/01 - Created
　　Rem
　　create materialized view ma_lineitem
　　pctfree 2
　　tablespace ts_default
　　parallel
　　nologging
　　REFRESH fast
　　ON demand
　　enable query rewrite
　　as select
　　count(*) as count_p_group,
　　l_shipdate,
　　l_returnflag,
　　l_linestatus,
　　sum(l_quantity) as sum_qty,
　　sum(l_extendedprice) as sum_base_price,
　　sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
　　count(l_extendedprice * (1 - l_discount)) as count_disc_price,
　　sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
　　count(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as count_charge,
　　count(l_quantity) as count_qty,
　　count(l_extendedprice) as count_price,
　　sum(l_discount) as sum_disc,
　　count(l_discount) as count_disc,
　　count(*) as count_order
　　FROM
　　lineitem
　　GROUP BY
　　l_returnflag ,
　　l_linestatus ,
　　l_shipdate ;
　　********************************************************************
　　快速刷新
　　********************************************************************
　　Rem Fast Refresh (mav12.sql)
　　Rem
　　Rem DESCRIPTION
　　Rem Fast Refresh test
　　Rem 1. create the snapshot logs
　　Rem 2. create the materialized view (mav12)
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem mbender 03/22/01 - Created
　　Rem
　　create materialized view log on lineitem
　　tablespace ts_ind
　　parallel
　　with rowid
　　(
　　l_shipdate
　　,l_quantity
　　,l_discount
　　,l_extendedprice
　　,l_tax
　　,l_partkey
　　)
　　including new values;
　　create materialized view log on parts
　　tablespace ts_ind
　　parallel
　　with rowid
　　( p_partkey
　　,p_brand
　　,p_container
　　)
　　including new values;
　　Rem
　　Rem create the materialized view mav12 (see mav12.sql)
　　Rem snapshot logs must be there before creating the mav
　　Rem
　　Rem
　　Rem in Oracle8i only insert append is supported
　　Rem
　　alter session enable parallel dml;
　　insert into orders select * from temp_o /* 1 Row */;
　　insert into lineitem select * from temp_l /* 2 Rows */;
　　commit;
　　Rem for Oracle8i
　　execute dbms_snapshot.refresh(’MAV12’,’F’,atomic_refresh=>false);
　　Rem for Oracle9i
　　execute dbms_snapshot.refresh(’MAV12’,’F’);
　　
　　
　　Rem Fast Refresh (mav40.sql)
　　Rem
　　Rem DESCRIPTION
　　Rem Fast Refresh test
　　Rem 1. create the snapshot logs
　　Rem 2. create the materialized view (mav40)
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem mbender 03/22/01 - Created
　　Rem
　　create materialized view log on lineitem
　　tablespace ts_ind
　　parallel
　　partition by range (l_shipdate)
　　(
　　,partition item1 values less than (to_date(’1992-01-01’,’YYYY-MM-DD’))
　　,partition item2 values less than (to_date(’1992-02-01’,’YYYY-MM-DD’))
　　,partition item3 values less than (to_date(’1992-03-01’,’YYYY-MM-DD’))
　　…
　　,partition item82 values less than (to_date(’1998-10-01’,’YYYY-MM-DD’))
　　,partition item83 values less than (to_date(’1998-11-01’,’YYYY-MM-DD’))
　　,partition item84 values less than (MAXVALUE)
　　)
　　with rowid
　　(
　　l_shipdate ,
　　l_returnflag ,
　　l_linestatus ,
　　l_quantity ,
　　l_extendedprice ,
　　l_discount ,
　　l_tax ,
　　l_suppkey ,
　　l_partkey ,
　　l_orderkey
　　)
　　including new values
　　;
　　Rem
　　Rem create the materialized view mav40 (see mav40.sql)
　　Rem snapshot logs must be there before creating the mav
　　Rem
　　alter session enable parallel dml;
　　insert into lineitem select * from temp_item_3 /* 179,000 Rows */;
　　commit;
　　execute dbms_snapshot.refresh(’MA40’);
　　****************************************************************************
　　pl/sql
　　****************************************************************************
　　Rem function.sql
　　Rem
　　Rem NAME
　　Rem function.sql
　　Rem
　　Rem DESCRIPTION
　　Rem This script consists of two steps:
　　Rem 1.) Creating the function total_price
　　Rem 2.) Test SQL applying total_price to table orders
　　Rem
　　Rem
　　Rem Prerequisite: Index on column l_orderkey
　　Rem Subselect determines how many rows are retrieved
　　Rem In this version: 400,000
　　Rem
　　Rem MODIFIED (MM/DD/YY)
　　Rem batzenbe 03/22/01 - Created
　　Rem
　　CREATE OR REPLACE FUNCTION
　　total_price
　　(
　　ex_price in number
　　,discount in number
　　)
　　RETURN NUMBER DETERMINISTIC PARALLEL_ENABLE
　　IS discount_price NUMBER(30,2);
　　BEGIN
　　discount_price:=ex_price*(1-discount);
　　RETURN (discount_price);
　　END;
　　select
　　round(max(total_price(l_extendedprice,l_discount)),2) as max_total_price
　　,round(min(total_price(l_extendedprice,l_discount)),2) as min_total_price
　　,round(avg(total_price(l_extendedprice,l_discount)),2) as avg_total_price
　　from lineitem l
　　where l_orderkey > (
　　select max(l_orderkey)-400000
　　from lineitem
　　);
　　*****************************************************************************
　　索引跳扫
　　*****************************************************************************
　　Rem index_skip.sql
　　Rem
　　Rem NAME
　　Rem index_skip.sql
　　Rem
　　Rem DESCRIPTION
　　Rem This script consists of two steps:
　　Rem 1.) Create index I_ORDERS_SKIP
　　Rem 2.) Querying the table
　　Rem
　　Rem Query filters 0,.14% of rows
　　Rem By default the optimizer chooses Bitmap Join Index in 9i and index fast full scan in 8i
　　Rem MODIFIED (MM/DD/YY)
　　Rem batzenbe 03/23/01 - Created
　　Rem
　　create index I_orders_skip
　　on orders
　　(
　　o_orderpriority
　　, o_orderstatus
　　, o_totalprice
　　)
　　nologging
　　parallel( degree 24)
　　storage (initial 10M next 10M pctincrease 0)
　　compute statistics
　　tablespace ts_ps;
　　select
　　o_orderpriority
　　,o_orderstatus
　　,sum(o_totalprice)
　　from
　　orders
　　where
　　o_orderstatus=’F’
　　and
　　o_totalprice between 0 and 2000
　　group by
　　o_orderpriority
　　,o_orderstatus;
　　*******************************************************************************
　　位图联接索引
　　*******************************************************************************
　　Rem bitmap_join.sql
　　Rem
　　Rem NAME
　　Rem bitmap_join.sql
　　Rem
　　Rem DESCRIPTION
　　Rem This script consists of two steps:
　　Rem 1.) Create index I_PARTSUP_BMJ (only Oracle9i )
　　Rem 2.) Querying the table
　　Rem
　　Rem
　　Rem By default optimizer chooses Bitmap Join Index aclearcase/" target="_blank" >ccess in 9i and hash join in 8i
　　Rem MODIFIED (MM/DD/YY)
　　Rem batzenbe 03/29/01 - Created
　　Rem
　　create bitmap index i_partsup_bmj
　　on partsupp(p.p_type,s.s_nationkey)
　　from partsupp ps,parts p,supplier s
　　where ps.ps_partkey=p.p_partkey
　　and ps.ps_suppkey=s.s_suppkey
　　local
　　nologging
　　parallel (degree 16)
　　storage (initial 1m next 1m pctincrease 0)
　　compute statistics;
　　select
　　count(distinct ps_suppkey)
　　,avg(ps_supplycost)
　　,max(ps_supplycost)
　　,min(ps_supplycost)
　　from partsupp ps,supplier s,parts p
　　where ps.ps_suppkey=s.s_suppkey
　　and ps.ps_partkey=p.p_partkey
　　and s.s_nationkey=24
　　and p.p_type=’medium burnished steel’;
　　

原文转自：http://www.ltesting.net