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).
Received on Sat Jun 02 2001 - 09:48:50 CDT
