Oracle数据仓库查询优化技术

三、Dimension
    数据仓库中由于数据量巨大，一些聚合计算等操作往往通过物化视图预先计算存储，但是，不可能对所有维度的所有可能的聚合操作都建立物化视图。那么，在对某些聚合操作的SQL进行查询重写时，就希望能利用已经存在的物化视图，尽管他们的聚合操作条件不完全一致。而dimension定义的各个level之间的层次关系，对于一些上卷(rolling up)和下钻(drilling down)操作的查询重写的判断是相当重要的，dimension中定义的attributes对于使用不同的列来做分组的查询重写起作用。本部分的内容参考了网友d.c.b.a的一篇文章：http://www.anysql.net/oracle/oracle_olap_dimension.html，在此表示感谢。

  在上一个物化视图的例子中，我们通过建立一个月度聚合的物化视图，使得月度统计的SQL能够通过查询重写从物化视图中受益。但是，如果我们想按季度统计信息，则无法利用到该物化视图： 

SQL> select t.t_quarter, sum(f.amount1),sum(f.amount2) 2 from time_dim t,fact_sales f 3 where t.time_id=f.time_id 4 group by t.t_quarter; 执行计划 ---------------------------------------------------------- Plan hash value: 53462861 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 60000 | 9 (23)| 00:00:01 | | 1 | HASH GROUP BY | | 1000 | 60000 | 9 (23)| 00:00:01 | |* 2 | HASH JOIN | | 1000 | 60000 | 8 (13)| 00:00:01 | | 3 | TABLE ACCESS FULL| TIME_DIM | 1000 | 21000 | 4 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| FACT_SALES | 1000 | 39000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------

创建一个Dimension，指定维度表中各level之间的层次关系：

SQL> create dimension time_dim 2 level year is (time_dim.t_year) 3 level quarter is (time_dim.t_quarter) 4 level month is (time_dim.t_month) 5 level day is (time_dim.t_day) 6 hierarchy year_quarter_month_day 7 ( 8 day child of 9 month child of 10 quarter child of 11 year 12 );

维已创建。

重新执行查询，注意query_rewrite_integrity参数设置为Trust。同时分析fact_sales，time_dim表以及mv_month物化视图。有了dimension中定义的层次关系以后，优化器就能正确的利用月度物化视图进行季度统计的查询重写了：

SQL> select t.t_quarter, sum(f.amount1),sum(f.amount2) 2 from time_dim t,fact_sales f 3 where t.time_id=f.time_id 4 group by t.t_quarter; 执行计划 ---------------------------------------------------------- Plan hash value: 3478386927 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 330 | 11 (28)| 00:00:01 | | 1 | HASH GROUP BY | | 11 | 330 | 11 (28)| 00:00:01 | |*2 | HASH JOIN | | 257 | 7710 | 10 (20)| 00:00:01 | | 3| MAT_VIEW REWRITE ACCESS FULL| MV_MONTH |33 |561|3 (0)| 00:00:01| | 4 | VIEW | | 257 | 3341 | 6 (17)| 00:00:01 | | 5 | HASH UNIQUE | | 257 | 3341 | 6 (17)| 00:00:01 | | 6 | TABLE ACCESS FULL | TIME_DIM | 1000 | 13000 | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------------

查看本文来源