而为了提高查询速度, 根据数据特征, key1,key2,key3,key4这四个字段会比较适合每个字段上建一个位图索引(Bitmap Index), 但是上面的查询语句并不能用到位图索引, 除非是进行了星型转换, 这个转换需要将SQL转换成下面的格式:
SELECT
D1.xxx, D2.xxx, D3.xxx, D4.xxx,
SUM(F.xxx), SUM(F.xxx)
FROM F, D1, D2, D3, D4
WHERE F.KEY1=D1.KEY1 AND F.KEY2=D2.KEY2
AND F.KEY3=D3.KEY3 AND F.KEY4=D4.KEY4
AND D1.xxx=? AND D2.xxx=?
AND D3.xxx=? AND D4.xxx=?
AND F.KEY1 IN (SELECT D1.KEY1 FROM D1 WHERE D1.xxx=?)
AND F.KEY2 IN (SELECT D2.KEY2 FROM D2 WHERE D2.xxx=?)
AND F.KEY3 IN (SELECT D3.KEY3 FROM D3 WHERE D3.xxx=?)
AND F.KEY4 IN (SELECT D4.KEY4 FROM D4 WHERE D4.xxx=?)
GROUP BY D1.xxx, D2.xxx, D3.xxx, D4.xxx
有条件的可以试试, 使用星型转换有三个条件:
◆事实表的每个代码列上有位图索引
◆参数STAR_TRANSFORMATION_ENABLED的值要设为
文章来源于领测软件测试网 https://www.ltesting.net/
