Oracle Star Schema

星型轉換是一個非常強大的最佳化技術，它是通過對原來的SQL語句的隱式的改寫來實現的，它能夠很大程度減少I/O. 終端使用者並不需要知道有關星型轉換的任何細節。資料庫最佳化器會在合適的時候進行星型轉換。要獲得星型轉換的最大效能，需要遵循以下3個基本的條件：

1，事實表上的維度列上要有外鍵
2，事實表的每個外鍵上都有BITMAP索引。
3，star_transformation_enabled=true。   系統預設是false. 它有三個取值：（TRUE, FALSE, TEMP_DISABLE）.其中TEMP_DISABLE表示不允許用暫存資料表來存放第一次掃描的結果集。 星型查詢中，維度資料表會被掃描兩次，如果維度資料表很大，效能會很差，所以需要一個暫存資料表來存放第一次掃描的維度資料表集合。
如果能夠滿足這三個條件,則查詢會使用star transformation,而這是提高基於事實表的查詢效率的主要的技術。

資料庫進行星型查詢時，會使用兩個基本的階段：
第一個階段從事實表(或者說結果集)裡擷取所有必要的記錄行。由於這是通過bitmap索引來檢索資料，因此比較高效。
第二個階段將該結果集與維度資料表進行關聯。這叫做semi-join(也就是exists和in寫法)。
注意：只有oracle企業版才有bitmap索引。標準版不支援bitmap索引和星型轉換。

 

下面的SQL語句執行計畫可以很好的說明進行"星型查詢"的條件：

SQL> show parameter star_transformation_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled          string      FALSE

 

 

SQL> conn sh/sh
Connected.

 

SQL>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')
 11  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

Execution Plan
----------------------------------------------------------
Plan hash value: 1612666291

--------------------------------------------------------------------------------
--------------------------

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|
Time     | Pstart| Pstop |

--------------------------------------------------------------------------------
--------------------------

|   0 | SELECT STATEMENT             |           |   572 | 48048 |   793   (8)|
00:00:10 |       |       |

|   1 |  HASH GROUP BY               |           |   572 | 48048 |   793   (8)|
00:00:10 |       |       |

|*  2 |   HASH JOIN                  |           |  3116 |   255K|   791   (8)|
00:00:10 |       |       |

|*  3 |    TABLE ACCESS FULL         | CHANNELS  |     2 |    42 |     3   (0)|
00:00:01 |       |       |

|*  4 |    HASH JOIN                 |           |  6231 |   383K|   788   (8)|
00:00:10 |       |       |

|*  5 |     TABLE ACCESS FULL        | TIMES     |   183 |  2928 |    15   (0)|
00:00:01 |       |       |

|*  6 |     HASH JOIN                |           | 49822 |  2286K|   771   (8)|
00:00:10 |       |       |

|*  7 |      TABLE ACCESS FULL       | CUSTOMERS |   383 |  9958 |   332   (2)|
00:00:04 |       |       |

|   8 |      PARTITION RANGE SUBQUERY|           |   918K|    18M|   426   (9)|
00:00:06 |KEY(SQ)|KEY(SQ)|

|   9 |       TABLE ACCESS FULL      | SALES     |   918K|    18M|   426   (9)|
00:00:06 |KEY(SQ)|KEY(SQ)|

--------------------------------------------------------------------------------
--------------------------

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

   2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   3 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
   4 - access("S"."TIME_ID"="T"."TIME_ID")
   5 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')

   6 - access("S"."CUST_ID"="C"."CUST_ID")
   7 - filter("C"."CUST_STATE_PROVINCE"='CA')

可以看出上面的SQL語句沒有用到star transfermation, 因為star_transformation_enabled=false。執行計畫只是普通的JOIN操作。

 

 

 我來把star_transformation_enabled=TRUE，讓他用star transfermation技術執行SQL語句。

SQL> alter system set star_transformation_enabled=true;

System altered.

SQL> col PLAN_PLUS_EXP format a120
SQL> set autotrace traceonly exp;

 

SQL> col PLAN_PLUS_EXP format a120
SQL> set autotrace traceonly exp;
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')
 11  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

Execution Plan
----------------------------------------------------------
Plan hash value: 580301883

--------------------------------------------------------------------------------
----------------------------------------------------

| Id  | Operation                               | Name                     | Row
s  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------
----------------------------------------------------

|   0 | SELECT STATEMENT                        |                          |
 1 |    73 |   786   (2)| 00:00:10 |       |       |

|   1 |  TEMP TABLE TRANSFORMATION              |                          |
   |       |            |          |       |       |

|   2 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D660B_7E653 |
   |       |            |          |       |       |

|*  3 |    TABLE ACCESS FULL                    | CUSTOMERS                |   3
83 |  9958 |   332   (2)| 00:00:04 |       |       |

|   4 |   HASH GROUP BY                         |                          |
 1 |    73 |   455   (2)| 00:00:06 |       |       |

|*  5 |    HASH JOIN                            |                          |
 1 |    73 |   454   (2)| 00:00:06 |       |       |

|*  6 |     HASH JOIN                           |                          |
 2 |   104 |   450   (2)| 00:00:06 |       |       |

|*  7 |      HASH JOIN                          |                          |
32 |  1184 |   448   (2)| 00:00:06 |       |       |

|*  8 |       TABLE ACCESS FULL                 | TIMES                    |   1
83 |  2928 |    15   (0)| 00:00:01 |       |       |

|   9 |       PARTITION RANGE SUBQUERY          |                          |   2
54 |  5334 |   432   (2)| 00:00:06 |KEY(SQ)|KEY(SQ)|

|  10 |        TABLE ACCESS BY LOCAL INDEX ROWID| SALES                    |   2
54 |  5334 |   432   (2)| 00:00:06 |KEY(SQ)|KEY(SQ)|

|  11 |         BITMAP CONVERSION TO ROWIDS     |                          |
   |       |            |          |       |       |

|  12 |          BITMAP AND                     |                          |
   |       |            |          |       |       |

|  13 |           BITMAP MERGE                  |                          |
   |       |            |          |       |       |

|  14 |            BITMAP KEY ITERATION         |                          |
   |       |            |          |       |       |

|  15 |             BUFFER SORT                 |                          |
   |       |            |          |       |       |

|* 16 |              TABLE ACCESS FULL          | CHANNELS                 |
 2 |    42 |     3   (0)| 00:00:01 |       |       |

|* 17 |             BITMAP INDEX RANGE SCAN     | SALES_CHANNEL_BIX        |
   |       |            |          |KEY(SQ)|KEY(SQ)|

|  18 |           BITMAP MERGE                  |                          |
   |       |            |          |       |       |

|  19 |            BITMAP KEY ITERATION         |                          |
   |       |            |          |       |       |

|  20 |             BUFFER SORT                 |                          |
   |       |            |          |       |       |

|* 21 |              TABLE ACCESS FULL          | TIMES                    |   1
83 |  2928 |    15   (0)| 00:00:01 |       |       |

|* 22 |             BITMAP INDEX RANGE SCAN     | SALES_TIME_BIX           |
   |       |            |          |KEY(SQ)|KEY(SQ)|

|  23 |           BITMAP MERGE                  |                          |
   |       |            |          |       |       |

|  24 |            BITMAP KEY ITERATION         |                          |
   |       |            |          |       |       |

|  25 |             BUFFER SORT                 |                          |
   |       |            |          |       |       |

|  26 |              TABLE ACCESS FULL          | SYS_TEMP_0FD9D660B_7E653 |
 1 |    13 |     2   (0)| 00:00:01 |       |       |

|* 27 |             BITMAP INDEX RANGE SCAN     | SALES_CUST_BIX           |
   |       |            |          |KEY(SQ)|KEY(SQ)|

|  28 |      TABLE ACCESS FULL                  | SYS_TEMP_0FD9D660B_7E653 |   3
83 |  5745 |     2   (0)| 00:00:01 |       |       |

|* 29 |     TABLE ACCESS FULL                   | CHANNELS                 |
 2 |    42 |     3   (0)| 00:00:01 |       |       |

--------------------------------------------------------------------------------
----------------------------------------------------

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

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   6 - access("S"."CUST_ID"="C0")
   7 - access("S"."TIME_ID"="T"."TIME_ID")
   8 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')

  16 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
  17 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  21 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')

  22 - access("S"."TIME_ID"="T"."TIME_ID")
  27 - access("S"."CUST_ID"="C0")
  29 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')

從上面執行計畫可以看出，此SQL語句做了star transformation. 走了BITMAP索引，例如：BITMAP MERGE ,BITMAP AND等等。