At 08:11 AM 9/16/96 -0500, 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
Your skepticism is well-founded. Oracle will behave the same way as
Informix when the wildcard character is not the first character in the query
string. Your example of (...WHERE column LIKE 'HUB%') will cause Oracle to
use the index, providing of course that "column" is an indexed field (either
by itself or the first field in a concatenated key).
Rick
----------------------------------------------------------------
| Rick Christie | Platinum technology, inc. |
| christie@platinum.com | 620 W. Germantown Pike |
| Advisory Product Developer | Suite 200 |
| Aston Brooke Development Lab | Plymouth Meeting, PA 19462 |
----------------------------------------------------------------