Oracle数据仓库查询优化技术

1、、运行环境SQL select * from v$version;BANNEROracle Database 10g Enterprise Edition Release 1020.1.0 - ProdP L/SQL Release 10.2.0.1.0 - P roductionCORE 10.2.0.1.0P roductionTNS for 32-bit Windows: Version 10.2.0.1.0 - P roductionNLSRTL Version 10.2.0.1.0 - P roductionSQL show p arameter queryNAMETYPE VALU

2、Equery_rewrite_enabledstring TRUEquery_rewrite_integritystring TRUSTEDSQL conn / as sysdba已连接。SQL create user ning identified by ning;用户已创建。SQL grant dba to ning;授权成功。SQL conn ning/ning已连接。本文采用一个简单的星型模型的示例数据，只包含一个事实表fact_sales和一个时间维度表 time_dim，具体的生成脚本请见附录。二、物化视图通常，在数据仓库中可以通过创建摘要信息(summary)来提升性能。这里的摘

3、要指的是预先对一些连接(join)和聚合(aggregation)进行计算并将结果保存下来，后续查询的时候可以直接利用保存的摘要信息来生成报表。在oracle中，可以利用物化视图(materialized view) 来创建数据仓库中的摘要。结合 oracle优化器的查询重写(query rewrite) 功能，可以Oracle提供了两种刷新方式：增量刷新(fast refresh )和完全刷在不改写应用的情况下，利用物化视图提升查询性能。显然，物化视图需要一种刷新机制来保证和基表的数据同步,新(comp lete refresh)。增量刷新方式需要满足一系列的条件(具体的限制条件请参考Met

4、alink : Doc ID: Note:222843.1)，简单起见，本文例子中的物化视图采用了完全刷新方式。假如我们要得到每月的销售总量统计，可以执行以下查询:SQL set autot trace expSQL select t.t_month, sum(f.amount1),sum(f.amount2)2 from time_dim t,fact_sales f3 where t.time id=f.time id4 group by t.t_month;已选择33行。执行计划Plan hash value: 53462861| Id | Op eration| Name | Rows

5、 | Bytes | Cost (%C PU)| Time |0 | SELECT STATEMENT | 1000 | 61000 |9 (23)| 00:00:01 |HASH GROU P BY | 1000 | 61000 |9 (23)| 00:00:01 |HASH JOIN| 1000 | 61000 |8 (13)| 00:00:01 |TABLE ACCESS FULL| TIME_DIM | 1000 | 22000 |4 (0)| 00:00:01 |TABLE ACCESS FULL| FACT_SALES| 1000 | 39000 | 3 (0)| 00:00:01

6、 |创建一个月度统计的物化视图:SQL create materialized view mv month2 refresh compi ete3 enable query rewrite4 as5 select t.t_month, sum(f.amount1),sum(f.amount2)6 from time_dim t,fact_sales f7 where t.time id=f.time id8 group by t.t_month;实体化视图已创建。再次执行相同的查询，发现执行计划已经改变，优化器自动使用刚才建立的物化视图代替两张基表的查询:SQL select t.t_mont

7、h, sum(f.amount1),sum(f.amount2)2 from time_dim t,fact_sales f3 where t.time id=f.time id4 group by t.t_month;已选择33行。执行计划Plan hash value: 3083828679| Id | Op eration | Name | Rows | Bytes | Cost (%C PU)| Time | 0 | SELECT STATEMENT | | 33 | 1155 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL

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

9、用。在上一个物化视图的例子中，我们通过建立一个月度聚合的物化视图，使得月度统计的SQL能够通过查询重写从物化视图中受益。但是，如果我们想按季度统计信息，则无法利用到该物化视图:SQL select t.t_quarter, sum(f.amount1),sum(f.amount2)from time_dim t,fact_sales fwhere t.time id=f.time idgroup by t.t_quarter;执行计划Plan hash value: 53462861| Id | Op eration| Name | Rows | Bytes | Cost (%C PU)| T

10、ime | 0 | SELECT STATEMENT | 1000 | 60000 | 9 (23)1 00:00:01 |HASH GROU P BY | 1000 | 60000 | 9 (23)| 00:00:01 |I* 2 |HASH JOIN | 1000 | 60000 |8 (13)| 00:00:01 |(0)| 00:00:01 |3 (0)| 00:00:01 |TABLE ACCESS FULL| TIME_DIM | 1000 | 21000 | 4TABLE ACCESS FULL| FACT_SALES | 1000 | 39000 |创建一个Dimension，

11、指定维度表中各level之间的层次关系:SQL create dimension time dim2 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_day8 day child of9 month child of10 quarter child of11 year12 );维已创建。重新执行查询，注意quer

12、y_rewrite_ in tegrity参数设置为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 f3 where t.time id=f.time id4 group by t.t_quarter;执行计划Plan hash value: 3478386927| Id | Op era

13、tion | Name | Rows | Bytes | Cost (%C PU)| Time | 0 | SELECT STATEMENT | | 11 | 330 | 11 (28)| 00:00:01 | | 1 | HASH GROU P BY | | 11 | 330 | 11 (28)| 00:00:01 | |*2 | HASH JOIN I I 257 | 7710 | 10 (20)| 00:00:01 | | 3| MAT_VIEW REWRITE ACCESS FULL| MV_MONTH |33 |561|3 (0)| 00:00:01| | 4 | VIEW | |

14、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-innerjoin)。对于数据仓库而言，较普遍的是Fact table的外键列和相关的Dimension table的主键列的等于连接操作。位图连接索引能够消

15、除查询中的连接操作，因为它实际上已经将连接的结果保存在索引当中了。而且，相对于在表的连接列上建普通位图索引来说，位图连接索引需要更少的存储空间。物化视图也可以用来消除连接操作，但位图连接索引比起物化视图来更有效率，因为通过位图连接索引可以直接将基于索引列的查询对应到事实表的 rowid。一个连接事实表和维度表的查询:SQL select f.amount1,f.amount22 from fact_sales f,time_dim t3 where t.time id=f.time id4 and t.t_day=2007-01-01;执行计划Plan hash value: 10802130

16、47| Id | Op eration | Name | Rows | Bytes | Cost (%C PU)| Time | 0 | SELECT STATEMENT | | 1 | 22 | 9 (12)| 00:00:01 | |*1 | HASH JOIN I I 1 | 22 | 9 (12)| 00:00:01 |I* 2 I TABLE ACCESS FULL| TIME_DIM | 1 | 13 | 5 (0)| 00:00:01 |I 3 | TABLE ACCESS FULL| FACT_SALES | 1000 | 9000 | 3 (0)| 00:00:01 |创建连

17、接时间维度表和销售事实表之间的位图连接索引:SQL create bitma p index ix_sales_time2 on fact_sales(time_dim.t_day)3 from fact_sales,time_dim4 where fact_sales.time_id=time_dim.time_id;索引已创建。再次执行同样的查询，可以发现，通过位图连接索引，无须再对time_dim和fact_salces表进行连接操作，直接通过位图连接索引，访问fact_slaes 表即可得到结果:SQL select f.amount1,f.amount22 from fact_sales f,time_dim t3 where t.time id=f.time id4 and t.t_day=2007-01-01;执行计划Plan hash value: 1533750321I Id I Op eration | Name | Ro