Re: function index for like '%string%'

good question, i've not tried any intermedia/context stuff.

any experts out there?

Joe
"Thater, William" wrote:
>
> On Fri, 1 Jun 2001,Joseph S. Testa scribbled on the wall in glitter crayon:
>
> ->i spent about a week about 6 months ago looking for something to fill
> ->that possibility to no avail.
>
> dumb question would a contex... oh excuse me InterMedia index work?
>
> ->
> ->joe
> ->Rachel Carmichael wrote:
> ->>
> ->> she knows why it isn't being used. What Suzy wants to know (and I do too,
> ->> because I have a similar situation) is if there is a way to create an index
> ->> that will let you search with the like clause.
> ->>
> ->> Unfortunately my gut instinct is that you can't make Oracle do that
> ->>
> ->> >From: "Hillman, Alex" <Alex.Hillman_at_usmint.treas.gov>
> ->> >Reply-To: ORACLE-L_at_fatcity.com
> ->> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> ->> >Subject: RE: function index for like '%string%'
> ->> >Date: Fri, 01 Jun 2001 14:11:08 -0800
> ->> >
> ->> >index is not used because you have leading % in the like predicate.
> ->> >
> ->> >Alex Hillman
> ->> >
> ->> >-----Original Message-----
> ->> >Sent: Friday, June 01, 2001 5:09 PM
> ->> >To: Multiple recipients of list ORACLE-L
> ->> >
> ->> >
> ->> >
> ->> >I've created a function index for lower(column_name). However the query
> ->> >does
> ->> >a like comparison rather than equality so the index isn't used. Is there a
> ->> >way to create a function index for this?
> ->> >
> ->> >Here's the index and query:
> ->> >
> ->> >create index fx1_auth_users
> ->> >on auth_users (lower(current_email)
> ->> >tablespace app01_midx ;
> ->> >
> ->> >select current_email from auth_users
> ->> >where lower(current_email) like '%datsit.com%' ;
> ->> >
> ->> >Thanks much,
> ->> >Suzy
> ->> >--
> ->> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ->> >--
> ->> >Author: Suzy Vordos
> ->> > INET: lvordos_at_datsit.com
> ->> >
> ->> >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).
> ->> >--
> ->> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ->
> ->
> ->
> ->
>
> --
> Bill "Shrek" Thater Certifiable ORACLE DBA
> Telergy, Inc. thaterw_at_telergy.net
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> You gotta program like you don't need the money,
> You gotta compile like you'll never get hurt,
> You gotta run like there's nobody watching,
> It's gotta come from the heart if you want it to work.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> MIPS: Meaningless Indicator of Processor Speed.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Thater, William
> INET: ThaterW_at_telergy.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)

-- 
Joe Testa  http://www.oracle-dba.com
Performing Remote DBA Services, need some backup DBA support?
For Sale: Oracle-dba.com domain, its not going cheap but feel free to
ask :)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joseph S. Testa
  INET: teci_at_the-testas.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).