BITMAP索引的INLIST ITERATOR与BITMAP OR

最新推荐文章于 2023-10-16 23:53:30 发布

ckawt40802

最新推荐文章于 2023-10-16 23:53:30 发布

阅读量130

点赞数

文章标签：数据库

看文档时发现了BITMAP索引也存在INLIST ITERATOR的访问路径。

当查询BITMAP索引列时指定了IN表达式，Oracle会选择BITMAP索引的INLIST ITERATOR方式扫描，刚看到这里还以为文档有问题，因为IN (A, B, C)等价于 = A R = B R = C，既然如此利用BITMAP OR操作不就可以了，为什么还会出现INLIST ITERATOR访问方式呢。

做了个简单的测试：

SQL> create table t_bitmap (id number, name varchar2(30), type number(1));

表已创建。

SQL> insert into t_bitmap select rownum, object_name, mod(rownum, 3) from user_objects;

已创建13809行。

SQL> commit;

提交完成。

SQL> update t_bitmap set type = 3 where rownum < 10;

已更新9行。

SQL> update t_bitmap set type = 4 where type != 3 and rownum < 5;

已更新4行。

SQL> commit;

提交完成。

SQL> create bitmap index ind_b_t_type on t_bitmap(type);

索引已创建。

SQL> set autot on
SQL> select * from t_bitmap where type in (3, 4);

        ID NAME                                 TYPE
---------- ------------------------------ ----------
      1784 T_PART_INTER                            3
      1785 T_PART_INTER                            3
      1786 T_PART_INTER                            3
      1787 T_PART_INTER                            3
      1788 T_PART_INTER                            3
      1789 T_PART_INTER                            3
      1790 T_PART_INTER                            3
      1791 T_PART_INTER                            3
      1792 T_PART_INTER                            3
      1793 T_PART_INTER                            4
      1794 T_PART_INTER                            4
      1795 T_PART_INTER                            4
      1796 T_PART_INTER                            4

已选择13行。

执行计划
----------------------------------------------------------
Plan hash value: 695458996

--------------------------------------------------------------------------------------------
| Id | Operation                     | Name         | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT              |              |    13 |   559 |     8   (0)| 00:00:01 |
| 1 | INLIST ITERATOR              |              |       |       |            |          |
| 2 |   TABLE ACCESS BY INDEX ROWID | T_BITMAP     |    13 |   559 |     8   (0)| 00:00:01 |
| 3 |    BITMAP CONVERSION TO ROWIDS|              |       |       |            |          |
|* 4 |     BITMAP INDEX SINGLE VALUE | IND_B_T_TYPE |       |       |            |          |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("TYPE"=3 OR "TYPE"=4)

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
          5 recursive calls
          0 db block gets
         33 consistent gets
          0 physical reads
          0 redo size
       1024 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         13 rows processed

SQL> select * from t_bitmap where type = 3 or type = 4;

已选择13行。

执行计划
----------------------------------------------------------
Plan hash value: 695458996

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("TYPE"=3 OR "TYPE"=4)

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
          4 recursive calls
          0 db block gets
         33 consistent gets
          0 physical reads
          0 redo size
       1024 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         13 rows processed

显然INLIST ITERATOR执行计划与OR和IN的写法没有关系。其实BITMAP OR操作是针对两个不同BITMAP索引执行的操作，而当前虽然TYPE存在多个值但是这些值属于同一列，因此只需要访问一个索引就可以了，显然Oracle不再需要额外的空间去进行BITMAP OR操作，只需要对当前索引迭代扫描就可以了。

SQL> create bitmap index ind_b_t_name on t_bitmap (name);

索引已创建。

SQL> select * from t_bitmap where type = 4 or name = 'T';

        ID NAME                                 TYPE
---------- ------------------------------ ----------
      1793 T_PART_INTER                            4
      1794 T_PART_INTER                            4
      1795 T_PART_INTER                            4
      1796 T_PART_INTER                            4
     11875 T                                       1

执行计划
----------------------------------------------------------
Plan hash value: 1534953101

--------------------------------------------------------------------------------------------
| Id | Operation                    | Name         | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     5 |   215 |     8   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID | T_BITMAP     |     5 |   215 |     8   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|              |       |       |            |          |
|   3 |    BITMAP OR                 |              |       |       |            |          |
|* 4 |     BITMAP INDEX SINGLE VALUE| IND_B_T_TYPE |       |       |            |          |
|* 5 |     BITMAP INDEX SINGLE VALUE| IND_B_T_NAME |       |       |            |          |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("TYPE"=4)
5 - access("NAME"='T')

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
         40 recursive calls
          0 db block gets
         36 consistent gets
          0 physical reads
          0 redo size
        814 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          5 rows processed

只有存在多个BITMAP索引的时候才会使用BITMAP OR操作来合并索引。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-676588/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-676588/

ckawt40802

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
BITMAP索引的INLIST ITERATOR与BITMAP OR

看文档时发现了BITMAP索引也存在INLIST ITERATOR的访问路径。当查询BITMAP索引列时指定了IN表达式，Oracle会选择BITMAP索引的INLIST ITERATOR方式扫描，刚看到这里还以...
复制链接

扫一扫