Index Range Scan vs. Leading Wildcard

Can you use an index range scan with a LIKE if you have a leading wildcard in your expression?

e.g.

select *
from my_table
where my_column like '%something%'

Most people would say “no” but, I actually found a way to “trick” Oracle into doing so using bind variables, and taking advantage of bind-peeking.

create table drop_me2
(
    x primary key 
)
  compress 
as
select to_char(rownum)
from dual 
connect by level <= 1000000;

set serveroutput on

declare
  v_bind varchar2(20);
  v_dummy number;
begin
  
  v_bind := 'MOOSE%';

  -- Run the query once with a "properly" placed bind variable
  -- to trick Oracle when it bind-peeks
  select count(*) into v_dummy
  from drop_me2 a
  where a.x like v_bind;

  v_bind := '%MOOSE%';

  -- Then come back and run the exact same query, but change the bind
  -- variable.
  select count(*) into v_dummy
  from drop_me2 a
  where a.x like v_bind;
  
  --You'll see that both queries used the same plan.  The number of session logical reads on the second plan is significantly higher than the first.
  for v_row in (select *
                from table(dbms_xplan.display_cursor(format => '+predicate +peeked_binds')))
  loop
    dbms_output.put_line(v_row.plan_table_output);  
  end loop;
  
  execute immediate 'drop table drop_me2';
end;
/

Output:

SQL_ID  ftjrpvy6f3c13, child number 0
-------------------------------------
SELECT COUNT(*) FROM DROP_ME2 A WHERE A.X LIKE :B1
 
Plan hash value: 3147153515
 
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |             |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_C009054 |     1 |     7 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :B1 (VARCHAR2(30), CSID=873): 'MOOSE%'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."X" LIKE :B1)
       filter("A"."X" LIKE :B1)

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets