创建一个表，含有位图index和b-tree index

SQL> create table t as select object_id id ,object_name from dba_objects;
Table created.
SQL> create index b_tree_ind on t(id);
Index created.

SQL> create bitmap index b_bm_name on t(object_name);
Index created.

SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=> true);
SELE
PL/SQL procedure successfully completed.

SQL> set autotrace trace exp stat

SQL> select id from t;
50365 rows selected.
Execution Plan
----------------------
Plan hash value: 1601196873
--------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------
|?? 0 | SELECT STATEMENT? |????? | 47448 |?? 602K|??? 57?? (2)| 00:00:01 |
|?? 1 |? TABLE ACCESS FULL| T??? | 47448 |?? 602K|??? 57?? (2)| 00:00:01 |
--------------------------------------
Note
-----
?? - dynamic sampling used for this statement
Statistics
----------------------
???????? 28? recursive calls
????????? 0? db block gets
?????? 3646? consistent gets
????????? 0? physical reads
????????? 0? redo size
???? 728900? bytes sent via SQL*Net to client
????? 37312? bytes received via SQL*Net from client
?????? 3359? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????? 50365? rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，预料之中事件

SQL> select /*+ index(t b_tree_ind) */ id from t
50365 rows selected.
Execution Plan
----------------------
Plan hash value: 3465251059
------------------------------------------------------
| Id? | Operation??????????????????? | Name????? | Rows? | Bytes | Cost (%CPU)| Time???? |
------------------------------------------------------
|?? 0 | SELECT STATEMENT???????????? |?????????? | 47448 |?? 602K|?? 805?? (1)| 00:00:10 |
|?? 1 |? TABLE ACCESS BY INDEX ROWID | T???????? | 47448 |?? 602K|?? 805?? (1)| 00:00:10 |
|?? 2 |?? BITMAP CONVERSION TO ROWIDS|?????????? |?????? |?????? |??????????? |????????? |
|?? 3 |??? BITMAP INDEX FULL SCAN??? | B_BM_NAME |?????? |?????? |??????????? |????????? |
------------------------------------------------------
Note
-----
?? - dynamic sampling used for this statement
Statistics
----------------------
????????? 0? recursive calls
????????? 0? db block gets
????? 27318? consistent gets
????????? 0? physical reads
????????? 0? redo size
??? 2147500? bytes sent via SQL*Net to client
????? 37312? bytes received via SQL*Net from client
?????? 3359? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????? 50365? rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，这里的疑惑是：
就算不会使用b_tree_t_xifenfei index也不应该会使用BITMAP_T_XIFENFEI index，因为使用这个的cost会大于全表扫描

SQL> select /*+ index(t aaa) */ id from t;
50365 rows selected.
Execution Plan
----------------------
Plan hash value: 3465251059
------------------------------------------------------
| Id? | Operation??????????????????? | Name????? | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------