Oracle 12CR2查詢轉換之星型轉換

eric0435發表於2018-10-25
Oracle

星型轉換是一種最佳化轉換它用來避免對星型方案中的事實表進行全表掃描。一個星型方案將資料分成事實與維度表。事實是對一個事件比如銷售的測量通常是數字。維度是標識事實的分類，比如日期，位置與產品。一個事實表有一個由方案中維度表主鍵所組成的複合鍵。維度表實際上充當查詢或引用表能讓你選擇你查詢所要請求的值。

星型轉換的目的
在連線事實表與維度表時，星型轉換可能避免對事實表執行完全掃描。星型轉換透過只獲取連線到約束維度行記錄的相關事實行記錄來提高效能。在有些情況下，查詢已經在維度表的其它列上有限制性過濾了。過濾組合可以大大減少資料庫從事實表中要處理的資料集大小。

星型轉換的工作原理
星型轉換增加了子查詢謂詞，叫作點陣圖半連線謂詞，關聯到約束維度表。當在實際連線列上存在索引時最佳化器執行轉換。透過驅動點陣圖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_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;

示例輸出如下: 

SQL> show parameter star_transformation_enabled
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled          string      FALSE
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;
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
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66
22 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 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
Plan 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.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66
22 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 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
Plan 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$5E9A798F
Outline 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行)上索引檢索點陣圖。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2217536/，如需轉載，請註明出處，否則將追究法律責任。

相關文章