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

阅览次数:
文章来源: cp整理
原文作者: 不详
整理日期: 2007-03-29
发表评论

字体大小:
小
中
大

一、运行环境

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 |

----------------------------------------------------------------------------

[1] [2]

上一篇:ORACLE 11g新特性简介
下一篇:我们应该在哪个层次编写代码？

推荐文章

热点文章

相关文章

百度搜索

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

网友评论: