【DB笔试面试591】在Oracle中，什么是星型连接（Star Join）？
16lz
2021-04-16
♣题目 部分
在Oracle中，什么是星型连接（Star Join）？
♣答案部分
星型连接（Star Join）通常用于数据仓库类型的应用，它是一种单个事实表（Fact Table）和多个维度表（Dimension Table）之间的连接。星型连接的各维度表之间没有直接的关联条件，其事实表和各维度表之间是基于事实表的外键列和对应维度表的主键列之间的连接，并且通常在事实表的外键列上还会存在对应的位图索引。星型转换可以有效改善大的事实表与多个具有良好选择率的维度表间连接的查询，从而有效避免了全表扫描的性能窘境。
星型转换由初始化参数STAR_TRANSFORMATION_ENABLED控制，该参数可以有三种选项：
① TRUE：CBO优化器自动识别语句中的事实表和约束维度表并进行星型转换。CBO优化器需要确定转换后的执行计划成本要低于不转换的执行计划.如果利用物化的临时表性能更高，那么CBO优化器还会尝试利用物化的临时表。
② FALSE：优化器不会考虑星型转换，为参数STAR_TRANSFORMATION_ENABLED的默认值。
③ TEMP_DISABLE：当一个维度表超过100个块时，如果简单地设置STAR_TRANSFORMATION_ENABLED为TRUE来启用星型变换，那么会话会创建一个内存中的全局临时表（Global Temporary Table）来保存已过滤的维度数据，这在过去会造成很多问题。这里所说的100个块其实是由隐含参数“_temp_tran_block_threshold”（number of blocks for a dimension before we temp transform）来控制的。此外，隐含参数“_temp_tran_cache”（determines if temp table is created with cache option，默认为TRUE）决定了这类临时表是否被缓存住。为了避免创建全局临时表可能带来的问题，就可以用到TEMP_DISABLE这个禁用临时表的选项，让优化器不再考虑使用物化的临时表。
参数STAR_TRANSFORMATION_ENABLED的默认值为FALSE，因为星型转换适用的场景是数据仓库环境中具有星型模型的模式，而且需要事实表的各个连接列上均有良好的索引时才能发挥其优势。如果能确定以上因素，那么就可以使用星型转换。
下面给出一个示例：
1SYS@orclasm > show parameter star_transformation_enabled 2 3NAME TYPE VALUE 4------------------------------------ ----------- ------------------------------ 5star_transformation_enabled string FALSE 6 7SYS@orclasm > conn sh/sh 8Connected. 9SH@orclasm > set autotr trace exp;10SH@orclasm > SELECT CH.CHANNEL_CLASS,11 2 C.CUST_CITY,12 3 T.CALENDAR_QUARTER_DESC,13 4 SUM(S.AMOUNT_SOLD) SALES_AMOUNT14 5 FROM SALES S, TIMES T, CUSTOMERS C, CHANNELS CH15 6 WHERE S.TIME_ID = T.TIME_ID16 7 AND S.CUST_ID = C.CUST_ID17 8 AND S.CHANNEL_ID = CH.CHANNEL_ID18 9 AND C.CUST_STATE_PROVINCE = 'CA'19 10 AND CH.CHANNEL_DESC IN ('Internet', 'Catalog')20 11 AND T.CALENDAR_QUARTER_DESC IN ('1999-Q1', '1999-Q2')21 12 GROUP BY CH.CHANNEL_CLASS, C.CUST_CITY, T.CALENDAR_QUARTER_DESC;2223Execution Plan24----------------------------------------------------------2526Plan hash value: 5934207982728-------------------------------------------------------------------------------------------------------------29| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |30-------------------------------------------------------------------------------------------------------------31| 0 | SELECT STATEMENT | | 572 | 48048 | 964 (3)| 00:00:12 | | |32| 1 | HASH GROUP BY | | 572 | 48048 | 964 (3)| 00:00:12 | | |33|* 2 | HASH JOIN | | 3116 | 255K| 963 (3)| 00:00:12 | | |34|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |35|* 4 | HASH JOIN | | 6231 | 383K| 960 (3)| 00:00:12 | | |36| 5 | PART JOIN FILTER CREATE | :BF0000 | 183 | 2928 | 18 (0)| 00:00:01 | | |37|* 6 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |38|* 7 | HASH JOIN | | 49822 | 2286K| 941 (3)| 00:00:12 | | |39|* 8 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |40| 9 | PARTITION RANGE JOIN-FILTER| | 918K| 18M| 530 (3)| 00:00:07 |:BF0000|:BF0000|41| 10 | TABLE ACCESS FULL | SALES | 918K| 18M| 530 (3)| 00:00:07 |:BF0000|:BF0000|42-------------------------------------------------------------------------------------------------------------4344Predicate Information (identified by operation id):45---------------------------------------------------4647 2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")48 3 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')49 4 - access("S"."TIME_ID"="T"."TIME_ID")50 6 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')51 7 - access("S"."CUST_ID"="C"."CUST_ID")52 8 - filter("C"."CUST_STATE_PROVINCE"='CA')show parameter star_transformation_enabled
2
3NAME TYPE VALUE
4------------------------------------ ----------- ------------------------------
5star_transformation_enabled string FALSE
6
7SYS@orclasm > conn sh/sh
8Connected.
9SH@orclasm > set autotr trace exp;
10SH@orclasm > SELECT CH.CHANNEL_CLASS,
11 2 C.CUST_CITY,
12 3 T.CALENDAR_QUARTER_DESC,
13 4 SUM(S.AMOUNT_SOLD) SALES_AMOUNT
14 5 FROM SALES S, TIMES T, CUSTOMERS C, CHANNELS CH
15 6 WHERE S.TIME_ID = T.TIME_ID
16 7 AND S.CUST_ID = C.CUST_ID
17 8 AND S.CHANNEL_ID = CH.CHANNEL_ID
18 9 AND C.CUST_STATE_PROVINCE = 'CA'
19 10 AND CH.CHANNEL_DESC IN ('Internet', 'Catalog')
20 11 AND T.CALENDAR_QUARTER_DESC IN ('1999-Q1', '1999-Q2')
21 12 GROUP BY CH.CHANNEL_CLASS, C.CUST_CITY, T.CALENDAR_QUARTER_DESC;
22
23Execution Plan
24----------------------------------------------------------
25
26Plan hash value: 593420798
27
28-------------------------------------------------------------------------------------------------------------
29| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
30-------------------------------------------------------------------------------------------------------------
31| 0 | SELECT STATEMENT | | 572 | 48048 | 964 (3)| 00:00:12 | | |
32| 1 | HASH GROUP BY | | 572 | 48048 | 964 (3)| 00:00:12 | | |
33|* 2 | HASH JOIN | | 3116 | 255K| 963 (3)| 00:00:12 | | |
34|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
35|* 4 | HASH JOIN | | 6231 | 383K| 960 (3)| 00:00:12 | | |
36| 5 | PART JOIN FILTER CREATE | :BF0000 | 183 | 2928 | 18 (0)| 00:00:01 | | |
37|* 6 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
38|* 7 | HASH JOIN | | 49822 | 2286K| 941 (3)| 00:00:12 | | |
39|* 8 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
40| 9 | PARTITION RANGE JOIN-FILTER| | 918K| 18M| 530 (3)| 00:00:07 |:BF0000|:BF0000|
41| 10 | TABLE ACCESS FULL | SALES | 918K| 18M| 530 (3)| 00:00:07 |:BF0000|:BF0000|
42-------------------------------------------------------------------------------------------------------------
43
44Predicate Information (identified by operation id):
45---------------------------------------------------
46
47 2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
48 3 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
49 4 - access("S"."TIME_ID"="T"."TIME_ID")
50 6 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')
51 7 - access("S"."CUST_ID"="C"."CUST_ID")
52 8 - filter("C"."CUST_STATE_PROVINCE"='CA')
发现上面的执行计划与三个表连接时都是全表扫描，下面打开星形转换后再执行同样的SQL语句：
1SYS@orclasm > alter system set star_transformation_enabled=true; 2 3System altered. 4 5SH@orclasm > SH@orclasm > set autotr trace exp; 6SH@orclasm > SELECT CH.CHANNEL_CLASS, 7 2 C.CUST_CITY, 8 3 T.CALENDAR_QUARTER_DESC, 9 4 SUM(S.AMOUNT_SOLD) SALES_AMOUNT10 FROM SALES S, TIMES T, CUSTOMERS C, CHANNELS CH11 WHERE S.TIME_ID = T.TIME_ID12 AND S.CUST_ID = C.CUST_ID13 AND S.CHANNEL_ID = CH.CHANNEL_ID14 AND C.CUST_STATE_PROVINCE = 'CA'15 AND CH.CHANNEL_DESC IN ('Internet', 'Catalog')16 AND T.CALENDAR_QUARTER_DESC IN ('1999-Q1', '1999-Q2')17 GROUP BY CH.CHANNEL_CLASS, C.CUST_CITY, T.CALENDAR_QUARTER_DESC;1819Execution Plan20----------------------------------------------------------2122Plan hash value: 4788865102324----------------------------------------------------------------------------------------------------------------------------------25| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |26----------------------------------------------------------------------------------------------------------------------------------27| 0 | SELECT STATEMENT | | 253 | 20493 | 556 (1)| 00:00:07 | | |28| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |29| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660D_466CBF7 | | | | | | |30|* 3 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |31| 4 | HASH GROUP BY | | 253 | 20493 | 150 (2)| 00:00:02 | | |32|* 5 | HASH JOIN | | 253 | 20493 | 149 (2)| 00:00:02 | | |33|* 6 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |34|* 7 | HASH JOIN | | 253 | 15180 | 146 (2)| 00:00:02 | | |35|* 8 | HASH JOIN | | 253 | 11385 | 143 (1)| 00:00:02 | | |36|* 9 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |37| 10 | VIEW | VW_ST_62EEF96F | 254 | 7366 | 125 (1)| 00:00:02 | | |38| 11 | NESTED LOOPS | | 254 | 14478 | 101 (0)| 00:00:02 | | |39| 12 | PARTITION RANGE SUBQUERY | | 254 | 7117 | 55 (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|40| 13 | BITMAP CONVERSION TO ROWIDS| | 254 | 7117 | 55 (2)| 00:00:01 | | |41| 14 | BITMAP AND | | | | | | | |42| 15 | BITMAP MERGE | | | | | | | |43| 16 | BITMAP KEY ITERATION | | | | | | | |44| 17 | BUFFER SORT | | | | | | | |45|* 18 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | |46|* 19 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|47| 20 | BITMAP MERGE | | | | | | | |48| 21 | BITMAP KEY ITERATION | | | | | | | |49| 22 | BUFFER SORT | | | | | | | |50|* 23 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |51|* 24 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|52| 25 | BITMAP MERGE | | | | | | | |53| 26 | BITMAP KEY ITERATION | | | | | | | |54| 27 | BUFFER SORT | | | | | | | |55| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_466CBF7 | 383 | 1915 | 2 (0)| 00:00:01 | | |56|* 29 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | | | | |KEY(SQ)|KEY(SQ)|57| 30 | TABLE ACCESS BY USER ROWID | SALES | 1 | 29 | 70 (0)| 00:00:01 | ROWID | ROWID |58| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_466CBF7 | 383 | 5745 | 2 (0)| 00:00:01 | | |59----------------------------------------------------------------------------------------------------------------------------------6061Predicate Information (identified by operation id):62---------------------------------------------------6364 3 - filter("C"."CUST_STATE_PROVINCE"='CA')65 5 - access("ITEM_1"="CH"."CHANNEL_ID")66 6 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')67 7 - access("ITEM_2"="C0")68 8 - access("ITEM_3"="T"."TIME_ID")69 9 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')70 18 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')71 19 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")72 23 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')73 24 - access("S"."TIME_ID"="T"."TIME_ID")74 29 - access("S"."CUST_ID"="C0")7576Note77-----78 - star transformation used for this statementalter system set star_transformation_enabled=true;
2
3System altered.
4
5SH@orclasm > SH@orclasm > set autotr trace exp;
6SH@orclasm > SELECT CH.CHANNEL_CLASS,
7 2 C.CUST_CITY,
8 3 T.CALENDAR_QUARTER_DESC,
9 4 SUM(S.AMOUNT_SOLD) SALES_AMOUNT
10 FROM SALES S, TIMES T, CUSTOMERS C, CHANNELS CH
11 WHERE S.TIME_ID = T.TIME_ID
12 AND S.CUST_ID = C.CUST_ID
13 AND S.CHANNEL_ID = CH.CHANNEL_ID
14 AND C.CUST_STATE_PROVINCE = 'CA'
15 AND CH.CHANNEL_DESC IN ('Internet', 'Catalog')
16 AND T.CALENDAR_QUARTER_DESC IN ('1999-Q1', '1999-Q2')
17 GROUP BY CH.CHANNEL_CLASS, C.CUST_CITY, T.CALENDAR_QUARTER_DESC;
18
19Execution Plan
20----------------------------------------------------------
21
22Plan hash value: 478886510
23
24----------------------------------------------------------------------------------------------------------------------------------
25| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
26----------------------------------------------------------------------------------------------------------------------------------
27| 0 | SELECT STATEMENT | | 253 | 20493 | 556 (1)| 00:00:07 | | |
28| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
29| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660D_466CBF7 | | | | | | |
30|* 3 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
31| 4 | HASH GROUP BY | | 253 | 20493 | 150 (2)| 00:00:02 | | |
32|* 5 | HASH JOIN | | 253 | 20493 | 149 (2)| 00:00:02 | | |
33|* 6 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
34|* 7 | HASH JOIN | | 253 | 15180 | 146 (2)| 00:00:02 | | |
35|* 8 | HASH JOIN | | 253 | 11385 | 143 (1)| 00:00:02 | | |
36|* 9 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
37| 10 | VIEW | VW_ST_62EEF96F | 254 | 7366 | 125 (1)| 00:00:02 | | |
38| 11 | NESTED LOOPS | | 254 | 14478 | 101 (0)| 00:00:02 | | |
39| 12 | PARTITION RANGE SUBQUERY | | 254 | 7117 | 55 (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|
40| 13 | BITMAP CONVERSION TO ROWIDS| | 254 | 7117 | 55 (2)| 00:00:01 | | |
41| 14 | BITMAP AND | | | | | | | |
42| 15 | BITMAP MERGE | | | | | | | |
43| 16 | BITMAP KEY ITERATION | | | | | | | |
44| 17 | BUFFER SORT | | | | | | | |
45|* 18 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | |
46|* 19 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|
47| 20 | BITMAP MERGE | | | | | | | |
48| 21 | BITMAP KEY ITERATION | | | | | | | |
49| 22 | BUFFER SORT | | | | | | | |
50|* 23 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
51|* 24 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|
52| 25 | BITMAP MERGE | | | | | | | |
53| 26 | BITMAP KEY ITERATION | | | | | | | |
54| 27 | BUFFER SORT | | | | | | | |
55| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_466CBF7 | 383 | 1915 | 2 (0)| 00:00:01 | | |
56|* 29 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | | | | |KEY(SQ)|KEY(SQ)|
57| 30 | TABLE ACCESS BY USER ROWID | SALES | 1 | 29 | 70 (0)| 00:00:01 | ROWID | ROWID |
58| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_466CBF7 | 383 | 5745 | 2 (0)| 00:00:01 | | |
59----------------------------------------------------------------------------------------------------------------------------------
60
61Predicate Information (identified by operation id):
62---------------------------------------------------
63
64 3 - filter("C"."CUST_STATE_PROVINCE"='CA')
65 5 - access("ITEM_1"="CH"."CHANNEL_ID")
66 6 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
67 7 - access("ITEM_2"="C0")
68 8 - access("ITEM_3"="T"."TIME_ID")
69 9 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')
70 18 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
71 19 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
72 23 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')
73 24 - access("S"."TIME_ID"="T"."TIME_ID")
74 29 - access("S"."CUST_ID"="C0")
75
76Note
77-----
78 - star transformation used for this statement
其实经过星形转换后，Oracle对上面的SQL语句进行了查询重写。以上SQL需要在相关的事实表上有位图索引，否则依然不能使用星型转换：
1create bitmap index cust_id_ind on sh.sales(cust_id) local;2create bitmap index time_id_ind on sh.sales(time_id) local;3create bitmap index channel_id_ind on sh.sales(channel_id) local;create bitmap index cust_id_ind on sh.sales(cust_id) local;
2create bitmap index time_id_ind on sh.sales(time_id) local;
3create bitmap index channel_id_ind on sh.sales(channel_id) local;
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧
更多相关文章
- MySQL之left join表查询中发生字符集转换导致表索引失效
- 【DB笔试面试618】在Oracle中，“OR扩展”可以有查询转换吗？
- 【DB笔试面试619】在Oracle中，举例说明“连接因式分解（Join factor
- MySQL中日期转换 FROM_UNIXTIME和UNIX_TIMESTAMP
- office2010 excel简繁体转换功能异常
- javascript数据类型转换
- Python3 常见数据类型的转换
- MySQL 隐式类型转换
- MySQL和Oracle中的隐式转换