如何用btree 和 bitmap 来做统计
我有一个表,想找出里面单个交易transaction 里面都是A,B,C,D的交易的数量transaction item
1 A
1 B
1 D
1 E
1 F
2 D
2 E
2 C
2 A
3 B
3 C
3 E
..... ......我创建了两个索引:create index btreeindex on t2(transaction);create bitmap index bitmapindex on t2(item) ; 然后执行以下语句:select /*+ index_combine(t2, btreeindex, bitmapindex) */ count(count(*)) from t2
where item = 'A' or item = 'B' or item = 'C' or item = 'D'
group by transaction
having count(*) = 4
;统计满足条件交易数量执行计划如下:
| 0 | SELECT STATEMENT
|* 1 | FILTER |
| 2 | HASH GROUP BY |
| 3 | INLIST ITERATOR | |
| 4 | TABLE ACCESS BY INDEX ROWID | T2
| 5 | BITMAP CONVERSION TO ROWIDS| | | |
|* 6 | BITMAP INDEX SINGLE VALUE | BITMAPINDEX | | |只用到 bitmap index , 并没有用到btree index, 这句"TABLE ACCESS BY INDEX ROWID "要读表我想把整个搜索全走索引,不扫表,请问有办法做到吗?我测试了另外一种写法:select /*+index_combine(t2, compressindex, bitmapindex) */ count(transaction) from t2
where item = 'A' and transaction = 3这个可以全走索引, 但比较SB, 无法做到统计.请教各位牛人了.
我有一个表,想找出里面单个交易transaction 里面都是A,B,C,D的交易的数量transaction item
1 A
1 B
1 D
1 E
1 F
2 D
2 E
2 C
2 A
3 B
3 C
3 E
..... ......我创建了两个索引:create index btreeindex on t2(transaction);create bitmap index bitmapindex on t2(item) ; 然后执行以下语句:select /*+ index_combine(t2, btreeindex, bitmapindex) */ count(count(*)) from t2
where item = 'A' or item = 'B' or item = 'C' or item = 'D'
group by transaction
having count(*) = 4
;统计满足条件交易数量执行计划如下:
| 0 | SELECT STATEMENT
|* 1 | FILTER |
| 2 | HASH GROUP BY |
| 3 | INLIST ITERATOR | |
| 4 | TABLE ACCESS BY INDEX ROWID | T2
| 5 | BITMAP CONVERSION TO ROWIDS| | | |
|* 6 | BITMAP INDEX SINGLE VALUE | BITMAPINDEX | | |只用到 bitmap index , 并没有用到btree index, 这句"TABLE ACCESS BY INDEX ROWID "要读表我想把整个搜索全走索引,不扫表,请问有办法做到吗?我测试了另外一种写法:select /*+index_combine(t2, compressindex, bitmapindex) */ count(transaction) from t2
where item = 'A' and transaction = 3这个可以全走索引, 但比较SB, 无法做到统计.请教各位牛人了.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货