Oracle优化器：星型转换（Star Query Transformation ）

Star query是一个事实表（fact table）和一些维度表（dimension）的join。每个维度表都跟事实表通过主外键join，且每个维度表之间不join。

星型模式设计的好处：

1）提供了直接、直观的业务实体与终端用户schema设计的映射。

2）对典型的star query提供了高性能的优化。

3）通过大量的商业智能工具的广泛支持，可能会期望甚至要求数据仓库架构包含维度表。

星型模式是用于简单的数据集市和大型数据仓库。

雪花状模式（snowflake schema）：

Snowflake schema是star schema的一种，但更复杂。它的维度数据被分到多个表而不是一个大表。更多的维度表，更多的外键joins，使查询更复杂，查询性能下降。

Oracle建议使用star schema替代snowflake schema，除非你有别的原因。

优化Star Query：

1）在fact table的每个外键列上建立bitmap index。

2）初始化参数STAR_TRANSFORMATION_ENABLED 应被设为TRUE，默认false。

星型转换（Star transformation）能为star query提供高效的查询性能。

合适的情况下，Oracle会自动选择star transformation技术，隐视重写star query SQL，提高star query效率。

星型查询2个基本步骤：

1）从fact table中检索出必要的结果集。（bitmap index会提高效率）

2）结果集与维度表joins。

星型转换(bitmap index)

通过bitmap AND操作将3个维度表bitmaps为一个单独的bitmap，然后与fact table通过bitmap indexes逻辑JOIN。

SELECT ch.channel_class,

c.cust_city,

t.calendar_quarter_desc,

SUM(s.amount_sold) sales_amount

FROM sales s, times t, customers c, channels ch

WHERE s.time_id = t.time_id

AND s.cust_id = c.cust_id

AND s.channel_id = ch.channel_id

AND c.cust_state_province = 'CA'

AND ch.channel_desc in ('Internet', 'Catalog')

AND t.calendar_quarter_desc IN ('1999-01', '1999-02')

GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

星型转换（a Bitmap Join Index）

--建一个bitmap join index

CREATE BITMAP INDEX sales_c_state_bjix ON sales(customers.cust_state_province)

FROM sales, customers

WHERE sales.cust_id = customers.cust_id

LOCAL NOLOGGING COMPUTE STATISTICS;

即使用bitmap join index代替与表customer的bitmap join。

星型转换的限制

1、 下面的情况不支持星型转换

? 查询中使用hint

? 查询包含绑定变量

? Fact table上的bitmap indexes太少

? 远程fact tables

? Anti-joined tables

? Fact table是一个unmerged view

? Fact table是一个partitioned view

2、 优化器不选择star transformation的情况

? 表有一个好的单表访问路径

? 表太小不值得转换

3、临时表在下面情况下不适用star transformation

? 数据库read-only模式

? Star query是串行事务的一部分

优化星型查询

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

调整星型查询
使用星型转换

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

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

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

使用星型转换

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

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

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

SELECT ... FROM sales
WHERE time_id IN
(SELECT time_id FROM times
WHERE calendar_quarter_desc IN(\'1999-Q1\',\'1999-Q2\'))
AND cust_id IN
(SELECT cust_id FROM customers WHERE cust_state_province=\'CA\')
AND channel_id IN
(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数据库的优化器会针对给定的维度表来自动确定哪种访问方法是最适合的。

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

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

点击(此处)折叠或打开

SELECT STATEMENT
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL CHANNELS
HASH JOIN
TABLE ACCESS FULL CUSTOMERS
HASH JOIN
TABLE ACCESS FULL TIMES
PARTITION RANGE ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID SALES
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP MERGE
BITMAP KEY ITERATION
BUFFER SORT
TABLE ACCESS FULL CUSTOMERS
BITMAP INDEX RANGE SCAN SALES_CUST_BIX
BITMAP MERGE
BITMAP KEY ITERATION
BUFFER SORT
TABLE ACCESS FULL CHANNELS
BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
BITMAP MERGE
BITMAP KEY ITERATION
BUFFER SORT
TABLE ACCESS FULL TIMES
BITMAP INDEX RANGE SCAN SALES_TIME_BIX

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

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

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

点击(此处)折叠或打开