Oracle learning-Data Warehouse (2) Dimension

Oracle learning-Data Warehouse (2) Dimension
In a data warehouse, there are two concepts: fact table and dimension table. A fact table is a central table in the data warehouse structure. It contains the numerical measurement values and keys that relate to facts and dimension tables. Fact data tables contain data describing specific events in a business (such as product sales. A dimension table is a set of dimension attributes. Is a window for problem analysis. It is a specific point of view for people to observe data. It is a type of attribute in consideration of the problem. The set of attributes forms a dimension. We can see from the sales table and times table under the sh user, SALES is the fact table

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 is a dimension table.

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

 

If we create a Materialized View

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_idGROUP BY prod_id,         channel_id,         promo_id,         t.calendar_month_id;

 

If we perform the following monthly grouping queries:

SQL> alter session set query_rewrite_enabled=true;SQL> alter session set query_rewrite_integrity=trusted;SQL> set autotrace traceonlySQL> set line 200SQL>   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_idGROUP 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 |------------------------------------------------------------------------------------------------

 

It can be seen that the query uses a materialized view, but what if I want to perform grouped query on the data by year or quarter?

  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_idGROUP BY prod_id,         channel_id,         promo_id,         t.calendar_quarter_id;

 

The materialized view cannot be used for this view. The execution plan is as follows:

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 introduces the concept of Dimension to make query rewriting more intelligent. Dimension is called a Dimension. It is based on a Dimension table and is used to describe the hierarchical relationship between dimensions of a Dimension table.

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: defines the LEVEL based on the dimension table and HIERARCHY keywords. By hierarchical relationships, we know that quarter is composed of month. Query again

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

 

This time, the materialized view is used to associate with the times table, with higher performance. The following two queries are compared:

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 = 1769GROUP 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 |--------------------------------------------------------------------------------------------------

 

Materialized View used

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

 

Materialized views are not used. In fact, the conditions are essentially the same, because t. calendar_quarter_desc = '2014-01 'and t. calendar_quarter_id = 1998 indicate the same data in the times table. However, Oracle does not know the relationship between CALENDAR_QUARTER_DESC and CALENDAR_QUARTER_ID. When creating Dimension, we can specify the attribute value for LEVEL. As follows:

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

 

Query again

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

 

This time the materialized view is used.