Re: Indexing for LIKE selection
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: JDunn@xxxxxxxxx
- Date: Mon, 05 Mar 2007 13:01:52 +0100
John,
The matter with LIKE is exactly like the case of composite indexes -
that is if you refer to the start of the string (as to the first columns
of the key) everything is fine, Oracle can descend the index tree, if
not ... it can decide either to scan the index or the table, but in both
cases it's probably not what you would expect from an index search.
In other words
like 'blah%' is fine,
like '%blah%' is not.
Same problem, by the way, with regular expressions. Needless to say, if
the queries you expect are mostly of the '%blah%' kind, what you are
performing is a text search, which points to Intermedia (or whatever it
is called nowadays, it has been renamed half a score times at least
since introduction).
Perhaps it is also worth pointing out that if searching on parts of a
column is slowly becoming a standard way of querying the database, it
may mean that the model no longer satisfies the needs and that you are
slowly drifting from the first normal form ...
Stéphane Faroult
John Dunn wrote:
We currently allow users to search a large table for a specific
indexed value. Performance is fine.
However they have now requested to be able to enter a partial string
or wildcards.
I am asssuming we will use the LIKE clause in the select statement.
Do we need to review how we index the table? If so, what is most
appropriate?
Oracle version is 10.2
John
--
//www.freelists.org/webpage/oracle-l
Other related posts:
