will "like" always use full table scan instead of function-based index?

From: Tom McCready <noone_at_nowhere.com>
Date: Thu, 19 Feb 2004 13:09:23 -0500
Message-ID: <c12tti$mglk$1@rs7.loc.gov>

Hello all,

I am a new DBA, who would appreciate your help. In Oracle 9.2.0.2.0 (enterprise edition) on AIX 5L, SQL with "like" in the WHERE clause always prompts a full-table scan instead of reading from a function-based index (please see below). The SQL for index:

create index handle_ix2 on handles(rawtochar(handle))

If I translate the characters to raw myself and use these as the arguments it works with the index, but as soon as I put the chartoraw function in it does the table scan. I also created a regular index on handles(handle) - handles_ix1.

The same query with an "=" in the WHERE clause uses the index (again please see below.) This function-based index has no NULL values. I have rebooted the instance after setting the init*.ora parameters, query_rewrite_enabled='TRUE' and query_rewrite_integrity='TRUSTED'. The schema owner has been granted "query rewrite." Also I have run "analyze table handles compute statistics for table for all indexes for all indexed columns" . The table has more than 700,000 rows.

1 select rawtochar(handle) from handles where 2* handle like chartoraw('LOC.PNP/CPH.3B21874') SQL> / RAWTOCHAR(HANDLE)

LOC.PNP/CPH.3B21874
LOC.PNP/CPH.3B21874 Statistics

          0  recursive calls
          0  db block gets
      11128  consistent gets
       8218  physical reads
          0  redo size
        569  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

but changing the "like" to "=" avoids a full-table scan:

1 select rawtochar(handle) from handles where 2* handle = chartoraw('LOC.PNP/CPH.3B21874') SQL> / RAWTOCHAR(HANDLE)

LOC.PNP/CPH.3B21874
LOC.PNP/CPH.3B21874 Statistics

          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        569  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Received on Thu Feb 19 2004 - 12:09:23 CST