Index SCAN Instead of FAST FULL SCAN With COL LIKE '%bind Variable%' (Doc ID 2781949.1)

Last updated on MARCH 23, 2023

Applies to:

Symptoms

• The query goes for index range scan instead of index fast full scan when the like predicate has leading wildcard for bind variable:
  
  SELECT...
  WHERE TEST_1 LIKE '%'||UPPER(:..)||'%'
  
  Rows (1st) Rows (avg) Rows (max) Row Source Operation
  ---------- ---------- ---------- ---------------------------------------------------
  1 1 1 TABLE ACCESS BY INDEX ROWID BATCHED TB (cr=9795 pr=9794 pw=0 time=7773144 us starts=1 cost=20693 size=44889246 card=114222)
  1 1 1 INDEX RANGE SCAN IX_C_02 (cr=9794 pr=9793 pw=0 time=19063598 us starts=1 cost=91 size=0 card=20628)(object id 309730)
   

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.