执行计划出现BITMAP CONVERSION TO ROWIDS的解释!

最新推荐文章于 2021-04-15 08:57:19 发布

cl2689163935

最新推荐文章于 2021-04-15 08:57:19 发布

阅读量461

点赞数

文章标签：数据库 php 开发工具

我在查看执行计划的时候发现BITMAP CONVERSION TO ROWIDS,好像sql反应比较慢,baidu了一下,有下面的解释!

我的解决办法是: 把索引删除,建立了联合索引,就没出现这个问题了,一致性获取也下降了很多,速度也快了不少!

不知道oracle用这个来究竟是有什么用!

http://www.itpub.net/showthread.php?s=&threadid=602866&perpage=10&pagenumber=1

都是cbo的context和算法决定的，可以试试false _b_tree_bitmap_plans，我说错了这个不应该是join是combine

给一段Jonathan Lewis的解释：
B-tree to Bitmap Conversions
One of the optimizer’s strategies is to range scan B-tree indexes to acquire lists of rowids, convert
the lists of rowids into the equivalent bitmaps, and perform bitwise operations to identify a
small set of rows. Effectively, the optimizer can take sets of rowids from index range scans and
convert them to bitmap indexes on the fly before doing an index_combine on the resulting
bitmap indexes.
In 8i, only tables with existing bitmap indexes could be subject to this treatment, unless
the parameter _b_tree_bitmap_plans had been set to relax the requirement for a preexisting
bitmap index.
In 9i, the default value for this parameter changed from false to true—so you may see
execution plans involving bitmap conversions after you’ve upgraded, even though you don’t
have a single bitmap index in your database. Unfortunately, because of the implicit packing
assumption that the optimizer uses for bitmap indexes, this will sometimes be a very bad idea.
As a related issue, this change can make it worth using the minimize_records_per_block
option on all your important tables.

其实，Lewis也给出了自己的疑问，认为将B树索引转换为除了在可选的方法里多了一种以外，不一定还有其他好处，甚至转换需要多消耗CPU资源。
呵呵，从我看来，这个功能好像不咋地。

有关bitwise，oracledba已经说得很详细了：
两个索引，分别转化为位图，满足各自条件的设置位为1，如果两个索引的位都为1，则rowid保留
上面这个过程就叫做bitwise operation.
BTW 查金山词霸，可以知道
bitwise logical operator
位逻辑运算

http://www.orawh.com/68.html

and_equal,index_join,index_combine
Published by wanghai六月 22, 2006 in Oracle.
and_equal,index_join,index_combine这三种都是oracle利用索引关联获得数据的方法,三者的目的都是为了最大限度的利用索引,减少回表的代价.但是三者的实现方法是有区别的,下面一一来分析.

and_equal:
这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据.and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是”=”.在10g中,and_equal已经被废弃了,只能通过hint才能生效.

create table test as select * from dba_objects;

create index ind_test_owner on test(owner);

create index ind_test_object_name on test(object_name);

SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where owner=’test’ and object_name=’test’;