Oracle数据仓库查询优化技术

核心提示： 一个连接事实表和维度表的查询：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';执行计划

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

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性能优化的不二法门。