Shulou(Shulou.com)06/01 Report--

In the data warehouse, there are two concepts: fact table and dimension table.

The fact table is the central table in the data warehouse structure, which contains numerical measures and keys that relate to facts and dimension tables. The fact table contains data that describes specific events within a business, such as product sales.

A dimension table is a collection of dimension attributes. It is a window to analyze the problem. It is the specific angle from which people observe the data, and it is a kind of attribute when considering the problem, and the collection of attributes constitutes a dimension.

As shown in the diagram

Let's look at the sales table and times table under sh users.

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 (10penny 2) AMOUNT_SOLD NOT NULL NUMBER (10pence2)

TIMES is the 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 do the following monthly grouping query

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 Murray- -| 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 materialized views, but what if I need to group the data by year and quarter?

SELECT t. SUM quarterball, 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

This view certainly cannot use the materialized view, and the execution plan is as follows

Execution Plan---Plan hash value: 3221963832 Murray- -| 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 in order to make query rewriting more intelligent. Dimension, which we call dimension, is based on the dimension table and is used to describe the hierarchical relationship between the dimensions of the 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 levels, based on dimension tables, and the HIERARCHY keyword defines hierarchical relationships. By hierarchical relationships, we know that quarter is made up of month.

Let's check again.

SQL > SELECT t.promotional quarterkeeper, 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 Murray- -| 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, and the performance is higher.

Let's compare the following two queries

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 Murray- -| 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 | | | 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 views are 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 Murray- -| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |- -| 0 | SELECT STATEMENT | | 8146 | 373k | | 848K (2) | 00:00:11 | | 1 | HASH GROUP BY | | 8146 | 373K | 3632K | 848K | 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.

The conditions are essentially the same, because t.calendar_quarter_desc = '1998-01' and t.calendar_quarter_id = 1769 represent the same data in the times table.

But Oracle doesn't know the relationship between CALENDAR_QUARTER_DESC and CALENDAR_QUARTER_ID.

When we create the Dimension, we can specify the property value for the 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)

Let's check 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 Rows selected.Execution Plan---Plan hash value: 3290467316 Murray- -| 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 |- -

Materialized views are used this time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.