优化星型查询

当你使用星型查询时，你需要考虑以下两点：

1. 调整星型查询
2. 使用星型转换

调整星型查询
为了获得星型查询的最佳性能，遵循一些基本准则是非常重要的：

• 应该为事实表的每一个外键列都创建位图索引。
• 初始化参数STAR_TRANSFORMATION_ENABLED应设置为TRUE。这将开启对星型查询的 重要优化功能。为了向下兼容，它在默认情况下设置为FALSE。

当一个数据仓库满足这些条件，在数据仓库中运行的大多数星型查询将会使用被称为星形转换的查询执行策略。星型转换为星型查询提供了非常高效的查询性能。

使用星型转换

星型转换是依靠隐式重写（或转换）原始星型查询SQL的强大优化技术。最终用户不需要知道任何关于星形转换的细节。 Oracle数据库的查询优化器会在合适的地方自动选择星型转换。

星型转换是一个查询转换，旨在高效执行星型查询。 Oracle数据库使用两个基本阶段来处理星型查询。第一阶段是从事实表（结果集）精确地检索出必要的行。由于这种检索使用了位图索引，因此是非常高效的。第二阶段是将一阶段查到的结果集与维度表相结合。最终用户查询的一个例子是：“在西部和西南部地区的销售门店的最后三个季度，食品部门的销售额和利润是多少？”这是一个简单的星型查询。

使用位图索引的星型转换
星型转换的一个前提条件，即在事实表的每一个连接列上都有一个单列位图索引。这些连接列包括所有的外键列。
例如，sh示例模式下的sales表，分别在TIME_ID，CHANNEL_ID，CUST_ID，PROD_ID和promo_id列上建有位图索引。
考虑下面的星型查询：

点击(此处)折叠或打开

1. SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
2.    SUM(s.amount_sold) sales_amount
3. FROM sales s, times t, customers c, channels ch
4. WHERE s.time_id = t.time_id
5. AND s.cust_id = c.cust_id
6. AND s.channel_id = ch.channel_id
7. AND c.cust_state_province = \'CA\'
8. AND ch.channel_desc in (\'Internet\',\'Catalog\')
9. AND t.calendar_quarter_desc IN (\'1999-Q1\',\'1999-Q2\')
10. GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

该查询分两个阶段进行处理。在第一阶段，Oracle数据库使用事实表外键列的位图索引从事实表中找出并检索出必要的行。也就是说，Oracle数据库从事实表中检索结果集，从本质上是使用下面的查询：

点击(此处)折叠或打开

1. SELECT ... FROM sales
2. WHERE time_id IN
3.   (SELECT time_id FROM times
4.    WHERE calendar_quarter_desc IN(\'1999-Q1\',\'1999-Q2\'))
5.    AND cust_id IN
6.   (SELECT cust_id FROM customers WHERE cust_state_province=\'CA\')
7.    AND channel_id IN
8.   (SELECT channel_id FROM channels WHERE channel_desc IN(\'Internet\',\'Catalog\'));

这是该算法的转换步骤，因为原始星型查询已被改造成子查询表示方式。访问事实表的这种方法利用了位图索引的优势。直观地说，在关系数据库中位图索引提供了基于集合的处理方案。 Oracle实现了非常快速的方法去处理集合操作，如AND（交集），OR（并集），MINUS和COUNT。

在这个星形查询中，TIME_ID位图索引用于标识事实表中销售时间在1999年-Q1所有行的集合。这个集合被表示为位图（一个由1和0组成的字符串，用来表示事实表中的哪些行属于该集合）。

一个类似的位图检索对应sales事实表中1999年第二季度的的所有行。该位图的或操作用于合并Q1销售结果集与Q2销售结果集。

另外还将在客户维度，产品维度来完成集合操作。在星型查询处理的这一点上，有三个位图。每个位图对应于一个单独的维度表，并且每个位图代表了事实表中满足单独维度约束的行的集合。

这三个位图通过AND操作被合并成一个单独的位图。这个最终的位图表示了事实表中满足所有维度约束的行集合。这就是结果集，从评估查询所需的事实表行的确切集合。请注意，没有任何事实表中的实际数据被访问。所有这些操作完全依赖位图索引和维度表。因为位图索引的压缩数据表示，位图集合操作是非常高效的。

一旦确认了结果集，可以通过位图来访问sales表的实际数据。从事实表中仅仅检索需要的数据。在这一点上，Oracle数据库，有效地将所有维度表和事实表结合了起来。这种技术提供了优异的性能，因为Oracle数据库使用了一个逻辑的连接操作将所有维度表和事实表连接恰里，而不是将每个维度表与事实表分别进行连接。。

该查询的第二阶段是将事实表中的行（结果集）与维度表连接在一起。 Oracle使用最有效的方法来访问和连接维度表。许多维度表非常小，并且全表扫描通常是针对这些维度表的最有效的访问方法。对于大尺寸的表，全表扫描可能不是最有效的访问方法。在前面的例子中， product.department列的位图索引可以用来快速识别在食品部门的所有产品。基于优化程序对每个维度表的大小和数据分布的判断，Oracle数据库的优化器会针对给定的维度表来自动确定哪种访问方法是最适合的。

对于每个维度表而言，具体连接方法（以及索引方法）同样将被优化器智能地确定。哈希连接往往是连接维度表最有效的算法。一旦连接了所有的维度表，最终的结果将返回到用户。从一个表中检索出匹配行，然后连接到另一个表的查询技术通常被称为半连接。

使用位图索引星型转换的执行计划
下面这个典型的执行计划是由带位图索引的星型转换生成的：

点击(此处)折叠或打开

1. SELECT STATEMENT
2.  SORT GROUP BY
3.   HASH JOIN
4.    TABLE ACCESS FULL CHANNELS
5.    HASH JOIN
6.     TABLE ACCESS FULL CUSTOMERS
7.     HASH JOIN
8.      TABLE ACCESS FULL TIMES
9.      PARTITION RANGE ITERATOR
10.       TABLE ACCESS BY LOCAL INDEX ROWID SALES
11.        BITMAP CONVERSION TO ROWIDS
12.         BITMAP AND
13.          BITMAP MERGE
14.           BITMAP KEY ITERATION
15.            BUFFER SORT
16.             TABLE ACCESS FULL CUSTOMERS
17.            BITMAP INDEX RANGE SCAN SALES_CUST_BIX
18.          BITMAP MERGE
19.           BITMAP KEY ITERATION
20.            BUFFER SORT
21.             TABLE ACCESS FULL CHANNELS
22.            BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
23.          BITMAP MERGE
24.           BITMAP KEY ITERATION
25.            BUFFER SORT
26.             TABLE ACCESS FULL TIMES
27.            BITMAP INDEX RANGE SCAN SALES_TIME_BIX

在这个计划中，是通过一个由三个位图合并而来的位图访问路径来访问事实表。这三个位图是BITMAP MERGE根据行资源树的位图生成的。每个这样的行资源树是从子查询行资源树的位图键迭代行源组成，在这个例子是一个全表扫描。对于每一个这样的值，位图键迭代行源从位图索引中检索位图。在相应的事实表行通过这种访问路径被检索到以后，它们与维度表及临时表合并产生的查询结果。

 

使用位图连接索引的星型转换

除了位图索引，您可以在星型转换中使用位图连接索引。假设你有以下附加索引结构：

点击(此处)折叠或打开

1. CREATE BITMAP INDEX sales_c_state_bjix
2. ON sales(customers.cust_state_province)
3. FROM sales, customers
4. WHERE sales.cust_id = customers.cust_id
5. LOCAL NOLOGGING COMPUTE STATISTICS;

使用位图连接索引的星型查询和之前的例子非常相似，唯一的区别是在星型查询的第一阶段，Oracle利用连接索引，而不是一个单表位图索引，去访问顾客数据。

使用位连接图索引星型转换的执行计划
下面这个典型的执行计划是由带位连接图索引的星型转换生成的：

点击(此处)折叠或打开

1. SELECT STATEMENT
2.  SORT GROUP BY
3.   HASH JOIN
4.    TABLE ACCESS FULL CHANNELS
5.    HASH JOIN
6.     TABLE ACCESS FULL CUSTOMERS
7.     HASH JOIN
8.      TABLE ACCESS FULL TIMES
9.      PARTITION RANGE ALL
10.       TABLE ACCESS BY LOCAL INDEX ROWID SALES
11.        BITMAP CONVERSION TO ROWIDS
12.         BITMAP AND
13.          BITMAP INDEX SINGLE VALUE SALES_C_STATE_BJIX
14.          BITMAP MERGE
15.           BITMAP KEY ITERATION
16.            BUFFER SORT
17.             TABLE ACCESS FULL CHANNELS
18.            BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
19.          BITMAP MERGE
20.           BITMAP KEY ITERATION
21.            BUFFER SORT
22.             TABLE ACCESS FULL TIMES
23.            BITMAP INDEX RANGE SCAN SALES_TIME_BIX

这个执行计划和前面相比，区别在于使用位图索引扫描顾客维度的那一部分没有子查询。这是因为在customer.cust_state_province的连接述语信息已经满足了位图连接索引sales_c_state_bjix。

Oracle如何选择使用星型转换

优化器可以生成并保存一个未经转换的最优执行计划。如果星型转换被启用，优化器将尝试将其应用到查询；如果适用，则产生一个使用转换查询的最优执行计划。基于这两个版本的执行计划，优化器通过比较二者的成本估算，然后决定使用经过转换的最优执行计划或者是未经转换的版本。

如果查询需要访问事实表中的大部分行，最好使用全表扫描，而不是使用星型转换查询。但是，如果维度表的约束谓词具有充分的可选性，也就是说只会从事实表中检索很小一部分数据，那么基于转换的执行计划很有可能会更好。

需要注意的是，优化器会根据许多标准判断，在它任务合理的情况下才会根据维度表生成子查询。Oracle优化器并不保证为所有维度表生成子查询。基于表和查询的特性，优化器还可以决定该转换是否值得被应用到特定查询中。在这种情况下，优化器将会使用最优计划。

使用星型转换的限制条件

具有任何以下特征的表均不支持星型转换：

?查询使用了与位图访问路径不兼容的表提示（hint）

?查询包含绑定变量

?表没有位图索引。事实表的列必须有位图索引，优化器才能创建子查询。

?远程事实表。然而，子查询中允许使用远程维度表。

?反连接的表

?已经在子查询中用作维度表的表

?表是unmerged视图，并且不是分区视图

?事实表是unmerged视图

?事实表是分区视图

在以下场景优化器可能不会选择星型转换：

?表具有良好的单表访问路径

?表太小，不值得进行转换

此外，在下列条件下星型转换不使用临时表：

?数据库处于只读模式

?星型查询是串行化事务的一部分

hoegh

15.05.08

-- The End --