Re: what is fastest way to search text field with 'like'

sdfsd <sdfes_at_dsf.com> wrote:
>
>we have 2 million rows with a comoany name column the problem is that there
>maybe different spellings for the same company name, i.e. jewel, jewels, jewel
>inc., etc.
>
>indexing doesn't help, and like does full scan. any ideas to deal with issues
>like this? maybe some way of decoding or something? for example creating a
>field that has first 2 letters of name and then indexing that?
>
>any ideas appreciated

LIKE can use an index if one exists. Two possibilities spring to mind:

Are you querying with LIKE '%je%' ? An index can't help with a leading wildcard.

Alternatively, if you are using the Cost Based Optimizer, then the it may be choosing to do a full table scan despite the index. This can be more efficient, as you don't do any I/O on the index; consider the pathological case where a query matches all rows, clearly it takes less I/O to just read the table rather than the index and the table. There's a break-even point when the index lookup becomes inefficient, As a rule of thumb, if more than about 5-10% of rows will return a match (exact value depends on the particular table and data) then the CBO will ignore the index and do a FTS.

If you're using the CBO, make sure that the table statistics are up to date. You could try giving the query a hint to use the index e.g. :

SELECT /* +INDEX(name) */
name
FROM company
WHERE name LIKE 'je%';

However, if you're using a recent version of Oracle, and have up-to-date statistics, don't be surprised if the CBO knows better than you as to when it's useful to use the index (in most cases). Older versions are less good.

As an alternative, you could create a lookup table that maps all the variants of a given company name to a single ID, and use that ID in your main table. This adds the cost of the join, but helps your data model, and may allow more efficient lookups.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/