| Indexes with wild card at both sides [message #22832] |
Thu, 31 October 2002 14:32  |
Anbu
Messages: 6
Registered: August 2000
|
Junior Member |
|
|
Folks,
Is there a way to make Oracle use indexes in the query that has wild card at both ends as the following query, any special indexes, please note there is a UPPER, so, it has to be function based index? Can I create multiple index on the same column to somehow make this query use the index?
Btw, the description column is of type VARCHAR2(500).
SELECT NAME, DESCRIPTION FROM MY_TEST_TABLE WHERE UPPER(DESCRIPTION) LIKE '%ABC%'
|
|
|
|
| Re: Indexes with wild card at both sides [message #22833 is a reply to message #22832] |
Thu, 31 October 2002 15:02  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
No, there is no B*Tree index (function-based or otherwise) that will help this query. If the search string ('ABC' in your example) is really a word IN the description, with surrounding spaces, or part of a word and set off by punctuation, then Oracle Text (interMedia) would be the optimal solution.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
