欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

Oracle學(xué)習(xí)之?dāng)?shù)據(jù)倉(cāng)庫(kù)（二）Dimension的理解

   在數(shù)據(jù)倉(cāng)庫(kù)中，有事實(shí)表、維度表兩個(gè)概念。

員工經(jīng)過(guò)長(zhǎng)期磨合與沉淀，具備了協(xié)作精神，得以通過(guò)團(tuán)隊(duì)的力量開(kāi)發(fā)出優(yōu)質(zhì)的產(chǎn)品。創(chuàng)新互聯(lián)堅(jiān)持“專(zhuān)注、創(chuàng)新、易用”的產(chǎn)品理念，因?yàn)椤皩?zhuān)注所以專(zhuān)業(yè)、創(chuàng)新互聯(lián)網(wǎng)站所以易用所以簡(jiǎn)單”。公司專(zhuān)注于為企業(yè)提供成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、微信公眾號(hào)開(kāi)發(fā)、電商網(wǎng)站開(kāi)發(fā)，微信小程序定制開(kāi)發(fā)，軟件按需網(wǎng)站建設(shè)等一站式互聯(lián)網(wǎng)企業(yè)服務(wù)。

   事實(shí)表是數(shù)據(jù)倉(cāng)庫(kù)結(jié)構(gòu)中的中央表，它包含聯(lián)系事實(shí)與維度表的數(shù)字度量值和鍵。事實(shí)數(shù)據(jù)表包含描述業(yè)務(wù)（例如產(chǎn)品銷(xiāo)售）內(nèi)特定事件的數(shù)據(jù)。

   維度表是維度屬性的集合。是分析問(wèn)題的一個(gè)窗口。是人們觀察數(shù)據(jù)的特定角度，是考慮問(wèn)題時(shí)的一類(lèi)屬性，屬性的集合構(gòu)成一個(gè)維。

   如圖示

 

Oracle 學(xué)習(xí)之 數(shù)據(jù)倉(cāng)庫(kù)（二） Dimension 的理解

我們以sh用戶(hù)下的sales表和times表來(lái)看，

SALES為事實(shí)表

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

如果我們創(chuàng)建一個(gè)物化視圖

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;

如果我們做如下按月的分組查詢(xún)

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 |
------------------------------------------------------------------------------------------------

可見(jiàn)查詢(xún)使用的是物化視圖，但是如果我需要按年、季度對(duì)數(shù)據(jù)做分組查詢(xún)呢？

  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;

這個(gè)查看肯定是不能使用物化視圖的，執(zhí)行計(jì)劃如下

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為了是查詢(xún)重寫(xiě)更加的智能，引入了Dimension的概念。Dimension我們稱(chēng)之為維，它是基于維度表的，用來(lái)描述維度表的維度之間的層級(jí)關(guān)系。

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定義等級(jí)，基于維度表，HIERARCHY關(guān)鍵字定義層級(jí)關(guān)系。由層級(jí)關(guān)系，我們知道quarter是由month組成的。

我們?cè)俅尾樵?xún)

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表做關(guān)聯(lián)，性能更高了。

我們對(duì)比如下兩個(gè)查詢(xún)

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|
-----------------------------------------------------------------------------------------------------------------

沒(méi)有使用物化視圖。

其實(shí)條件實(shí)質(zhì)上是一樣的，因?yàn)閠.calendar_quarter_desc = '1998-01' 和t.calendar_quarter_id = 1769 在times表中表示相同的數(shù)據(jù)。

但是Oracle不知道CALENDAR_QUARTER_DESC與CALENDAR_QUARTER_ID的關(guān)系。

我們?cè)趧?chuàng)建Dimension時(shí)，可以為L(zhǎng)EVEL指定屬性值。

如下

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);

我們?cè)俅尾樵?xún)

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 |
--------------------------------------------------------------------------------------------------

這次就使用了物化視圖。

分享標(biāo)題：Oracle學(xué)習(xí)之?dāng)?shù)據(jù)倉(cāng)庫(kù)（二）Dimension的理解
本文URL：http://chinadenli.net/article20/ppigco.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、網(wǎng)站設(shè)計(jì)公司、做網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)、建站公司、關(guān)鍵詞優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都app開(kāi)發(fā)公司