Oracle 12CR2查询转换之星型转换的方法
发表于：2025-01-24 作者：千家信息网编辑
千家信息网最后更新 2025年01月24日，这篇文章主要为大家展示了"Oracle 12CR2查询转换之星型转换的方法"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"Oracle 12CR2查询转换之
千家信息网最后更新 2025年01月24日Oracle 12CR2查询转换之星型转换的方法
这篇文章主要为大家展示了"Oracle 12CR2查询转换之星型转换的方法"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"Oracle 12CR2查询转换之星型转换的方法"这篇文章吧。
星型转换是一种优化转换它用来避免对星型方案中的事实表进行全表扫描。一个星型方案将数据分成事实与维度表。事实是对一个事件比如销售的测量通常是数字。维度是标识事实的分类，比如日期，位置与产品。一个事实表有一个由方案中维度表主键所组成的复合键。维度表实际上充当查找或引用表能让你选择你查询所要请求的值。
星型转换的目的
在连接事实表与维度表时，星型转换可能避免对事实表执行完全扫描。星型转换通过只获取连接到约束维度行记录的相关事实行记录来提高性能。在有些情况下，查询已经在维度表的其它列上有限制性过滤了。过滤组合可以大大减少数据库从事实表中要处理的数据集大小。
星型转换的工作原理
星型转换增加了子查询谓词，叫作位图半连接谓词，关联到约束维度表。当在实际连接列上存在索引时优化器执行转换。通过驱动位图and和or来操作由子查询所提供的键值，数据库只需要从事实表中检索相关行记录。如果维度表上的谓词过滤掉了大量数据，那么星型转换比对事实表完全扫描更有效。
在数据库从事实表中检索相关行记录之后，数据库可能需要使用原始谓词连接这些行记录回维度表。当以下条件满足时数据库可以消除连接回维度表:
.维度表上的所有谓词是半连接子查询谓词的一部分
.从子查询中所选择的列具有唯一性
.维度列不在select列，group by子句中等等
控制星型转换
star_transformation_enabled参数控制着星型转换。这个参数有以下参数值:
.true
优化器通过自动识别事实与约束维度表来执行星型转换。只有转换后的执行计划成本比原始执行计划成本低时优化器才执行星型转换。当物化提高性能时优化器也会尝试临时表转换。
.false(缺省值)
优化器不执行星型转换
.temp_disable
这个值与true相同，只是优化器不会尝试临时表转换
星型转换:应用场景
下面的查询找出1999年Q1和Q2季度在California的所有城市中的总的网络销售额:
SELECT c.cust_city,t.calendar_quarter_desc,SUM(s.amount_sold) sales_amountFROM sales s,times t,customers c,channels chWHERE s.time_id = t.time_idAND s.cust_id = c.cust_idAND s.channel_id = ch.channel_idAND c.cust_state_province = 'CA'AND ch.channel_desc = 'Internet'AND t.calendar_quarter_desc IN ('1999-01','1999-02')GROUP BY c.cust_city, t.calendar_quarter_desc;示例输出如下:
SQL> show parameter star_transformation_enabledNAME TYPE VALUE------------------------------------ ----------- ------------------------------star_transformation_enabled string FALSESQL> SELECT c.cust_city, 2 t.calendar_quarter_desc, 3 SUM(s.amount_sold) sales_amount 4 FROM sales s, 5 times t, 6 customers c, 7 channels ch 8 WHERE s.time_id = t.time_id 9 AND s.cust_id = c.cust_id 10 AND s.channel_id = ch.channel_id 11 AND c.cust_state_province = 'CA' 12 AND ch.channel_desc = 'Internet' 13 AND t.calendar_quarter_desc IN ('1999-01','1999-02') 14 GROUP BY c.cust_city, t.calendar_quarter_desc;CUST_CITY CALENDA SALES_AMOUNT------------------------------ ------- ------------Montara 1999-02 1618.01Pala 1999-01 3263.93Cloverdale 1999-01 52.64Cloverdale 1999-02 266.28San Francisco 1999-01 3058.27San Mateo 1999-01 8754.59Los Angeles 1999-01 1886.19San Mateo 1999-02 21399.42Pala 1999-02 936.62El Sobrante 1999-02 3744.03El Sobrante 1999-01 5392.34Quartzhill 1999-01 987.3Legrand 1999-01 26.32Pescadero 1999-01 26.32Arbuckle 1999-02 241.2Quartzhill 1999-02 412.83Montara 1999-01 289.07Arbuckle 1999-01 270.08San Francisco 1999-02 11257Los Angeles 1999-02 2128.59Pescadero 1999-02 298.44Legrand 1999-02 18.6622 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));SQL_ID a069wzk60bbqd, child number 1-------------------------------------SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)sales_amount FROM sales s, times t, customers c, channels ch WHEREs.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 ='Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUPBY c.cust_city, t.calendar_quarter_descPlan hash value: 1865285285---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 957 (100)| | | | 22 |00:00:00.18 | 1812 | | | || 1 | HASH GROUP BY | | 1 | 22 | 1672 | 957 (2)| 00:00:01 | | | 22 |00:00:00.18 | 1812 | 1022K| 1022K| 1382K (0)||* 2 | HASH JOIN | | 1 | 138 | 10488 | 956 (2)| 00:00:01 | | | 964 |00:00:00.14 | 1812 | 1538K| 1538K| 1588K (0)|| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 183 | 2928 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | | | ||* 4 | TABLE ACCESS FULL | TIMES | 1 | 183 | 2928 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | | | ||* 5 | HASH JOIN | | 1 | 964 | 57840 | 938 (2)| 00:00:01 | | | 964 |00:00:00.11 | 1747 | 1448K| 1448K| 1521K (0)|| 6 | MERGE JOIN CARTESIAN | | 1 | 3341 | 127K| 426 (1)| 00:00:01 | | | 3341 |00:00:00.02 | 1531 | | | ||* 7 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 13 | 3 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | | | || 8 | BUFFER SORT | | 1 | 3341 | 86866 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.02 | 1522 | 178K| 178K| 158K (0)||* 9 | TABLE ACCESS FULL | CUSTOMERS | 1 | 3341 | 86866 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.01 | 1522 | | | || 10 | PARTITION RANGE JOIN-FILTER| | 1 | 819K| 16M| 510 (2)| 00:00:01 |:BF0000|:BF0000| 118K|00:00:00.02 | 216 | | | || 11 | TABLE ACCESS FULL | SALES | 2 | 819K| 16M| 510 (2)| 00:00:01 |:BF0000|:BF0000| 118K|00:00:00.02 | 216 | | | |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------从上面的执行中可以看到，在没有使用星型转换时id=11，对表sales执行的是全表扫描。在这个例子中，sales表是事实表，并且其它的表是维度表。sales表对于每一个销售的产品都有一行记录，因此它可以包含上十亿行销售记录。然而，只有少量产品在指定的季度通过网络销售到了California的客户手中。
使用星形转换
SQL> alter session set star_transformation_enabled='true';Session altered.SQL> SELECT c.cust_city, 2 t.calendar_quarter_desc, 3 SUM(s.amount_sold) sales_amount 4 FROM sales s, 5 times t, 6 customers c, 7 channels ch 8 WHERE s.time_id = t.time_id 9 AND s.cust_id = c.cust_id 10 AND s.channel_id = ch.channel_id 11 AND c.cust_state_province = 'CA' 12 AND ch.channel_desc = 'Internet' 13 AND t.calendar_quarter_desc IN ('1999-01','1999-02') 14 GROUP BY c.cust_city, t.calendar_quarter_desc;Montara 1999-02 1618.01Pala 1999-01 3263.93Cloverdale 1999-01 52.64Cloverdale 1999-02 266.28San Francisco 1999-01 3058.27San Mateo 1999-01 8754.59Los Angeles 1999-01 1886.19San Mateo 1999-02 21399.42Pala 1999-02 936.62El Sobrante 1999-02 3744.03El Sobrante 1999-01 5392.34Quartzhill 1999-01 987.3Legrand 1999-01 26.32Pescadero 1999-01 26.32Arbuckle 1999-02 241.2Quartzhill 1999-02 412.83Montara 1999-01 289.07Arbuckle 1999-01 270.08San Francisco 1999-02 11257Los Angeles 1999-02 2128.59Pescadero 1999-02 298.44Legrand 1999-02 18.6622 rows selected.从10053跟踪文件中找到的星型转换后的语句如下:
ST: Query after star xformation:******* UNPARSED QUERY IS *******SELECT /*+ CACHE (T1) */ T1.C1 CUST_CITY, T.CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC, SUM(S.AMOUNT_SOLD) SALES_AMOUNT FROM SH.SALES S, SH.TIMES T, SYS.SYS_TEMP_0FD9D6684_63D6F82 T1 WHERE S.CUST_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER CACHE (T1) */ T1.C0 C0 FROM SYS.SYS_TEMP_0FD9D6684_63D6F82 T1) AND S.CHANNEL_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER */ CH.CHANNEL_ID ITEM_1 FROM SH.CHANNELS CH WHERE CH.CHANNEL_DESC = 'Internet') AND S.TIME_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER */ T.TIME_ID ITEM_1 FROM SH.TIMES T WHERE T.CALENDAR_QUARTER_DESC = '1999-01' OR T.CALENDAR_QUARTER_DESC = '1999-02') AND S.TIME_ID = T.TIME_ID AND S.CUST_ID = T1.C0 AND (T.CALENDAR_QUARTER_DESC = '1999-01' OR T.CALENDAR_QUARTER_DESC = '1999-02') GROUP BY T1.C1, T.CALENDAR_QUARTER_DESC
其执行计划如下:
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));SQL_ID a069wzk60bbqd, child number 3-------------------------------------SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)sales_amount FROM sales s, times t, customers c, channels ch WHEREs.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 ='Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUPBY c.cust_city, t.calendar_quarter_descPlan hash value: 2164696140------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 573 (100)| | | | 22 |00:00:01.41 | 9083 | 96 | 10 | | | || 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 22 |00:00:01.41 | 9083 | 96 | 10 | | | || 2 | LOAD AS SELECT | SYS_TEMP_0FD9D667F_63D6F82 | 1 | | | | | | | 0 |00:00:01.07 | 1539 | 2 | 10 | 1042K| 1042K| ||* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 383 | 9958 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.01 | 1522 | 0 | 0 | | | || 4 | HASH GROUP BY | | 1 | 542 | 30894 | 150 (1)| 00:00:01 | | | 22 |00:00:00.33 | 7538 | 93 | 0 | 1022K| 1022K| 1346K (0)||* 5 | HASH JOIN | | 1 | 1681 | 95817 | 149 (0)| 00:00:01 | | | 964 |00:00:00.31 | 7538 | 93 | 0 | 1572K| 1572K| 1677K (0)|| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667F_63D6F82 | 1 | 383 | 5745 | 2 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 10 | 0 | | | ||* 7 | HASH JOIN | | 1 | 1681 | 70602 | 147 (0)| 00:00:01 | | | 964 |00:00:00.29 | 7520 | 83 | 0 | 1538K| 1538K| 1686K (0)||* 8 | TABLE ACCESS FULL | TIMES | 1 | 183 | 2928 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | || 9 | VIEW | VW_ST_A3F94988 | 1 | 1685 | 43810 | 129 (0)| 00:00:01 | | | 964 |00:00:00.23 | 7455 | 83 | 0 | | | || 10 | NESTED LOOPS | | 1 | 1685 | 96045 | 106 (0)| 00:00:01 | | | 964 |00:00:00.23 | 7455 | 83 | 0 | | | || 11 | PARTITION RANGE SUBQUERY | | 1 | 1684 | 47167 | 52 (0)| 00:00:01 |KEY(SQ)|KEY(SQ)| 964 |00:00:00.22 | 7271 | 83 | 0 | | | || 12 | BITMAP CONVERSION TO ROWIDS| | 2 | 1684 | 47167 | 52 (0)| 00:00:01 | | | 964 |00:00:00.21 | 7204 | 83 | 0 | | | || 13 | BITMAP AND | | 2 | | | | | | | 2 |00:00:00.21 | 7204 | 83 | 0 | | | || 14 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.05 | 15 | 10 | 0 | 1024K| 512K| 4096 (0)|| 15 | BITMAP KEY ITERATION | | 2 | | | | | | | 2 |00:00:00.04 | 15 | 10 | 0 | | | || 16 | BUFFER SORT | | 2 | | | | | | | 2 |00:00:00.01 | 9 | 0 | 0 | 73728 | 73728 | ||* 17 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 13 | 3 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | 0 | 0 | | | ||* 18 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | 2 | | | | |KEY(SQ)|KEY(SQ)| 2 |00:00:00.04 | 6 | 10 | 0 | | | || 19 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.03 | 445 | 10 | 0 | 1024K| 512K|39936 (0)|| 20 | BITMAP KEY ITERATION | | 2 | | | | | | | 181 |00:00:00.02 | 445 | 10 | 0 | | | || 21 | BUFFER SORT | | 2 | | | | | | | 362 |00:00:00.01 | 65 | 0 | 0 | 73728 | 73728 | ||* 22 | TABLE ACCESS FULL | TIMES | 1 | 183 | 2928 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | ||* 23 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | 362 | | | | |KEY(SQ)|KEY(SQ)| 181 |00:00:00.01 | 380 | 10 | 0 | | | || 24 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.14 | 6744 | 63 | 0 | 1024K| 512K|45056 (0)|| 25 | BITMAP KEY ITERATION | | 2 | | | | | | | 403 |00:00:00.14 | 6744 | 63 | 0 | | | || 26 | BUFFER SORT | | 2 | | | | | | | 6682 |00:00:00.01 | 18 | 0 | 0 | 5512K| 964K| 174K (0)|| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667F_63D6F82 | 1 | 383 | 1915 | 2 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 0 | 0 | | | ||* 28 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | 6682 | | | | |KEY(SQ)|KEY(SQ)| 403 |00:00:00.10 | 6726 | 63 | 0 | | | || 29 | TABLE ACCESS BY USER ROWID | SALES | 964 | 1 | 29 | 77 (2)| 00:00:01 | ROWID | ROWID | 964 |00:00:00.01 | 184 | 0 | 0 | | | |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$D5EF7599 2 - SEL$F6045C7B 3 - SEL$F6045C7B / C@SEL$F6045C7B 6 - SEL$D5EF7599 / T1@SEL$9C741BEB 8 - SEL$D5EF7599 / T@SEL$1 9 - SEL$5E9A798F / VW_ST_A3F94988@SEL$D5EF7599 10 - SEL$5E9A798F 12 - SEL$5E9A798F / S@SEL$1 17 - SEL$6EE793B7 / CH@SEL$6EE793B7 22 - SEL$ACF30367 / T@SEL$ACF30367 27 - SEL$E1F9C76C / T1@SEL$E1F9C76C 29 - SEL$5E9A798F / SYS_CP_S@SEL$5E9A798FOutline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') OPT_PARAM('star_transformation_enabled' 'true') ALL_ROWS NO_PARALLEL OUTLINE_LEAF(@"SEL$F6045C7B") OUTLINE_LEAF(@"SEL$ACF30367") OUTLINE_LEAF(@"SEL$6EE793B7") OUTLINE_LEAF(@"SEL$E1F9C76C") OUTLINE_LEAF(@"SEL$5E9A798F") TABLE_LOOKUP_BY_NL(@"SEL$0E028FD0" "S"@"SEL$1") OUTLINE_LEAF(@"SEL$D5EF7599") OUTLINE(@"SEL$1") OUTLINE(@"SEL$0E028FD0") OUTLINE(@"SEL$C3AF6D21") ELIMINATE_JOIN(@"SEL$1" "CH"@"SEL$1") OUTLINE(@"SEL$5208623C") STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("T"@"SEL$1") ("CH"@"SEL$1") TEMP_TABLE("C"@"SEL$1"))) FULL(@"SEL$D5EF7599" "T"@"SEL$1") NO_ACCESS(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599") FULL(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB") LEADING(@"SEL$D5EF7599" "T"@"SEL$1" "VW_ST_A3F94988"@"SEL$D5EF7599" "T1"@"SEL$9C741BEB") USE_HASH(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599") USE_HASH(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB") SWAP_JOIN_INPUTS(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB") USE_HASH_AGGREGATION(@"SEL$D5EF7599") BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CHANNEL_ID") 1) BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."TIME_ID") 2) BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CUST_ID") 3) ROWID(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F") LEADING(@"SEL$5E9A798F" "S"@"SEL$1" "SYS_CP_S"@"SEL$5E9A798F") SUBQUERY_PRUNING(@"SEL$5E9A798F" "S"@"SEL$1" PARTITION) USE_NL(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F") FULL(@"SEL$E1F9C76C" "T1"@"SEL$E1F9C76C") SEMIJOIN_DRIVER(@"SEL$E1F9C76C") FULL(@"SEL$6EE793B7" "CH"@"SEL$6EE793B7") SEMIJOIN_DRIVER(@"SEL$6EE793B7") FULL(@"SEL$ACF30367" "T"@"SEL$ACF30367") SEMIJOIN_DRIVER(@"SEL$ACF30367") FULL(@"SEL$F6045C7B" "C"@"SEL$F6045C7B") SEMIJOIN_DRIVER(@"SEL$F6045C7B") END_OUTLINE_DATA */Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("C"."CUST_STATE_PROVINCE"='CA') 5 - access("ITEM_1"="C0") 7 - access("ITEM_2"="T"."TIME_ID") 8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02')) 17 - filter("CH"."CHANNEL_DESC"='Internet') 18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID") 22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02')) 23 - access("S"."TIME_ID"="T"."TIME_ID") 28 - access("S"."CUST_ID"="C0")Column Projection Information (identified by operation id):----------------------------------------------------------- 1 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22] 2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0] 3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_CITY"[VARCHAR2,30], "C"."CUST_STATE_PROVINCE"[VARCHAR2,40] 4 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22] 5 - (#keys=1; rowset=256) "C0"[NUMBER,22], "ITEM_1"[NUMBER,22], "C1"[VARCHAR2,30], "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_3"[NUMBER,22] 6 - (rowset=256) "C0"[NUMBER,22], "C1"[VARCHAR2,30] 7 - (#keys=1; rowset=256) "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_1"[NUMBER,22], "ITEM_3"[NUMBER,22] 8 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7] 9 - "ITEM_1"[NUMBER,22], "ITEM_2"[DATE,7], "ITEM_3"[NUMBER,22] 10 - ROWID[ROWID,10], ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22] 11 - ROWID[ROWID,10] 12 - ROWID[ROWID,10] 13 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496] 14 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496] 15 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CHANNEL_ID"[NUMBER,22] 16 - (#keys=2) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20] 17 - (rowset=256) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20] 18 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CHANNEL_ID"[NUMBER,22] 19 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496] 20 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."TIME_ID"[DATE,7] 21 - (#keys=2) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7] 22 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7] 23 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."TIME_ID"[DATE,7] 24 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496] 25 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CUST_ID"[NUMBER,22] 26 - (#keys=1) "C0"[NUMBER,22] 27 - (rowset=256) "C0"[NUMBER,22] 28 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CUST_ID"[NUMBER,22] 29 - ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]Note----- - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold - cbqt star transformation used for this statement - this is an adaptive plan从Note部分的cbqt star transformation used for this statement信息可知执行了星型转换，从执行计划中的ID=29这个步骤可知对表sales使用了索引扫描而不是全表扫描。对于子查询中的times(第22行),customers(第3行),channels（第17行）表中的每个键值，数据库使用事实表sales(第23,28,18行)上索引检索位图。
以上是"Oracle 12CR2查询转换之星型转换的方法"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！
维度
事实
查询
数据
数据库
谓词
销售
方法
之星
产品
位图
内容
参数
方案
篇文章
索引
检索
原始
只有
季度
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发企业优惠政策
我的世界中国服务器下载教程
租服务器下片
农业银行软件开发中心位置
网络安全术语英文单词
国家网络安全组徂长
服务器上安装安全卫士
高速服务器pcb知识
网络技术和网络工程是一样吗
桥接 dhcp服务器
视觉传达转软件开发
铁路公安网络安全大赛
太原国产信创服务器
安徽互联网新科技
中科院引文数据库是在哪里检索
自学c 软件开发
我的世界服务器0.15.6
投资者数据库
密码保障网络安全
wince 应用软件开发
关系数据库小型
贵州轩立韵互联网科技
淄博网络安全宣传活动
本地windows服务器
聚咖互联网络科技有限公司
sgk网络安全
就业网站系统数据库
临夏州国家网络安全周
服务器负载设备
网络技术服务询价函
