| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: function index for like '%string%'
ooh ooh a fbi with instr! can't wait until Monday to try that one out on the table in question...
>From: "A. Bardeen" <abardeen1_at_yahoo.com>
>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: Sat, 02 Jun 2001 07:45:27 -0800
>
>I understand what you want to do, but realistically
>how do you expect Oracle to maintain an index or
>efficiently search it when the leading characters are
>unknown and of variable length?
>
>Perhaps this would be suitable for a
>context/intermedia index? I have long since forgotten
>what little I knew about context from O7, so I'm not
>much help there.
>
>Is the substring that you're interested in a fixed
>value or a range of exclusive values (i.e. the string
>will not contain more than one of the values)? If so,
>why not create a trigger which populates another
>column and index that column? I would think a bitmap
>index would be ideal for that situation.
>
>Hmm, now that I think about it couldn't you create a
>fbi that uses the instr function to accomplish this?
>
>HTH,
>
>-- Anita
>
>
>--- SuzyV <lvordos_at_datsit.com> wrote:
> >
> > How unpleasant, the answer I expected but didn't
> > want to hear. At points of
> > loopy desperation I've tried dumbest things to make
> > this work, eg., creating the
> > index as lower('%email_addy%') which produces the
> > expected useless result in
> > dba_ind_expressions:
> >
> > SQL> create index idx_test
> > on test (lower('%email_addy%') ;
> >
> > SQL> select index_name, column_expression from
> > dba_ind_expressions
> > where table_name = 'TEST'
> >
> > INDEX_NAME COLUMN_EXPRESSION
> > ---------- -----------------
> > IDX_TEST '%email_addy%'
> >
> >
> > My suggested workaround to the developers is to
> > store the string lower case.
> > For some strings this is okay, but obviously not a
> > great solution for strings
> > which may be case-dependent.
> >
> > fbi = f**ing boneheaded index :)
> >
> > Suzy
> >
> >
> > "Joseph S. Testa" wrote:
> > >
> > > i spent about a week about 6 months ago looking
> > for something to fill
> > > that possibility to no avail.
> > >
> > > 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
> > >
> > > --
> > > 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).
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: SuzyV
> > 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).
>
>
>__________________________________________________
>Do You Yahoo!?
>Get personalized email addresses from Yahoo! Mail - only $35
>a year! http://personal.mail.yahoo.com/
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: A. Bardeen
> INET: abardeen1_at_yahoo.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 -- Author: Rachel Carmichael INET: carmichr_at_hotmail.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).Received on Sat Jun 02 2001 - 15:04:25 CDT
 |
 |