想试验一下 index_combine这个hint，于是做了如下试验。
1.创建一个具有若干index的表
SQL> create table test as select object_id,object_type,status from dba_objects; Table created. SQL> create index tobject_id on test(object_id); Index created. SQL> create index tobject_type on test(object_type); Index created. 2.加 index_combin hint 来看执行计划 SQL> explain plan for select /*+ index_combine(test tobject_id tobject_type) */ * from test where object_id>5000 and object_type='INDEX'; Explained. 在看执行计划之前，我以为index_combine会这样处理这条语句
1.把object_id>500 用索引范围扫描，然后用bitmap convert to rowid 转化成位图索引
2.把object_type=index 也用bitmap convert to rowid 转化成位图索引
3.combine 上面的两个结果 但没想到查询优化器采用的方式居然是全表扫描，如下： --------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4071 | 115K| 48 (11)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 4071 | 115K| 48 (11)| 00:00:01 |
-------------------------------------------------------------------------- 根据官网对index_combine的说法
The INDEX_COMBINE hint instructs the optimizer to use a bitmap access path for the table. If indexspec is omitted from the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of indexes has the best cost estimate for the table. If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. 我指定了 indexspec，那么查询优化器应该在我指定的索引 列表中选取合适的组合啊，怎么变成全表扫描了 3.我试着把object_type这列的索引由B-tree索引改成了位图索引，hint立马就生效了。 难道说这个hint要求指定的索引列表中必须有位图索引吗？ 不应该吧？ SQL> drop index tobject_type; Index dropped. SQL> create bitmap index tobject_type on test(object_type); Index created. SQL> explain plan for select /*+ index_combine(test tobject_id tobject_type) */ * from test where object_id>5000 and object_type='INDEX'; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1817610418 -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4071 | 115K| 157 (4)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 4071 | 115K| 157 (4)| 00:00:02 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | TOBJECT_TYPE | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS| | | | | | PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | SORT ORDER BY | | | | | |
|* 7 | INDEX RANGE SCAN | TOBJECT_ID | | | 122 (4)| 00:00:02 |
-------------------------------------------------------------------------------------------------