设为首页收藏本站
开启辅助访问 切换到窄版
搜索
第一问答网»论坛 › 中问网 › 问答 › 如何理解这句话，bitmap index？

如何理解这句话，bitmap index？

[复制链接]
查看11 | 回复5 | 2011-11-16 22:31:22 | 显示全部楼层 |阅读模式
[php]
Bitmap indexing efficiently merges indexes that correspond to several
conditions in a WHERE clause. Rows that satisfy some, but not all,
conditions are filtered out before the table itself is accessed. This
improves response time, often dramatically.
.
[/php]
回复

使用道具 举报

千问 | 2011-11-16 22:31:22 | 显示全部楼层
bitmap index对于and/or之类的条件，可以先通过index本身进行and/or操作进行过滤
回复

使用道具 举报

千问 | 2011-11-16 22:31:22 | 显示全部楼层
最初由 NinGoo 发布
[B]bitmap index对于and/or之类的条件，可以先通过index本身进行and/or操作进行过滤 [/B]
好像也没看出什么来，反而index也没有使用,方便的话给来个例子吧！
[PHP]
XYStest >create table tt as select *from dba_objects;
表已创建。
XYStest >create bitmap index idx_t_map on tt(owner);
索引已创建。
XYStest >explain plan for select owner , object_id , object_name from tt where
owner='SYS' OR object_id=9999;
已解释。
XYStest >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT||6746 | 632K|32 (4)| 00:00:01 |
|*1 |TABLE ACCESS FULL| TT |6746 | 632K|32 (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("OWNER"='SYS' OR "OBJECT_ID"=9999)
Note
-----
- dynamic sampling used for this statement
已选择17行。
XYStest >analyze table tt compute statistics;
表已分析。
XYStest >explain plan for select owner , object_id , object_name from tt where
owner='SYS' OR object_id=9999;
已解释。
XYStest >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT||1239 | 30975 |32 (4)| 00:00:01 |
|*1 |TABLE ACCESS FULL| TT |1239 | 30975 |32 (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("OWNER"='SYS' OR "OBJECT_ID"=9999)
已选择13行。
XYStest >explain plan for select owner , object_id , object_name from tt where
owner='SYS' and object_id=9999;
已解释。
XYStest >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 1 |25 |32 (4)| 00:00:01 |
|*1 |TABLE ACCESS FULL| TT | 1 |25 |32 (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("OBJECT_ID"=9999 AND "OWNER"='SYS')
已选择13行。
XYStest >explain plan for select owner , object_id , object_name from tt where
owner='XYS' OR object_id=9999;
已解释。
XYStest >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT||1239 | 30975 |32 (4)| 00:00:01 |
|*1 |TABLE ACCESS FULL| TT |1239 | 30975 |32 (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("OWNER"='XYS' OR "OBJECT_ID"=9999)
已选择13行。
XYStest >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3934611468
--------------------------------------------------------------------------------
----------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 1 |25 |67 (0)|
00:00:01 |
|*1 |TABLE ACCESS BY INDEX ROWID | TT| 1 |25 |67 (0)|
00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | |
|
|
|*3 |BITMAP INDEX SINGLE VALUE | IDX_T_MAP | | |
|
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9999)
3 - access("OWNER"='XYS')
已选择16行。
XYStest >explain plan for select /*+ INDEX(TT IDX_T_MAP) */ owner , object_id
, object_name from tt where owner='XYS' and object_id=9999;
已解释。
XYStest >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3934611468
--------------------------------------------------------------------------------
----------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 1 |25 |67 (0)|
00:00:01 |
|*1 |TABLE ACCESS BY INDEX ROWID | TT| 1 |25 |67 (0)|
00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | |
|
|
|*3 |BITMAP INDEX SINGLE VALUE | IDX_T_MAP | | |
|
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=9999)
3 - access("OWNER"='XYS')
已选择16行。
XYStest >
XYStest >
[/PHP]
回复

使用道具 举报

千问 | 2011-11-16 22:31:22 | 显示全部楼层
bitmap index merge指的是多个bitmap index对于and/or操作可以通过bitmap and/or操作进行合并过滤，然后再根据需要回访表，也就是and/or的条件过滤是通过多个bitmap index的合并操作来进行的，由于是位与和位或等操作，所以效率较高
一个不太恰当的例子
[php]
SQL> create table t as select object_id,object_type,object_name from all_objects;
Table created.
SQL> create bitmap index ix_t_type on t(object_type);
Index created.
SQL> create bitmap index ix_t_id on t(object_id);
Index created.
SQL> set autot trace exp
SQL> select * from t where object_id create table t as select object_id,object_type,object_name from all_objects;
Table created.
SQL> create bitmap index ix_t_type on t(object_type);
Index created.
SQL> create bitmap index ix_t_id on t(object_id);
Index created.
SQL> set autot trace exp
SQL> select * from t where object_id create table t as select object_id,object_type,object_name from all_objects;
Table created.
SQL> create bitmap index ix_t_type on t(object_type);
Index created.
SQL> create bitmap index ix_t_id on t(object_id);
Index created.
SQL> set autot trace exp
SQL> select * from t where object_id<1000 and object_type='TABLE';
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=17 Bytes=69

7)
10 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=8 Card=

17 Bytes=697)
21 BITMAP CONVERSION (TO ROWIDS)
32 BITMAP AND
43 BITMAP INDEX (SINGLE VALUE) OF 'IX_T_TYPE' (INDEX (B

ITMAP))
53 BITMAP MERGE
65 BITMAP INDEX (RANGE SCAN) OF 'IX_T_ID' (INDEX (BIT

MAP))
----------------------------------------------------------------------------------

注意到执行计划中的BITMAP AND和BITMAP MERGE操作
我把USERS_TABLE 换成DBA_TABLE 做出来的结果是 FULL-TABLE-SCAN , 觉得很奇怪,BITMAP反而没用索引.数据库版本是9.2.0.8 ,跟设置的参数有关系吗
回复

使用道具 举报

千问 | 2011-11-16 22:31:22 | 显示全部楼层
简单的说,bitmap index对于统计与分析系统来说效率会高些,对oltp系统就没什么用
回复

使用道具 举报

千问 | 2011-11-16 22:31:22 | 显示全部楼层
楼上说的没错
回复

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

千问

主题

0

回帖

4882万

积分

论坛元老

Rank: 8Rank: 8

积分
48824836
回复楼主 返回列表
热门排行