WildCards To Jeopardize Execuation Plan

Symptom:

 The incorrect usage of wildcard '%'  will Jeopardize Execuation Plan

create index username_idx on mytest(lower(USERNAME));
select *  from mytest where lower(USERNAME) like 'scot%';


Execution Plan
----------------------------------------------------------
Plan hash value: 3746642705

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

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

   2 - access(LOWER("USERNAME") LIKE 'scot%')
       filter(LOWER("USERNAME") LIKE 'scot%')

If you put % in front of a string, it tells oracle look for the string 'scot' anywhere in the column.
It results full tablescan as normal B-tree index can't handle such search. If it is key word, we may consider to use domain index

select *  from mytest where lower(USERNAME) like '%scot%'; --full scan

Execution Plan
----------------------------------------------------------
Plan hash value: 1692938441

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

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

   1 - filter(LOWER("USERNAME") LIKE '%scot%')