Um índice nem sempre otimiza uma consulta
Vejam abaixo um exemplo de que nem sempre um índice otimiza uma consulta:
SQL> ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
SQL>explain plan for
SELECT * FROM hr.employees e WHERE (manager_id = 108) OR (department_id = 110);
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 426 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 6 | 426|2 (0)| 00:00:01|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MANAGER_ID"=108 OR "DEPARTMENT_ID"=110)
SQL>ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SQL> explain plan for
SELECT * FROM hr.employees e WHERE (manager_id = 108) OR (department_id = 110);
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 426 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 426 | 13 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | EMP_MANAGER_IX | | | 4 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("MANAGER_ID"=108)
7 - access("DEPARTMENT_ID"=110)