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