程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle 學習之數據倉庫（二） Dimension 的理解

Oracle 學習之數據倉庫（二） Dimension 的理解

編輯：Oracle教程

Oracle 學習之數據倉庫（二） Dimension 的理解

在數據倉庫中，有事實表、維度表兩個概念。事實表是數據倉庫結構中的中央表，它包含聯系事實與維度表的數字度量值和鍵。事實數據表包含描述業務（例如產品銷售）內特定事件的數據。維度表是維度屬性的集合。是分析問題的一個窗口。是人們觀察數據的特定角度，是考慮問題時的一類屬性，屬性的集合構成一個維。如圖示

我們以sh用戶下的sales表和times表來看， SALES為事實表

SQL> desc sales
 Name                     Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                  NOT NULL NUMBER
 CUST_ID                  NOT NULL NUMBER
 TIME_ID                  NOT NULL DATE
 CHANNEL_ID                   NOT NULL NUMBER
 PROMO_ID                 NOT NULL NUMBER
 QUANTITY_SOLD                NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                  NOT NULL NUMBER(10,2)

TIMES為維度表

SQL> desc times
 Name                     Null?    Type
 ----------------------------------------- -------- ----------------------------
 TIME_ID                  NOT NULL DATE
 DAY_NAME                 NOT NULL VARCHAR2(9)
 DAY_NUMBER_IN_WEEK               NOT NULL NUMBER(1)
 DAY_NUMBER_IN_MONTH              NOT NULL NUMBER(2)
 CALENDAR_WEEK_NUMBER             NOT NULL NUMBER(2)
 FISCAL_WEEK_NUMBER               NOT NULL NUMBER(2)
 WEEK_ENDING_DAY              NOT NULL DATE
 WEEK_ENDING_DAY_ID               NOT NULL NUMBER
 CALENDAR_MONTH_NUMBER            NOT NULL NUMBER(2)
 FISCAL_MONTH_NUMBER              NOT NULL NUMBER(2)
 CALENDAR_MONTH_DESC              NOT NULL VARCHAR2(8)
 CALENDAR_MONTH_ID            NOT NULL NUMBER
 FISCAL_MONTH_DESC            NOT NULL VARCHAR2(8)
 FISCAL_MONTH_ID              NOT NULL NUMBER
 DAYS_IN_CAL_MONTH            NOT NULL NUMBER
 DAYS_IN_FIS_MONTH            NOT NULL NUMBER
 END_OF_CAL_MONTH             NOT NULL DATE
 END_OF_FIS_MONTH             NOT NULL DATE
 CALENDAR_MONTH_NAME              NOT NULL VARCHAR2(9)
 FISCAL_MONTH_NAME            NOT NULL VARCHAR2(9)
 CALENDAR_QUARTER_DESC            NOT NULL CHAR(7)
 CALENDAR_QUARTER_ID              NOT NULL NUMBER
 FISCAL_QUARTER_DESC              NOT NULL CHAR(7)
 FISCAL_QUARTER_ID            NOT NULL NUMBER
 DAYS_IN_CAL_QUARTER              NOT NULL NUMBER
 DAYS_IN_FIS_QUARTER              NOT NULL NUMBER
 END_OF_CAL_QUARTER               NOT NULL DATE
 END_OF_FIS_QUARTER               NOT NULL DATE
 CALENDAR_QUARTER_NUMBER          NOT NULL NUMBER(1)
 FISCAL_QUARTER_NUMBER            NOT NULL NUMBER(1)
 CALENDAR_YEAR                NOT NULL NUMBER(4)
 CALENDAR_YEAR_ID             NOT NULL NUMBER
 FISCAL_YEAR                  NOT NULL NUMBER(4)
 FISCAL_YEAR_ID              NOT NULL NUMBER
 DAYS_IN_CAL_YEAR             NOT NULL NUMBER
 DAYS_IN_FIS_YEAR             NOT NULL NUMBER
 END_OF_CAL_YEAR              NOT NULL DATE
 END_OF_FIS_YEAR              NOT NULL DATE

如果我們創建一個物化視圖

create materialized view sales_month_sum 
enable query rewrite as 
  SELECT t.calendar_month_id,
         prod_id,
         channel_id,
         promo_id,
         SUM (quantity_sold) quantity_sold,
         SUM (amount_sold) amount_sold
    FROM sales s, times t
   WHERE s.time_id = t.time_id
GROUP BY prod_id,
         channel_id,
         promo_id,
         t.calendar_month_id;

如果我們做如下按月的分組查詢

SQL> alter session set query_rewrite_enabled=true;
SQL> alter session set query_rewrite_integrity=trusted;
SQL> set autotrace traceonly
SQL> set line 200
SQL>   SELECT t.calendar_month_id,
         prod_id,
         channel_id,
         promo_id,
         SUM (quantity_sold) quantity_sold,
         SUM (amount_sold) amount_sold
    FROM sales s, times t
   WHERE s.time_id = t.time_id
GROUP BY prod_id,
         channel_id,
         promo_id,
         t.calendar_month_id; 

9068 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3287305789

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |  9068 |   690K|    13  (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| SALES_MONTH_SUM |  9068 |   690K|    13  (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

可見查詢使用的是物化視圖，但是如果我需要按年、季度對數據做分組查詢呢？

  SELECT t.calendar_quarter_id,prod_id,
         channel_id,
         promo_id,
         SUM (quantity_sold) quantity_sold,
         SUM (amount_sold) amount_sold
    FROM sales s, times t
   WHERE s.time_id = t.time_id
GROUP BY prod_id,
         channel_id,
         promo_id,
         t.calendar_quarter_id;

這個查看肯定是不能使用物化視圖的，執行計劃如下

Execution Plan
----------------------------------------------------------
Plan hash value: 3221963832

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |  |  2037 | 79443 |   569   (6)| 00:00:07 |  |   |
|   1 |  HASH GROUP BY            |  |  2037 | 79443 |   569   (6)| 00:00:07 |  |   |
|*  2 |   HASH JOIN            |  |   918K|    34M|   546   (2)| 00:00:07 |    |   |
|   3 |    PART JOIN FILTER CREATE    | :BF0000 |  1826 | 21912 |    18   (0)| 00:00:01 |    |   |
|   4 |     TABLE ACCESS FULL          | TIMES    |  1826 | 21912 |    18   (0)| 00:00:01 | |   |
|   5 |    PARTITION RANGE JOIN-FILTER|  |   918K|    23M|   525   (2)| 00:00:07 |:BF0000|:BF0000|
|   6 |     TABLE ACCESS FULL          | SALES    |   918K|    23M|   525   (2)| 00:00:07 |:BF0000|:BF0000|
---------------------------------------------------------------------------------------------------------

Oracle為了是查詢重寫更加的智能，引入了Dimension的概念。Dimension我們稱之為維，它是基於維度表的，用來描述維度表的維度之間的層級關系。

CREATE DIMENSION SH.TIMES_DIM
  LEVEL DAY                            IS 
    (SH.TIMES.TIME_ID)
  LEVEL MONTH                          IS 
    (SH.TIMES.CALENDAR_MONTH_ID)
  LEVEL QUARTER                        IS 
    (SH.TIMES.CALENDAR_QUARTER_ID)
  LEVEL YEAR                           IS 
    (SH.TIMES.CALENDAR_YEAR_ID)
  HIERARCHY CAL_ROLLUP
    (DAY                               CHILD OF
     MONTH                             CHILD OF
     QUARTER                           CHILD OF
     YEAR);

LEVEL定義等級，基於維度表，HIERARCHY關鍵字定義層級關系。由層級關系，我們知道quarter是由month組成的。我們再次查詢

SQL> SELECT t.calendar_quarter_id,prod_id,
         channel_id,
         promo_id,
         SUM (quantity_sold) quantity_sold,
         SUM (amount_sold) amount_sold
    FROM sales s, times t
   WHERE s.time_id = t.time_id
GROUP BY prod_id,
         channel_id, 
         promo_id,
         t.calendar_quarter_id;

3375 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3397140165

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    20 |  1720 |    36  (14)| 00:00:01 |
|   1 |  HASH GROUP BY             |      |    20 |  1720 |    36  (14)| 00:00:01 |
|*  2 |   HASH JOIN             |      |   128K|    10M|    33   (7)| 00:00:01 |
|   3 |    VIEW               |      |   849 |  6792 |    19   (6)| 00:00:01 |
|   4 |     HASH UNIQUE         |      |   849 |  6792 |    19   (6)| 00:00:01 |
|   5 |      TABLE ACCESS FULL         | TIMES       |  1826 | 14608 |    18   (0)| 00:00:01 |
|   6 |    MAT_VIEW REWRITE ACCESS FULL| SALES_MONTH_SUM |  9068 |   690K|    13   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

這次是使用物化視圖與times表做關聯，性能更高了。我們對比如下兩個查詢

SQL>   SELECT t.calendar_quarter_id,
         prod_id,
         channel_id,
         promo_id,
         SUM (quantity_sold) quantity_sold,
         SUM (amount_sold) amount_sold
    FROM sales s, times t
   WHERE s.time_id = t.time_id AND t.calendar_quarter_id = 1769
GROUP BY prod_id,
         channel_id,
         promo_id,
         t.calendar_quarter_id; 

168 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3397140165

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     1 |    86 |    33   (7)| 00:00:01 |
|   1 |  HASH GROUP BY             |      |     1 |    86 |    33   (7)| 00:00:01 |
|*  2 |   HASH JOIN             |      |  6423 |   539K|    32   (4)| 00:00:01 |
|   3 |    VIEW               |      |    34 |   272 |    19   (6)| 00:00:01 |
|   4 |     HASH UNIQUE         |      |    34 |   272 |    19   (6)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL          | TIMES       |    90 |   720 |    18   (0)| 00:00:01 |
|   6 |    MAT_VIEW REWRITE ACCESS FULL| SALES_MONTH_SUM |  9068 |   690K|    13   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

使用了物化視圖

SQL>SELECT t.calendar_quarter_id,
         prod_id,
         channel_id,
         promo_id,
         SUM (quantity_sold) quantity_sold,
         SUM (amount_sold) amount_sold
    FROM sales s, times t
   WHERE s.time_id = t.time_id AND t.calendar_quarter_desc = '1998-01'
GROUP BY prod_id,
         channel_id,
         promo_id,
         t.calendar_quarter_id;

168 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3221963832

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |  |  8146 |   373K| |   848   (2)| 00:00:11 |   |   |
|   1 |  HASH GROUP BY            |  |  8146 |   373K|  3632K|   848   (2)| 00:00:11 |   |   |
|*  2 |   HASH JOIN            |  | 57459 |  2637K|   |   546   (2)| 00:00:07 |   |   |
|   3 |    PART JOIN FILTER CREATE    | :BF0000 |    91 |  1820 |  |    18   (0)| 00:00:01 |  |   |
|*  4 |     TABLE ACCESS FULL       | TIMES    |    91 |  1820 |   |    18   (0)| 00:00:01 |  |   |
|   5 |    PARTITION RANGE JOIN-FILTER|  |   918K|    23M|    |   525   (2)| 00:00:07 |:BF0000|:BF0000|
|   6 |     TABLE ACCESS FULL          | SALES    |   918K|    23M|    |   525   (2)| 00:00:07 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------------

沒有使用物化視圖。其實條件實質上是一樣的，因為t.calendar_quarter_desc = '1998-01' 和t.calendar_quarter_id = 1769 在times表中表示相同的數據。但是Oracle不知道CALENDAR_QUARTER_DESC與CALENDAR_QUARTER_ID的關系。我們在創建Dimension時，可以為LEVEL指定屬性值。如下

CREATE DIMENSION SH.TIMES_DIM
  LEVEL DAY                            IS 
    (SH.TIMES.TIME_ID)
  LEVEL MONTH                          IS 
    (SH.TIMES.CALENDAR_MONTH_ID)
  LEVEL QUARTER                        IS 
    (SH.TIMES.CALENDAR_QUARTER_ID)
  LEVEL YEAR                           IS 
    (SH.TIMES.CALENDAR_YEAR_ID)
  HIERARCHY CAL_ROLLUP
    (DAY                               CHILD OF
     MONTH                             CHILD OF
     QUARTER                           CHILD OF
     YEAR)
  ATTRIBUTE QUARTER DETERMINES 
    (SH.TIMES.CALENDAR_QUARTER_DESC,
     SH.TIMES.DAYS_IN_CAL_QUARTER,
     SH.TIMES.END_OF_CAL_QUARTER,
     SH.TIMES.CALENDAR_QUARTER_NUMBER)
  ATTRIBUTE YEAR DETERMINES 
    (SH.TIMES.CALENDAR_YEAR,
     SH.TIMES.DAYS_IN_CAL_YEAR,
     SH.TIMES.END_OF_CAL_YEAR);

我們再次查詢

SQL> SELECT t.calendar_quarter_id,
         prod_id,
         channel_id,
         promo_id,
         SUM (quantity_sold) quantity_sold,
         SUM (amount_sold) amount_sold
    FROM sales s, times t
   WHERE s.time_id = t.time_id AND t.calendar_quarter_desc = '1998-01'
GROUP BY prod_id,
         channel_id,
         promo_id,
         t.calendar_quarter_id;  

168 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3290467316

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    20 |  2240 |    33   (7)| 00:00:01 |
|   1 |  HASH GROUP BY             |      |    20 |  2240 |    33   (7)| 00:00:01 |
|*  2 |   HASH JOIN             |      | 17191 |  1880K|    32   (4)| 00:00:01 |
|   3 |    VIEW               | VW_GBF_5    |    91 |  3094 |    19   (6)| 00:00:01 |
|   4 |     HASH GROUP BY           |      |    91 |   728 |    19   (6)| 00:00:01 |
|   5 |      VIEW            |      |    91 |   728 |    19   (6)| 00:00:01 |
|   6 |       HASH UNIQUE          |      |    91 |  1456 |    19   (6)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL       | TIMES      |    91 |  1456 |    18   (0)| 00:00:01 |
|   8 |    MAT_VIEW REWRITE ACCESS FULL| SALES_MONTH_SUM |  9068 |   690K|    13   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

這次就使用了物化視圖。