> Jack van Zanen wrote: > > Hi All, > > I wish to avoid a full tablescan on the following data > > V. Zanen > Zanen > Van Zanen > ... > ... > ... > Lot's more data > > Select * from table where upper(name) like '%ZANEN%' > > I could create a function based index on upper(name) but this does not > take care of the % and like operator. > > Oracle has this (I believe it's called) context stuff that you can > index varchar fields etc. Is this the (only possible?) way to go?? > > TIA > > Jack
If you index name and put all the columns from the SELECT list into the index, my guess is that you will get an index fast full scan, which may not be that bad. Basically depends on how many blocks you have to wade through. Otherwise I don't see any other solution than Intermedia. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
