提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  其他

当前栏目

深入SQL执行计划之CBO查询转换(5)：星型转换（Star Transformation）

转换SQL执行 查询 深入 计划 Star Transformation
2023-06-13 09:11:17 时间

编者按：

本文作者系Scott（中文名陈晓辉），现任大连华信资深分析师 ，ORACLE数据库专家，曾就职于甲骨文中国。个人主页：segmentfault.com/u/db_perf ，经其本人授权发布。

【免责声明】本号文章仅代表个人观点，与任何公司无关。

今天讲一个小众Case，Oracle数据库的“Star Transformation”。

根据关系数据库的范式理论，表在设计过程中会尽量降低耦合和冗余。 于是，大多数的数据库都是这样设计的。

例： 在一个销售系统中，一般都会涉及到这样几种数据：产品，分类，厂家，卖家，买家等等。

下面是表设计：

产品表：产品ID，产品名，价格。
分类表：分类ID，分类名。
厂家表：厂家ID，厂家名。
卖家表：卖家ID，卖家名。
买家表：买家ID，买家名。
销售记录表：时间，产品ID，分类ID，厂家ID，卖家ID，买家ID，其他。

使用上面的数据库设计，基本可以满足销售数据的快速记录，更新等日常处理。 但是，随着销售记录的增加，做下面的销售汇总报表的时候就会非常慢，因为报表是需要把销售记录的各种ID转换成实际名字给人看的。

Select 产品名, sum(价格)
  from 销售记录表 t1, 产品表 t2, 分类表 t3, 厂家表 t4, 卖家表 t5, 买家表 t6
where t1.产品ID = t2.产品ID
   and t1.分类ID = t3.分类ID
   and t1.厂家ID = t4.厂家ID
   and t1.卖家ID = t5.卖家ID
   and t1.买家ID = t6.买家ID
   and t2.产品名 = <aaaa>
   and t3.分类名 = <bbbb>
   and t4.厂家名 = <cccc>
   and t5.卖家名 = <dddd>
   and t6.买家名 = <eeee>;

如何解决这个问题呢？ 聪明的ORACLE工程师设计了“Star Schema”和“Star Transformation”。

首先，什么是“Star Schema”呢？ 上面的例子里，我们可以发现在所有的表中，只有“销售记录表”一个“事实表”（Fact Table）。其他的表都只是"参照表"（Dimension Table）。结构可以用下面的图表示。

这就是一个典型的“Star Schema”。

那什么又是“Star Transformation”呢？

“Star Transformation”是ORACLE为了解决上面问题，结合ORACLE数据库特有的“Bitmap”索引而作出的优化。

“Star Transformation”是如何实现的呢？

  1. 在“Fact Table”的检索列上建立Bitmap索引。
  2. 通过检索列上的Bitmap索引操作（ BITMAP AND/OR）拿到“Fact Table”的RowID，从而避免”FULL SCAN“。

现在我用”Example Schemas“的几个表来演示一下。

取得”Star Transformation“没有启用时的执行计划。

取得”Star Transformation“启用时的执行计划。

简单的比较一下上面两个执行计划就可以发现”Star Transformation“启用后，Access的记录数和COST都大幅降低了。

在看一下CBO的动作，我们可以看到启用”Star Transformation“后，上面的SQL文被转换成了下面的SQL文：

SELECT
        "T1" . "C1" "CUST_CITY"
        ,"T" . "CALENDAR_QUARTER_DESC" "CALENDAR_QUARTER_DESC"
        ,SUM (
            "VW_ST_A3F94988" . "ITEM_3"
        ) "SALES_AMOUNT"
    FROM
        (
            SELECT
                    /*+ ORDERED USE_NL ("SYS_CP_S") NOPARALLEL ("S") */ "SYS_CP_S" . "CUST_ID" "ITEM_1"
                    ,"SYS_CP_S" . "TIME_ID" "ITEM_2"
                    ,"SYS_CP_S" . "AMOUNT_SOLD" "ITEM_3"
                FROM
                    "SH" . "SALES" "S"
                    ,"SH" . "SALES" "SYS_CP_S"
                WHERE
                    "S" . ROWID = "SYS_CP_S" . ROWID
                    AND "S" . "CUST_ID" = ANY (
                        SELECT
                                /*+ SEMIJOIN_DRIVER CACHE_TEMP_TABLE ("T1") */ "T1" . "C0" "C0"
                            FROM
                                "SYS" . "SYS_TEMP_0FD9D660B_340EC8" "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'
                    )
        ) "VW_ST_A3F94988"
        ,"SH" . "TIMES" "T"
        ,"SYS" . "SYS_TEMP_0FD9D660B_340EC8" "T1"
    WHERE
        "VW_ST_A3F94988" . "ITEM_2" = "T" . "TIME_ID"
        AND "VW_ST_A3F94988" . "ITEM_1" = "T1" . "C0"
        AND (
            "T" . "CALENDAR_QUARTER_DESC" = '1999-01'
            OR "T" . "CALENDAR_QUARTER_DESC" = '1999-02'
        )
    GROUP BY
        "T1" . "C1"
        ,"T" . "CALENDAR_QUARTER_DESC"

大家是否注意到了有一个临时表“SYS_TEMP_0FD9D660B_340EC8”被使用了呢？

这个转换我们上面没有提到，实际这是一个”Star Transformation“的附加功能，他把属于“c.cust_state_province = 'CA'”的“customers” 缓存到一个临时表中，避免了在和“sales”表数据结合是再次对”TABLE ACCESS FULL CUSTOMERS“

我们看看如果不使用临时表的情况。

SQL> alter session set star_transformation_enabled='temp_disable';
セッションが変更されました。
SQL> SELECT c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
  2    3    4  FROM sh.sales s,
  5   sh.times t,
  6   sh.customers c,
  7   sh.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;
  8    9   10   11   12   13   14

実行計画
----------------------------------------------------------
Plan hash value: 2346071880
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   |   405 | 27540 |  1337   (1)| 00:00:17 |       |       |
|   1 |  HASH GROUP BY                    |                   |   405 | 27540 |  1337   (1)| 00:00:17 |       |       |
|*  2 |   HASH JOIN                       |                   |  1948 |   129K|  1336   (1)| 00:00:17 |       |       |
|*  3 |    TABLE ACCESS FULL              | CUSTOMERS         |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|*  4 |    HASH JOIN                      |                   |  1949 | 81858 |   931   (1)| 00:00:12 |       |       |
|*  5 |     TABLE ACCESS FULL             | TIMES             |   183 |  2928 |    18   (0)| 00:00:01 |       |       |
|   6 |     VIEW                          | VW_ST_B1772830    |  1953 | 50778 |   913   (1)| 00:00:11 |       |       |
|   7 |      NESTED LOOPS                 |                   |  1953 |   108K|   487   (1)| 00:00:06 |       |       |
|   8 |       PARTITION RANGE SUBQUERY    |                   |  1952 | 54676 |   457   (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|
|   9 |        BITMAP CONVERSION TO ROWIDS|                   |  1952 | 54676 |   457   (1)| 00:00:06 |       |       |
|  10 |         BITMAP AND                |                   |       |       |            |          |       |       |
|  11 |          BITMAP MERGE             |                   |       |       |            |          |       |       |
|  12 |           BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
|  13 |            BUFFER SORT            |                   |       |       |            |          |       |       |
|* 14 |             TABLE ACCESS FULL     | CHANNELS          |     1 |    13 |     3   (0)| 00:00:01 |       |       |
|* 15 |            BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX |       |       |            |          |KEY(SQ)|KEY(SQ)|
|  16 |          BITMAP MERGE             |                   |       |       |            |          |       |       |
|  17 |           BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
|  18 |            BUFFER SORT            |                   |       |       |            |          |       |       |
|* 19 |             TABLE ACCESS FULL     | TIMES             |   183 |  2928 |    18   (0)| 00:00:01 |       |       |
|* 20 |            BITMAP INDEX RANGE SCAN| SALES_TIME_BIX    |       |       |            |          |KEY(SQ)|KEY(SQ)|
|  21 |          BITMAP MERGE             |                   |       |       |            |          |       |       |
|  22 |           BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
|  23 |            BUFFER SORT            |                   |       |       |            |          |       |       |
|* 24 |             TABLE ACCESS FULL     | CUSTOMERS         |   383 |  6128 |   405   (1)| 00:00:05 |       |       |
|* 25 |            BITMAP INDEX RANGE SCAN| SALES_CUST_BIX    |       |       |            |          |KEY(SQ)|KEY(SQ)|
|  26 |       TABLE ACCESS BY USER ROWID  | SALES             |     1 |    29 |   456   (1)| 00:00:06 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------------

是不是在24行，和“sales”表数据结合是再次对”TABLE ACCESS FULL CUSTOMERS“了呢。

最后，我们来总结一下如何启用”Star Transformation“。

1.  参数“star_transformation_enabled”设成“True”或“temp_disable”。
2.  "Fact Table"的检索列上有Bitmap索引。

相关文章