Oracle数据仓库查询优化技术-技术开发专区

Oracle数据仓库查询优化技术

作者：IT168 宁海元(NinGoo) 编辑：晓熊 2007-02-27 10:11

【IT168技术文档】

[文章摘要] ：Oracle在数据仓库的性能优化方面，提供了很多的特性和工具，除了常规的分区、并行以及位图索引，还有物化视图、Dimension，位图连接索引等。利用这些特性，可以在不改写应用的情况下，由DBA在数据库层面来进行查询性能优化。本文通过几个具体的例子，简单演示了利用物化视图等技术进行查询优化的方法。

[关键字]
数据库数据仓库物化视图查询重写 Dimension 位图连接索引

一、运行环境

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show parameter query
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                      string      TRUE
query_rewrite_integrity                     string       TRUSTED

SQL> conn / as sysdba
已连接。
SQL> create user ning identified by ning;
用户已创建。

SQL> grant dba to ning;
授权成功。

SQL> conn ning/ning
已连接。

本文采用一个简单的星型模型的示例数据，只包含一个事实表fact_sales和一个时间维度表time_dim，具体的生成脚本请见附录。
二、物化视图
通常，在数据仓库中可以通过创建摘要信息(summary)来提升性能。这里的摘要指的是预先对一些连接(join)和聚合(aggregation)进行计算并将结果保存下来，后续查询的时候可以直接利用保存的摘要信息来生成报表。在oracle中，可以利用物化视图(materialized view)来创建数据仓库中的摘要。结合oracle优化器的查询重写(query rewrite)功能，可以在不改写应用的情况下，利用物化视图提升查询性能。显然，物化视图需要一种刷新机制来保证和基表的数据同步，Oracle提供了两种刷新方式：增量刷新（fast refresh）和完全刷新(complete refresh)。增量刷新方式需要满足一系列的条件（具体的限制条件请参考Metalink：Doc ID: Note:222843.1），简单起见，本文例子中的物化视图采用了完全刷新方式。

假如我们要得到每月的销售总量统计，可以执行以下查询：

SQL> set autot trace exp
SQL> select t.t_month, 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_month;
已选择33行。
执行计划
----------------------------------------------------------
Plan hash value: 53462861
----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |  1000 | 61000 |      9  (23)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |  1000 | 61000 |     9  (23)| 00:00:01 |
|*  2 |   HASH JOIN          |           |  1000 | 61000 |     8  (13)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TIME_DIM | 1000 | 22000 |    4   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| FACT_SALES| 1000 | 39000 |   3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

创建一个月度统计的物化视图：

SQL> create materialized view mv_month
2 refresh complete
3 enable query rewrite
4 as
5 select t.t_month, sum(f.amount1),sum(f.amount2)
6 from time_dim t,fact_sales f
7 where t.time_id=f.time_id
8 group by t.t_month;

实体化视图已创建。

再次执行相同的查询，发现执行计划已经改变，优化器自动使用刚才建立的物化视图代替两张基表的查询：

SQL> select t.t_month, 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_month;

已选择33行。
执行计划
---------------------------------------------------------- 
Plan hash value: 3083828679
----------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 33 | 1155 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_MONTH | 33 |1155 |3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

三、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 |
----------------------------------------------------------------------------------

四、位图连接索引
位图连接索引（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性能优化的不二法门。

关注我们