For example:

SQL> DROP INDEX MYEMP_ENAME_INDX;

Index dropped.

SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(LOWER(ENAME));

Index created.

SQL> SELECT * FROM MYEMP WHERE LOWER(ENAME) = 'ward';

Execution Plan
----------------------------------------------------------
Plan hash value: 2632457189

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_ENAME_INDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


When we execute query with OR clause where OR is filtering the records from 2
different field. Oracle Database can not use index to filter the data.

For example:

SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO);

Index created.

SQL> CREATE INDEX MYEMP_HIREDATE_INDX ON MYEMP(HIREDATE);

Index created.

SQL> SET AUTOT TRACE
SQL> SELECT * FROM MYEMP WHERE EMPNO=7839 OR HIREDATE > SYSDATE - 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |     1 |    38 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

As here we can see that we have indexes on EMPNO and HIREDATE of EMP table,
But the query with EMPNO=7839 OR HIREDATE > SYSDATE - 60 condition was not able
to use any index.

Workaround of this problem is to break this OR clause into UNION,
so that index can be used by the Oracle Database

Solution:

SQL> SELECT * FROM MYEMP WHERE EMPNO=7839
  UNION
  SELECT * FROM MYEMP WHERE HIREDATE > SYSDATE - 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 1491074982

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     2 |    76 |     6  (67)| 00:00:01 |
|   1 |  SORT UNIQUE                  |                     |     2 |    76 |     6  (67)| 00:00:01 |
|   2 |   UNION-ALL                   |                     |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    38 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MYEMP_EMPNO_INDX    |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    38 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | MYEMP_HIREDATE_INDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------