Oracle数据仓库查询优化技术(2)

作者: 宁海元 出处:

三、Dimension

数据仓库中由于数据量巨大，一些聚合计算等操作往往通过物化视图预先计算存储，但是，不可能对所有维度的所有可能的聚合操作都建立物化视图。那么，在对某些聚合操作的SQL进行查询重写时，就希望能利用已经存在的物化视图，尽管他们的聚合操作条件不完全一致。而dimension定义的各个level之间的层次关系，对于一些上卷(rolling up)和下钻(drilling down)操作的查询重写的判断是相当重要的，dimension中定义的attributes对于使用不同的列来做分组的查询重写起作用。

在上一个物化视图的例子中，我们通过建立一个月度聚合的物化视图，使得月度统计的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 |
---------------------------------------------------------------------

 
四、位图连接索引

位图连接索引（bitmap join index）是基于多表连接的位图索引，连接条件要求是等于的内连接(equi-inner join)。对于数据仓库而言，较普遍的是Fact table的外键列和相关的Dimension table的主键列的等于连接操作。位图连接索引能够消除查询中的连接操作，因为它实际上已经将连接的结果保存在索引当中了。而且，相对于在表的连接列上建普通位图索引来说，位图连接索引需要更少的存储空间。物化视图也可以用来消除连接操作，但位图连接索引比起物化视图来更有效率，因为通过位图连接索引可以直接将基于索引列的查询对应到事实表的rowid。

一个连接事实表和维度表的查询：

SQL> select f.amount1,f.amount2
2 from fact_sales f,time_dim t
3 where t.time_id=f.time_id
4 and t.t_day='2007-01-01';
执行计划
---------------------------------------------------------- 
Plan hash value: 1080213047
---------------------------------------------------------------------
------------ 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
------------ 
| 0 | SELECT STATEMENT | | 1 | 22 | 9 (12)| 00:00:01 |
|*1 | HASH JOIN | | 1 | 22 | 9 (12)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TIME_DIM | 1 | 13 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| FACT_SALES | 1000 | 9000 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------

创建连接时间维度表和销售事实表之间的位图连接索引：

SQL> create bitmap index ix_sales_time
2 on fact_sales(time_dim.t_day)
3 from fact_sales,time_dim
4 where fact_sales.time_id=time_dim.time_id;

索引已创建。

再次执行同样的查询，可以发现，通过位图连接索引，无须再对time_dim和fact_salces表进行连接操作，直接通过位图连接索引，访问fact_slaes表即可得到结果：

SQL> select f.amount1,f.amount2
2 from fact_sales f,time_dim t
3 where t.time_id=f.time_id
4 and t.t_day='2007-01-01';
执行计划
---------------------------------------------------------- 
Plan hash value: 1533750321
------------------------------------------------------------
------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------
------------------------- 
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | FACT_SALES | 1 | 9 | 1 (0)| 
00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|*3 | BITMAP INDEX SINGLE VALUE | IX_SALES_TIME | | | | |
-------------------------------------------------------------

总结

本文简单的演示了Oracle数据仓库查询优化中的几种技术，注意体会各种不同的执行计划，选择最优的执行路径，减少逻辑读，是SQL性能优化的不二法门。