Re: Avoiding full table scan

> 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: sfaroult_at_oriole.com

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: 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).