溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
在數據倉庫中，有事實表、維度表兩個概念。
事實表是數據倉庫結構中的中央表，它包含聯系事實與維度表的數字度量值和鍵。事實數據表包含描述業務（例如產品銷售）內特定事件的數據。
維度表是維度屬性的集合。是分析問題的一個窗口。是人們觀察數據的特定角度，是考慮問題時的一類屬性，屬性的集合構成一個維。
如圖示
我們以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 | --------------------------------------------------------------------------------------------------
這次就使用了物化視圖。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
