demo56> create table t1 as select * from all_objects;

Table created.

demo56> create index t1_idx on t1(upper(object_name));

Index created.

demo56> explain plan for select * from t1 where upper(object_name)='DUAL';

Explained.

demo56> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | T1          |       |       |       |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("T1"."OBJECT_NAME")='DUAL')

Note: rule based optimization

14 rows selected.

demo56> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true)

PL/SQL procedure successfully completed.

demo56> explain plan for select * from t1 where upper(object_name)='DUAL';

Explained.

demo56> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   186 |     4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     2 |   186 |     4 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX      |     2 |       |     3 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("T1"."OBJECT_NAME")='DUAL')

Note: cpu costing is off

15 rows selected.