[DW]Two articles about Star Transformation(zz)

面试一家做数据仓库应用的公司时被问到什么是星型连接，之前只是听过这个名词，知道是数据仓库里用到的，但根本不晓得是个什么东东。今天在网上找到两篇相关文章看了一下，虽然仍然没有理解地十分透彻，但大概是个什么样子的已经有点概念了。记录在此。

数据库优化中什么是星型转换(Star Transform)?

作者: AnySQL.net
链接: http://www.anysql.net/oracle/olap_tuning_startransformation.html

在数据仓库中经常查询的SQL总带有下列特征:

• 几个表进行关联
• 只有一个数据量巨大的表, 称为事实表
• 其他的都是编码表, 称为维表
• 维表和事实表之间有主外键关系

假设有D1(key1),D2(key2),D3(key3),D4(key)四个小的维表和一个事实表F(key1,key2,key3,key4), 那么经常进行的查询将是:

SELECT
D1.xxx, D2.xxx, D3.xxx, D4.xxx,
SUM(F.xxx), SUM(F.xxx)
FROM F, D1, D2, D3, D4
WHERE F.KEY1=D1.KEY1 AND F.KEY2=D2.KEY2
AND F.KEY3=D3.KEY3 AND F.KEY4=D4.KEY4
AND D1.xxx=? AND D2.xxx=?
AND D3.xxx=? AND D4.xxx=?
GROUP BY D1.xxx, D2.xxx, D3.xxx, D4.xxx

而为了提高查询速度, 根据数据特征, key1,key2,key3,key4这四个字段会比较适合每个字段上建一个位图索引(Bitmap Index), 但是上面的查询语句并不能用到位图索引, 除非是进行了星型转换, 这个转换需要将SQL转换成下面的格式:

SELECT
D1.xxx, D2.xxx, D3.xxx, D4.xxx,
SUM(F.xxx), SUM(F.xxx)
FROM F, D1, D2, D3, D4
WHERE F.KEY1=D1.KEY1 AND F.KEY2=D2.KEY2
AND F.KEY3=D3.KEY3 AND F.KEY4=D4.KEY4
AND D1.xxx=? AND D2.xxx=?
AND D3.xxx=? AND D4.xxx=?
AND F.KEY1 IN (SELECT D1.KEY1 FROM D1 WHERE D1.xxx=?)
AND F.KEY2 IN (SELECT D2.KEY2 FROM D2 WHERE D2.xxx=?)
AND F.KEY3 IN (SELECT D3.KEY3 FROM D3 WHERE D3.xxx=?)
AND F.KEY4 IN (SELECT D4.KEY4 FROM D4 WHERE D4.xxx=?)
GROUP BY D1.xxx, D2.xxx, D3.xxx, D4.xxx

有条件的可以试试, 使用星型转换有三个条件:

• 事实表的每个代码列上有位图索引
• 参数STAR_TRANSFORMATION_ENABLED的值要设为TRUE
• 必须使用CBO, 所以最好对表进行适当的分析

应当好好地看一下没做转换和做了转换之后的执行计划的不同, 从而体会什么是星型转换.

小议星型转换

作者：hanson

链接：http://space.itpub.net/9842/viewspace-346823

星型转换是一个非常强大的优化技术，它是通过对原来的SQL语句的隐式的改写来实现的。终端用户并不需要知道有关星型转换的任何细节。数据库优化器会在合适的时候进行星型转换。
要获得星型转换的最大性能，需要遵循以下基本的指导：

1. 在事实表的每个外键列上都应该创建bitmap索引。
2. 需要将初始化参数STAR_TRANSFORMATION_ENABLED设置为true，这可以获得star query的重要的优化特性。缺省为false，是为了向下兼容。

如果数据仓库能够满足这个要求，则查询会使用star transformation，而这是提高基于事实表的查询效率的主要的技术。

数据库进行星型查询时，会使用两个基本的阶段：

1. 第一个阶段从事实表(或者说结果集)里获取所有必要的记录行。由于这是通过bitmap索引来检索数据，因此比较高效。
2. 第二个阶段将该结果集与维度表进行关联。这叫做semi-join(也就是exists和in写法)。

注意：只有oracle企业版才有bitmap索引。标准版不支持bitmap索引和星型转换。

SQL> connect sh/sh
SQL> set autotrace traceonly exp;
SQL> SELECT ch.channel_class, c.cust_city,
2 t.calendar_quarter_desc,
3 SUM(s.amount_sold) sales_amount
4 FROM sales s,times t,customers c,channels ch
5 WHERE s.time_id = t.time_id AND
6 s.cust_id = c.cust_id AND
7 s.channel_id = ch.channel_id AND
8 c.cust_state_province = 'CA' AND
9 ch.channel_desc IN ('Internet','Catalog') AND
10 t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
11 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=786 Card=1 Bytes=73)
1 0 TEMP TABLE TRANSFORMATION
2 1 LOAD AS SELECT OF 'SYS_TEMP_0FD9D6602_F1E23'
3 2 TABLE ACCESS (FULL) OF 'CUSTOMERS' (TABLE) (Cost=332 Card=383 Bytes=9958)
4 1 HASH (GROUP BY) (Cost=455 Card=1 Bytes=73)
5 4 HASH JOIN (Cost=454 Card=1 Bytes=73)
6 5 HASH JOIN (Cost=450 Card=2 Bytes=104)
7 6 HASH JOIN (Cost=448 Card=32 Bytes=1184)
8 7 TABLE ACCESS (FULL) OF 'TIMES' (TABLE) (Cost=15 Card=183 Bytes=2928)
9 7 PARTITION RANGE (SUBQUERY) (Cost=432 Card=254 Bytes=5334)
10 9 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' (TABLE) (Cost=432 Card=254 Bytes=5334)
11 10 BITMAP CONVERSION (TO ROWIDS)
12 11 BITMAP AND
13 12 BITMAP MERGE
14 13 BITMAP KEY ITERATION
15 14 BUFFER (SORT)
16 15 TABLE ACCESS (FULL) OF 'CHANNELS' (TABLE) (Cost=3 Card=2 Bytes=42)
17 14 BITMAP INDEX (RANGE SCAN) OF 'SALES_CHANNEL_BIX' (INDEX (BITMAP))
18 12 BITMAP MERGE
19 18 BITMAP KEY ITERATION
20 19 BUFFER (SORT)
21 20 TABLE ACCESS (FULL) OF 'TIMES' (TABLE) (Cost=15 Card=183 Bytes=2928)
22 19 BITMAP INDEX (RANGE SCAN) OF 'SALES_TIME_BIX' (INDEX (BITMAP))
23 12 BITMAP MERGE
24 23 BITMAP KEY ITERATION
25 24 BUFFER (SORT)
26 25 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_F1E23' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=13)
27 24 BITMAP INDEX (RANGE SCAN) OF 'SALES_CUST_BIX' (INDEX (BITMAP))
28 6 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_F1E23'(TABLE (TEMP)) (Cost=2 Card=383 Bytes=5745)
29 5 TABLE ACCESS (FULL) OF 'CHANNELS' (TABLE) (Cost=3 Card=2 Bytes=42)

这就是一个星型转换的例子。对于这个例子来说，数据库会进行两个阶段：
第一个阶段，数据库使用sales表的外键列的bitmap索引来从sales表里标识并检索出所有必须的数据行，得到一个结果集。会使用如下的SQL来进行改写：
SELECT s.amount_sold
FROM sales s
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'));
这是算法中的转换步骤，因为原来的星型查询已经转换为这样一个子查询的表现形式。在这个星型查询中，先扫描times表，获得符合1999-Q1和1999-Q2的time_id。然后根据这些time_id，再使用sales表上的time_id上的bitmap索引来找到事实表里对应到1999-Q1的数据行集。这个数据行集以bitmap里的bit位来表现，也就是1和0的组合，这些1和0就对应到事实表里符合该条件的、在该数据行集里的rowid。执行计划里的BITMAP KEY ITERATION步骤就体现了这个过程。然后使用类似的方法获得事实表里对应到1999-Q2的数据行集。然后采用OR操作符，将Q1的结果集与Q2的结果集进行合并，也就是BITMAP MERGE步骤。

对于customer维度和product维度，也会采用类似的额外的方法获得数据行集。

在该查询汇总，我们得到三个以bitmap所展现的数据行集。

每个bitmap对应到一个单独的维度表，每个bitmap表现了事实表里符合单个维度的限制。这三个bitmap通过使用bitmap AND操作组合成一个bitmap。
最终的bitmap就表现了事实表里符合所有条件的行集。这也就是最终的rowid结果集，然后通过该bitmap得到最终的结果。注意，在这个过程中，并没有实际访问过事实表，所有这些操作都是在维度表和bitmap索引上实现的。由于bitmap索引对数据进行了压缩，因此基于结果集的操作特别高效。

该查询的第二个阶段，就是将事实表所返回的数据行与维度表关联。在与维度表关联时，也就是在扫描维度表的时候，CBO会自动判断，如果维度表很小，则全表扫描，如果维度表很大，CBO可能会创建临时表，用来包含这两个阶段所要用到的记录。比如这里再扫描customers维度表时，就把customers表里的记录取出，并放到了一个临时表里。在关联维度时，通常会采用hash join的方式。

这基本就是星型转换的过程。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8558093/viewspace-1022279/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8558093/viewspace-1022279/