RE: optimizer function based indexes

Joe,

There are certainly some situations where it is advantageous to use an index in conjunction with LIKE and criteria containing a leading %. On a tuning gig not too long ago, I had the need, like you, to use a function based index where criteria was similar to yours -- upper(col1) like '%ABCDE%'. So, your email caught my eye.

I am able to duplicate your problem on my test machine (W2K with 8.1.7.0.0 EE and 9.0.1.1.1 EE). Against 8.1.7, I have no problem using a hint to force the use of the function based index even when the criteria has both a leading and trailing % -- e.g. '%12345%'. I cannot, though, force the use of the function based index against 9i, at least in the same test case, when the criteria starts and ends with the % wildcard.

If using a bind variable, I could get the index to be used -- and that's to be expected I suppose since knowledge of the value is unknown at parse time. And, any combination of trailing/leading underscore "_" has no problem when using literals. And, against 9i, I can force the index to be used if the criteria has a leading % *without* a trailing %. So, only when the value is constrained on both ends by the % do I see the problem (just as you do).

For grins, I also performed the test against 9i using a "normal" index. I could force the index when the criteria contained a leading and trailing %. So, this seems specific to a function based index.

I'm going to play around with this a lot more and see if I come up with anything. One of the first things I did and will mention here is the output from a 10053 trace.

9.0.1.1.1:

<SNIP>

SINGLE TABLE ACCESS PATH
Column: SYS_NC0000 Col#: 3 Table: FOO Alias: FOO
<SNIP>

  Access path: tsc Resc: 14601 Resp: 14601 <<<<< Item A   Access path: index (no sta/stp keys)
<SNIP>

8.1.7.0.0:

<SNIP>

SINGLE TABLE ACCESS PATH
Column: SYS_NC0000 Col#: 3 Table: FOO Alias: FOO
<SNIP>

  Access path: index (scan)
<SNIP>

Notice the inclusion of Item A in the 9i trace. Also notice the following line in the trace and the "(no sta/stp keys)" -- no start/stop keys? So, it looks like in 9i it is still considering a tablescan (tsc) in the section for the SYS_NC0000 "column" whereas we see nothing like that in the 10053 trace under 8.1.7? In the full 10053 trace under 9i, the index cost was higher. So, even though we specify the hint, it still looks like when considering "SYS_NC0000" it also compares against a tablescan and decides to ingore the hint? Do a 10053 trace yourself on your systems and see what you turn up since I only included just a snippet of the traces.

Anyway, I've tried things like cranking down the multiblock read count and other parameters, adding 20 large columns to the table, all in an effort to make a table scan more expensive. And I'm continuing to play around with documented (and undocumented) parameters to see what turns up.

Though there haven't been many other responses to you email, I wonder if others are looking into this as well.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
Sent: Tuesday, October 02, 2001 3:15 AM
To: Multiple recipients of list ORACLE-L

hello i've update my database from 8.1.7 release 3 to 9i and now following statement don't use my optimizer hint in 9i (in 8i it works)

upper_artikel_kurztext_idx is an function based index select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) LIKE '%WANN%'

when i change the statement it works but i need above statement select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) LIKE 'WANN%' or
select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) = 'WANN'

the following parameters are set in init.ora QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
OPTIMIZER_MODE = CHOOSE database is analyzed !

any ideas

thanx joe

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).