Re: Index usage with Wildcards - Informix vs. Oracle

Richard Spitz wrote: > > Hello, > > after a controversial discussion with some colleagues, I want to ask > this question to the collective net wisdom: > > When a query is done using wildcards (e.g. ...WHERE column LIKE 'HUB%'), > can the engine use an existing index on that column? > > To my knowledge, Informix can use the index as long as the wildcard > is not the first character in the query string, as in the above example. > > Our Oracle people claim that Oracle cannot use an index as soon as > wildcards are involved. Is that really true? > > Regards, Richard > -- > +----------------------------+-------------------------------------------+ > | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | > | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413 | > | Klinikum Grosshadern | FAX : +49-89-7095-8886 | > | 81366 Munich, Germany | | > +----------------------------+-------------------------------------------+ Oracle will use RANGE index scan for all entries before the first '%' or '?' sign. Like 'Sun%Sol%5.?.' will be in processed in range scan for 'Sun'. Eugene. -- ******************************************************** * Everything above is only my opinion * ******************************************************** * * * If you see a lion in a cage and sign says "elephant" * * , * * DO not belive your eyes ! * * (Kozma Prutkov. (informal translation)) * * * ********************************************************