BITMAP索引的INLIST ITERATOR與BITMAP OR
看文件時發現了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;
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)
統計資訊
----------------------------------------------------------
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Bitmap Index】B-Tree索引與Bitmap點陣圖索引的鎖代價比較研究Index索引
- BITMAP索引異常增大索引
- 點陣圖索引（Bitmap Index）——索引共用索引Index
- [轉]：bitmap索引和B*tree索引分析索引
- Bitmap的分析與使用
- 【索引】Bitmap點陣圖索引與普通的B-Tree索引鎖的比較索引
- 索引組織表上建立BITMAP索引（三）索引
- 索引組織表上建立BITMAP索引（二）索引
- 索引組織表上建立BITMAP索引（一）索引
- oracle 點陣圖索引（bitmap index）Oracle索引Index
- 點陣圖索引:原理（BitMap index）索引Index
- 點陣圖索引（bitmap-index）索引Index
- 物化檢視上使用bitmap索引索引
- 分割槽表的bitmap索引不能是global的索引
- bitmap index點陣圖索引系列(一)Index索引
- bitmap
- B-Tree索引與Bitmap點陣圖索引的鎖代價比較研究索引
- 點陣圖索引（Bitmap Index）——點陣圖索引與資料DML鎖定索引Index
- 關於INLIST ITERATOR的幾個測試
- BitMap與RoaringBitmap、JavaEWAHJava
- Jedis bitmap
- Bitmap IndexIndex
- Bitmap的載入與快取策略快取
- android中Bitmap的剪下與拉伸Android
- Bitmap的問題
- 關於Inlist iterator操作的一點認識
- 點陣圖索引（Bitmap Index）——從B*樹索引到點陣圖索引索引Index
- BitMap介紹
- Redis-BitMapRedis
- 淺談BitMap
- bitmap join indexIndex
- CREATE BITMAP INDEXIndex
- Bitmap的有關講解與優化優化
- zt_深入理解bitmap index點陣圖索引Index索引
- Android中的BitmapAndroid
- Clickhouse的bitmap函式函式
- 核心中bitmap的使用
- 關於B*tree索引(index)的中度理解及bitmap 索引的一點探究(zt)索引Index