C/C++教程
【DB笔试面试591】在Oracle中，什么是星型连接（Star Join）？
本文主要是介绍【DB笔试面试591】在Oracle中，什么是星型连接（Star Join）？，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
♣题目 部分
在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
3NAME TYPE VALUE
4------------------------------------ ----------- ------------------------------
5star_transformation_enabled string FALSE
7SYS@orclasm > conn sh/sh
8Connected.
9SH@orclasm > set autotr trace exp;
10SH@orclasm > 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;
23Execution Plan
24----------------------------------------------------------
26Plan hash value: 593420798
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-------------------------------------------------------------------------------------------------------------
44Predicate Information (identified by operation id):
45---------------------------------------------------
2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
- filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
- access("S"."TIME_ID"="T"."TIME_ID")
- filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')
- access("S"."CUST_ID"="C"."CUST_ID")
- 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;
3System altered.
5SH@orclasm > SH@orclasm > set autotr trace exp;
6SH@orclasm > 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;
19Execution Plan
20----------------------------------------------------------
22Plan hash value: 478886510
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----------------------------------------------------------------------------------------------------------------------------------
61Predicate Information (identified by operation id):
62---------------------------------------------------
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
- access("ITEM_1"="CH"."CHANNEL_ID")
- filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
- access("ITEM_2"="C0")
- access("ITEM_3"="T"."TIME_ID")
- filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')
- filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
- access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
- filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DESC"='1999-Q2')
- access("S"."TIME_ID"="T"."TIME_ID")
- access("S"."CUST_ID"="C0")
76Note
77-----
- 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，学习最实用的数据库技术。
喜欢就点击“好看”吧
这篇关于【DB笔试面试591】在Oracle中，什么是星型连接（Star Join）？的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
增量更新怎么做？-icode9专业技术文章分享11-23
-
压缩包加密方案有哪些？-icode9专业技术文章分享11-23
-
用shell怎么写一个开机时自动同步远程仓库的代码？-icode9专业技术文章分享11-23
-
webman可以同步自己的仓库吗？-icode9专业技术文章分享11-23
-
在 Webman 中怎么判断是否有某命令进程正在运行？-icode9专业技术文章分享11-23
-
如何重置new Swiper?-icode9专业技术文章分享11-23
-
oss直传有什么好处？-icode9专业技术文章分享11-23
-
如何将oss直传封装成一个组件在其他页面调用时都可以使用？-icode9专业技术文章分享11-23
-
怎么使用laravel 11在代码里获取路由列表？-icode9专业技术文章分享11-23
-
怎么实现ansible playbook 备份代码中命名包含时间戳功能？-icode9专业技术文章分享11-22
-
ansible 的archive 参数是什么意思？-icode9专业技术文章分享11-22
-
ansible 中怎么只用archive 排除某个目录？-icode9专业技术文章分享11-22
-
exclude_path参数是什么作用？-icode9专业技术文章分享11-22
-
微信开放平台第三方平台什么时候调用数据预拉取和数据周期性更新接口？-icode9专业技术文章分享11-22
-
uniapp 实现聊天消息会话的列表功能怎么实现？-icode9专业技术文章分享11-22
栏目导航
