Oracle Star Schema简析
星型转换是一个非常强大的优化技术，它是通过对原来的SQL语句的隐式的改写来实现的，它能够很大程度减少I/O. 终端用户并不需要知道有关星型转换的任何细节。数据库优化器会在合适的时候进行星型转换。要获得星型转换的最大性能，需要遵循以下3个基本的条件：
1，事实表上的维度列上要有外键
2，事实表的每个外键上都有BITMAP索引。
3，star_transformation_enabled=true。 系统默认是false. 它有三个取值：（TRUE, FALSE, TEMP_DISABLE）.其中TEMP_DISABLE表示不允许用临时表来存放第一次扫描的结果集。
星型查询中，维度表会被扫描两次，如果维度表很大，性能会很差，所以需要一个临时表来存放第一次扫描的维度表集合。
如果能够满足这三个条件,则查询会使用star transformation,而这是提高基于事实表的查询效率的主要的技术。
数据库进行星型查询时，会使用两个基本的阶段：
第一个阶段从事实表(或者说结果集)里获取所有必要的记录行。由于这是通过bitmap索引来检索数据，因此比较高效。
第二个阶段将该结果集与维度表进行关联。这叫做semi-join(也就是exists和in写法)。
注意：只有oracle企业版才有bitmap索引。标准版不支持bitmap索引和星型转换。
下面的SQL语句执行计划可以很好的说明进行"星型查询"的条件：
SQL> show parameter star_transformation_enabled
NAME TYPE VALUE
------------------------------------ ----------- -------------
star_transformation_enabled string FALSE
SQL> conn sh/sh
Connected.
SQL>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')
11 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 1612666291
----------------------------------------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT | | 572 | 48048 | 793 (8)|
00:00:10 | | |
| 1 | HASH GROUP BY | | 572 | 48048 | 793 (8)|
00:00:10 | | |
|* 2 | HASH JOIN | | 3116 | 255K| 791 (8)|
00:00:10 | | |
|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)|
00:00:01 | | |
|* 4 | HASH JOIN | | 6231 | 383K| 788 (8)|
00:00:10 | | |
|* 5 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 15 (0)|
00:00:01 | | |
|* 6 | HASH JOIN | | 49822 | 2286K| 771 (8)|
00:00:10 | | |
|* 7 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 332 (2)|
00:00:04 | | |
| 8 | PARTITION RANGE SUBQUERY| | 918K| 18M| 426 (9)|
00:00:06 |KEY(SQ)|KEY(SQ)|
| 9 | TABLE ACCESS FULL | SALES | 918K| 18M| 426 (9)|
00:00:06 |KEY(SQ)|KEY(SQ)|
---------------------------------------------------------------
--------------------------
www.2cto.com
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
3 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
4 - access("S"."TIME_ID"="T"."TIME_ID")
5 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')
6 - access("S"."CUST_ID"="C"."CUST_ID")
7 - filter("C"."CUST_STATE_PROVINCE"='CA')
可以看出上面的SQL语句没有用到star transfermation, 因为star_transformation_enabled=false。执行计划只是普通的JOIN操作。
把star_transformation_enabled=TRUE，让他用star transfermation技术执行SQL语句。
SQL> alter system set star_transformation_enabled=true;
System altered.
SQL> col PLAN_PLUS_EXP format a120
SQL> set autotrace traceonly exp;
SQL> col PLAN_PLUS_EXP format a120
SQL> set autotrace traceonly exp;
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')
11 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 580301883
----------------------------------------------------------
----------------------------------------------------
| Id | Operation | Name | Row
s | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------
----------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 73 | 786 (2)| 00:00:10 | | |
| 1 | TEMP TABLE TRANSFORMATION | |
| | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_7E653 |
| | | | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 3
83 | 9958 | 332 (2)| 00:00:04 | | |
| 4 | HASH GROUP BY | |
1 | 73 | 455 (2)| 00:00:06 | | |
|* 5 | HASH JOIN | |
1 | 73 | 454 (2)| 00:00:06 | | |
|* 6 | HASH JOIN | |
2 | 104 | 450 (2)| 00:00:06 | | |
|* 7 | HASH JOIN | |
32 | 1184 | 448 (2)| 00:00:06 | | |
|* 8 | TABLE ACCESS FULL | TIMES | 1
83 | 2928 | 15 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE SUBQUERY | | 2
54 | 5334 | 432 (2)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2
54 | 5334 | 432 (2)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 11 | BITMAP CONVERSION TO ROWIDS | |
| | | | | |
| 12 | BITMAP AND | |
| | | | | |
| 13 | BITMAP MERGE | |
| | | | | |
| 14 | BITMAP KEY ITERATION | |
| | | | | |
| 15 | BUFFER SORT | |
| | | | | |
|* 16 | TABLE ACCESS FULL | CHANNELS |
2 | 42 | 3 (0)| 00:00:01 | | |
|* 17 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX |
| | | |KEY(SQ)|KEY(SQ)|
| 18 | BITMAP MERGE | |
| | | | | |
| 19 | BITMAP KEY ITERATION | |
| | | | | |
| 20 | BUFFER SORT | |
| | | | | |
|* 21 | TABLE ACCESS FULL | TIMES | 1
83 | 2928 | 15 (0)| 00:00:01 | | |
|* 22 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX |
| | | |KEY(SQ)|KEY(SQ)|
| 23 | BITMAP MERGE | |
| | | | | |
| 24 | BITMAP KEY ITERATION | |
| | | | | |
| 25 | BUFFER SORT | |
| | | | | |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_7E653 |
1 | 13 | 2 (0)| 00:00:01 | | |
|* 27 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX |
| | | |KEY(SQ)|KEY(SQ)|
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_7E653 | 3
83 | 5745 | 2 (0)| 00:00:01 | | |
|* 29 | TABLE ACCESS FULL | CHANNELS |
2 | 42 | 3 (0)| 00:00:01 | | |
----------------------------------------------------
www.2cto.com
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
5 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
6 - access("S"."CUST_ID"="C0")
7 - access("S"."TIME_ID"="T"."TIME_ID")
8 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')
16 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
17 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
21 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')
22 - access("S"."TIME_ID"="T"."TIME_ID")
27 - access("S"."CUST_ID"="C0")
29 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
从上面执行计划可以看出，此SQL语句做了star transformation. 走了BITMAP索引，例如：BITMAP MERGE ,BITMAP AND等等。
补充：
星形模式的基本特点是由多个维表(Dimension Table)和事实表(Fact Table)组成。维表代表了相关的分析维度，事实表则是这些不同维度上的计算结果。星形模式存储了系统在不同维度上的预处理结果，因此进行多维分析时速度很快，但如果分析范围超出了预定义的维度，那么增加维度将是很困难的事情。
面试题：
给出在STAR SCHEMA中的两种表及它们分别含有的数据
解答：Fact tables 和dimension tables. fact table包含大量的主要的信息而dimension tables 存放对fact table 某些属性描述的信息
FACT Table上需要建立何种索引?
解答：位图索引 (bitmap index)
2942
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
