Oracle 19C 星型转型

原创 Asher.HU 2021-02-04

547

星型转换是一种优化器转换，可避免对星型架构中的事实表进行全表扫描。

5.7.1关于星型模式

一个星型模式将数据分成事实和维度。

事实是诸如销售之类的事件的度量，通常是数字。维度是识别事实的类别，例如日期，位置和产品。

事实表具有由架构的维表的主键组成的组合键。维度表充当查找表或参考表，使您能够选择约束查询的值。

图表通常显示一个中心事实表，其中的线条将其连接到维度表，从而呈现出星形。下图显示了sales作为事实表和products，times，customers，和channels作为维度表。

图5-1星型模式

一个雪花模式是其中的维度表引用其他桌的星型模式。一个暴风雪的模式是雪花模式的组合。

也可以看看：

《 Oracle数据库数据仓库指南》以了解有关星型模式的更多信息

5.7.2 星转化的目的

在事实和维度表的联接中，星形转换可以避免对事实表进行全面扫描。

星型转换仅通过提取与约束维行连接的相关事实行来提高性能。在某些情况下，查询在维表的其他列上具有限制性过滤器。筛选器的组合可以大大减少数据库从事实表处理的数据集。

5.7.3恒星转化如何工作

Star转换会添加与约束维度相对应的子查询谓词，称为位图半联接谓词。

当事实联接列上存在索引时，优化器将执行转换。通过驱动位图AND和OR子查询提供的键值的操作，数据库仅需要从事实表中检索相关的行。如果维表上的谓词过滤掉了重要数据，则转换可能比对事实表的完整扫描更为有效。

数据库从事实表中检索了相关行之后，数据库可能需要使用原始谓词将这些行连接回维表。满足以下条件时，数据库可以消除维表的联接：

维表上的所有谓词都是半联接子查询谓词的一部分。
从子查询中选择的列是唯一的。
维列不在SELECT列表，GROUP BY子句等中。

5.7.4恒星转化的控制

在STAR_TRANSFORMATION_ENABLED初始化参数控制星转换。

此参数采用以下值：

true
优化器通过自动识别事实和约束维表来执行星形转换。仅当转换后的计划的成本低于替代方案的成本时，优化器才会执行星形转换。另外，只要实现提高性能，优化器就会自动尝试进行临时表转换（请参阅“ 临时表转换：方案 ”）。
false （默认）
优化器不执行星形转换。
TEMP_DISABLE
该值与true除优化器不尝试临时表转换外的值相同。

也可以看看：

Oracle数据库参考，了解STAR_TRANSFORMATION_ENABLED初始化参数

5.7.5星型转型：场景

此方案演示了星形查询的星形转换。

示例5-6星查询

以下查询查找1999年第一季度和第二季度加利福尼亚州所有城市的互联网销售总额：

SELECT 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 = 'Internet'
AND    t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY c.cust_city, t.calendar_quarter_desc;
复制

示例输出如下：

CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
. . .
复制

在此示例中，sales是事实表，其他表是维度表。该sales表每销售一项产品就会包含一行，因此可以想象它包含数十亿条销售记录。但是，在指定的季度内，只有少数产品通过互联网出售给加利福尼亚的客户。

例5-7星型转换

此示例显示了示例5-6中查询的星形转换。转换避免对进行全表扫描sales。

SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM   sales s, times t, customers c
WHERE  s.time_id = t.time_id
AND    s.cust_id = c.cust_id
AND    c.cust_state_province = 'CA'
AND    t.calendar_quarter_desc IN ('1999-01','1999-02')
AND    s.time_id IN ( SELECT time_id
                      FROM   times 
                      WHERE  calendar_quarter_desc IN('1999-01','1999-02') )
AND    s.cust_id IN ( SELECT cust_id
                      FROM   customers
                      WHERE  cust_state_province='CA' )
AND    s.channel_id IN ( SELECT channel_id  
                         FROM   channels 
                         WHERE  channel_desc = 'Internet' )
GROUP BY c.cust_city, t.calendar_quarter_desc;
复制

示例5-8星型转化的部分执行计划

本示例显示了示例5-7中星形转换的执行计划的编辑版本。

第26行显示该sales表具有索引访问路径，而不是全表扫描。对于由channels（第14行），times（第19行）和customers（第24 行）的子查询产生的每个键值，数据库从sales事实表的索引中检索位图（第15、20、25行）。

位图中的每个位对应于事实表中的一行。当子查询的键值与事实表的行中的值相同时，将设置该位。例如，101000... 在位图中（省略号表示其余行的值为0），事实表的第1行和第3行具有与子查询匹配的键值。

第12、17和22行中的操作遍历子查询中的键并检索相应的位图。在示例5-7中，customers子查询查找州或省为的客户的ID CA。假设位101000... 对应于客户ID键值103515从customers表子查询。还假设customers子查询103516使用位图生成键值010000...，这意味着只有第2行sales具有与子查询匹配的键值。

数据库（使用OR运算符）合并每个子查询的位图（第11、16、21行）。在我们的customers示例中，数据库在合并两个位图后111000...为customers子查询生成了一个位图：

101000...   # bitmap corresponding to key 103515
010000...   # bitmap corresponding to key 103516
---------
111000...   # result of OR operation
复制

在第10行中，数据库将AND运算符应用于合并的位图。假设数据库执行完所有OR操作后，得到的位图为channels：100000...如果数据库AND对此位图和customers子查询中的位图执行了操作，则结果如下：

100000...   # channels bitmap after all OR operations performed
111000...   # customers bitmap after all OR operations performed
---------
100000...   # bitmap result of AND operation for channels and customers
复制

在第9行中，数据库生成最终位图的相应行ID。数据库sales使用行标识从事实表中检索行（第26行）。在我们的示例中，数据库仅生成一个与第一行相对应的rowid，因此仅获取单个行，而不扫描整个sales表。

-------------------------------------------------------------------------------
| Id  | Operation                         | Name
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |    
|   1 |  HASH GROUP BY                    | 
|*  2 |   HASH JOIN                       |
|*  3 |    TABLE ACCESS FULL              | CUSTOMERS
|*  4 |    HASH JOIN                      | 
|*  5 |     TABLE ACCESS FULL             | TIMES 
|   6 |     VIEW                          | VW_ST_B1772830 
|   7 |      NESTED LOOPS                 | 
|   8 |       PARTITION RANGE SUBQUERY    |  
|   9 |        BITMAP CONVERSION TO ROWIDS|  
|  10 |         BITMAP AND                |
|  11 |          BITMAP MERGE             | 
|  12 |           BITMAP KEY ITERATION    | 
|  13 |            BUFFER SORT            |
|* 14 |             TABLE ACCESS FULL     | CHANNELS 
|* 15 |            BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX 
|  16 |          BITMAP MERGE             | 
|  17 |           BITMAP KEY ITERATION    |
|  18 |            BUFFER SORT            |
|* 19 |             TABLE ACCESS FULL     | TIMES 
|* 20 |            BITMAP INDEX RANGE SCAN| SALES_TIME_BIX
|  21 |          BITMAP MERGE             |
|  22 |           BITMAP KEY ITERATION    | 
|  23 |            BUFFER SORT            | 
|* 24 |             TABLE ACCESS FULL     | CUSTOMERS 
|* 25 |            BITMAP INDEX RANGE SCAN| SALES_CUST_BIX 
|  26 |       TABLE ACCESS BY USER ROWID  | SALES
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="C"."CUST_ID")
   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   4 - access("ITEM_2"="T"."TIME_ID")
   5 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' 
               OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  14 - filter("CH"."CHANNEL_DESC"='Internet')
  15 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  19 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' 
               OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  20 - access("S"."TIME_ID"="T"."TIME_ID")
  24 - filter("C"."CUST_STATE_PROVINCE"='CA')
  25 - access("S"."CUST_ID"="C"."CUST_ID")

Note
-----
   - star transformation used for this statement
复制

5.7.6临时表转换：方案

在上述情况下，优化器不会将表channels重新加入表中，sales因为它没有在外部引用，并且channel_id是唯一的。

但是，如果优化器无法消除联接，则数据库会将子查询结果存储在临时表中，以避免重新扫描维度表以生成位图密钥并重新联接。另外，如果查询并行运行，则数据库将具体化结果，以便每个并行执行服务器都可以从临时表中选择结果，而不必再次执行子查询。

示例5-9使用临时表进行星型转换

在此示例中，数据库将子查询的结果具体化customers到临时表中：

SELECT t1.c1 cust_city, t.calendar_quarter_desc calendar_quarter_desc, 
       SUM(s.amount_sold) sales_amount
FROM   sales s, sh.times t, sys_temp_0fd9d6621_e7e24 t1 
WHERE  s.time_id=t.time_id
AND    s.cust_id=t1.c0
AND    (t.calendar_quarter_desc='1999-q1' OR t.calendar_quarter_desc='1999-q2')
AND    s.cust_id IN    ( SELECT t1.c0 
                         FROM   sys_temp_0fd9d6621_e7e24 t1 )
AND    s.channel_id IN ( SELECT ch.channel_id 
                         FROM   channels ch
                         WHERE  ch.channel_desc='internet' )
AND    s.time_id IN    ( SELECT t.time_id
                         FROM   times t
                         WHERE  t.calendar_quarter_desc='1999-q1'
                         OR     t.calendar_quarter_desc='1999-q2' )
GROUP BY t1.c1, t.calendar_quarter_desc
复制

优化器将customers用临时表sys_temp_0fd9d6621_e7e24替换，并将对列的引用cust_id以及cust_city临时表的相应列替换。数据库创建具有两列的临时表：(c0 NUMBER, c1 VARCHAR2(30))。这些列对应于cust_id和cust_city的的customers表。数据库通过在上一个查询的执行开始时执行以下查询来填充临时表：

SELECT c.cust_id, c.cust_city FROM customers WHERE c.cust_state_province = 'CA'
复制

示例5-10使用临时表进行恒星转化的部分执行计划

以下示例显示了示例5-9中查询的执行计划的编辑版本：

-------------------------------------------------------------------------------
| Id  | Operation                          | Name
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |
|   1 |  TEMP TABLE TRANSFORMATION         |
|   2 |   LOAD AS SELECT                   |
|*  3 |    TABLE ACCESS FULL               | CUSTOMERS
|   4 |   HASH GROUP BY                    | 
|*  5 |    HASH JOIN                       | 
|   6 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D6613_C716F 
|*  7 |     HASH JOIN                      | 
|*  8 |      TABLE ACCESS FULL             | TIMES 
|   9 |      VIEW                          | VW_ST_A3F94988 
|  10 |       NESTED LOOPS                 | 
|  11 |        PARTITION RANGE SUBQUERY    | 
|  12 |         BITMAP CONVERSION TO ROWIDS| 
|  13 |          BITMAP AND                | 
|  14 |           BITMAP MERGE             | 
|  15 |            BITMAP KEY ITERATION    |  
|  16 |             BUFFER SORT            |  
|* 17 |              TABLE ACCESS FULL     | CHANNELS 
|* 18 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX 
|  19 |           BITMAP MERGE             |  
|  20 |            BITMAP KEY ITERATION    | 
|  21 |             BUFFER SORT            |  
|* 22 |              TABLE ACCESS FULL     | TIMES 
|* 23 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX 
|  24 |           BITMAP MERGE             |  
|  25 |            BITMAP KEY ITERATION    |  
|  26 |             BUFFER SORT            | 
|  27 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D6613_C716F 
|* 28 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX 
|  29 |        TABLE ACCESS BY USER ROWID  | SALES 
-------------------------------------------------------------------------------
 
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")
复制

计划的第1、2和3 customers行将子查询具体化到临时表中。在第6行中，数据库扫描临时表（而不是子查询）以从事实表构建位图。第27行扫描临时表以进行联接，而不是进行扫描customers。数据库不需要customers在临时表上应用过滤器，因为在实现临时表的同时应用了过滤器。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者