How to properly use functions to avoid full table and index scan

1. WITHOUT INDEX

SQL> select COUNT(*) from PAYMENT_REQUESTS where DEPENDENT_CODE=1062209398;

  COUNT(*)
----------
        41

Elapsed: 00:00:06.18

Execution Plan
----------------------------------------------------------
Plan hash value: 3772329927

----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                               |     1 |     2 | 67406   (1)| 00:13:29 |
|   1 |  SORT AGGREGATE    |                               |     1 |     2 |            |          |
|*  2 |   TABLE ACCESS FULL| PAYMENT_REQUESTS   |     5 |    10 | 67406   (1)| 00:13:29 |
----------------------------------------------------------------------------------------------------

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

   2 - filter("DEPENDENT_CODE"='1078754196')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     250059  consistent gets
     250057  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2. AFTER CREATING INDEX "IDX_$XXX0001_ON_DEPENDENT_CODE"

SQL> select COUNT(*) from FIN_EDATT_STD_PAYMENT_REQUEST where DEPENDENT_CODE=1062209398;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4006545759

----------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                                |     1 |     2 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                                |     1 |     2 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_$XXX0001_ON_DEPENDENT_CODE |   120 |   240 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("DEPENDENT_CODE"='1062209398')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          1  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


3. PLACING FUNCTION ON LEFT

What if i have to convert this number column to character. Will index still be used ? Let's give it a try.

SQL> select COUNT(*) from PAYMENT_REQUESTS where TO_CHAR(STUDENT_ID_NUM)='1062209398';

  COUNT(*)
----------
        41

Elapsed: 00:00:01.83

Execution Plan
----------------------------------------------------------
Plan hash value: 1062758856

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |     7 |  6031   (2)| 00:01:13 |
|   1 |  SORT AGGREGATE       |                 |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX$$_13AED0003 |    30 |   210 |  6031   (2)| 00:01:13 |
-----------------------------------------------------------------------------------------

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

   2 - filter(TO_CHAR("STUDENT_ID_NUM")='1062209398')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      21737  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

You can see a fast full index scan occured and query took 1.83 seconds. This can be further improved moving funcion on Right.

4.     PLACING FUNCTION ON RIGHT

SQL> select COUNT(*) from PAYMENT_REQUESTS where DEPENDENT_CODE=TO_CHAR('1078754196');

  COUNT(*)
----------
         3

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4006545759

----------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                                |     1 |     2 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                                |     1 |     2 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_$XXX0001_ON_DEPENDENT_CODE |   120 |   240 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("DEPENDENT_CODE"='1078754196')


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Here you can see Index range scan occured and execution time dropped to  00:00:00.01 seconds.