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