Exemplo de como um índice pode não otimizar uma consulta no SQL

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)