Plan got  for the condition like '%.zip' in where clause


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

--------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |     1 |    37 |   167 |
|   1 |  SORT GROUP BY               |                             |     1 |    37 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSFER_DATA_TBL           |     1 |    29 |     4 |
|   3 |    NESTED LOOPS              |                             |     1 |    37 |   167 |
|   4 |     INDEX FAST FULL SCAN     | I3_C_ITEM                   |     1 |     8 |   163 |
|   5 |     INDEX RANGE SCAN         | TRANSFER_DATA_TBL_PKEY      |     1 |       |     2 |
--------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

Plan got after creating a FBI and replacing like  by substr
Using FBI (substr(col,-4) I am getting below explain plan and response time s for the query is less here



---------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |     1 |    37 |   166 |
|   1 |  SORT GROUP BY                |                             |     1 |    37 |       |
|   2 |   HASH JOIN                   |                             |     1 |    37 |   166 |
|   3 |    INDEX FAST FULL SCAN       | I3_C_ITEM       	    |     1 |     8 |   163 |
|   4 |    TABLE ACCESS BY INDEX ROWID| TRANSFER_DATA_TBL           |  7895 |   223K|     2 |
|   5 |     INDEX RANGE SCAN          | I4_TRANSFER_DATA_TBL        |  3196 |       |     1 |
---------------------------------------------------------------------------------------------