Oracle 星型查询转换实践

一.简介

星型查询是一种业务的应用，星型查询转换的核心思想是将原本的星型连接查询重写成更高效的形式。有些业务刚好满足如下的结构，我们可以使用星型查询。

1. 子查询转换：将针对维度表的过滤条件转化为对事实表的子查询，这样可以避免直接连接到维度表，减少I/O和CPU消耗。
2. 位图索引使用：在事实表的外键列上创建位图索引（Bitmap Indexes）。位图索引非常适合存储和检索少量的值，它们在处理大量行但少量唯一值的列时特别有效。
3. 隐式重写SQL语句：优化器在执行查询时会自动识别是否可以应用星型转换，并对SQL语句进行隐式的重写，以利用位图索引和子查询转换。

二.结构

星型查询的基本结构是一张表和多张表进行连接，也就是一对多的关系，为了区分这些表，我们把一对多中的一叫做 事实表 ， 把一对多中的多叫做 维度表。

三.举例

1.配置数据库支持星型查询

alter system set star_transformation_enabled = TRUE;

为什么默认是关闭的？  --如果默认打开的话，每个语句CBO都会考虑要不要使用星型查询，成本是比较大的，所以在生产环境中如果想要使用星型查询，不建议在实例级打开，建议在会话级别打开。

alter session set  star_transformation_enabled=true;

2.sh用户中sales表和customers表结构如下

3.创建本地分区位图索引

create bitmap index ind_sales_time_id on sh.sales(time_id) local;
create bitmap index ind_sales_channel_id on sh.sales(channel_id) local;
create bitmap index ind_sales_cust_id on sh.sales(cust_id) local;

4.查看执行计划

set autotrace traceonly

SELECT ch.channel_class,c.cust_city,t.calendar_quarter_desc,SUM (s.amount_sold) sales_amount
FROM sales s,times t,customers c,channels ch
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id AND c.cust_state_province = 'CA'
AND ch.channel_desc IN ('Internet', 'Catalog')
AND t.calendar_quarter_desc IN ('1999-01','1999-02','2000-03','2000-
04')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

可以看到产生了大量临时表。

5.优化临时表，建位图连接索引

CREATE BITMAP INDEX sales_c_state_bjix ON
 sales(customers.cust_state_province) 
 FROM sales, customers 
 WHERE sales.cust_id=customers.cust_id
 LOCAL NOLOGGING COMPUTE STATISTICS;

6.再次查看执行计划

临时表消失，实验结束。