SQL> create table test as select object_id,object_type,status from dba_objects where 1=0;
Table created.
SQL> create index tobject_id on test(object_id);
Index created.
SQL> create index tobject_type on test(object_type);
Index created.
SQL> set autotrace on
SQL> select /*+ index_combine(test tobject_id tobject_type) */ * from test where object_id>5000 and object_type='INDEX';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2953102736
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 1 | 29 | 4
(25)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 29 | 4
(25)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | |
| |
| 3 | BITMAP AND | | | |
| |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | |
| |
|* 5 | INDEX RANGE SCAN | TOBJECT_TYPE | | | 1
(0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | |
| |
| 7 | SORT ORDER BY | | | |
| |
|* 8 | INDEX RANGE SCAN | TOBJECT_ID | | | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_TYPE"='INDEX')
8 - access("OBJECT_ID">5000)
filter("OBJECT_ID">5000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed