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

From: Kenneth Koenraadt <plovmand_at_hotmail.com>
Date: Sat, 02 Jun 2001 21:07:11 GMT
Message-ID: <3b195027.3816853@news.mobilixnet.dk>

Hi,

If you have indexed (Normal B-tree index) the company name field and that index is not too disordered, then the index can and will be used by the optimizer if you use "like" this way:

select <field-list> from <mytable>
where comp_name like 'jewel%'

If your execution plan shows full table scan here, try rebuilding the index on comp_name and analyze the table + index afterwards.

In general, 'like' is indexable if you place the wildcard at the END of of your string. However, if you search for an occurence of a string ANYWHERE in the field, like this.

select <field-list> from <mytable>
where comp_name like '%jewel%'

The index is unusable.

---

Regards,
Kenneth Koenraadt
Systems Consultant
Oracle DBA
plovmand@<no-spam>hotmail.com

,
On 3 Jun 2001 15:05:08 -0500, sdfes_at_dsf.com (sdfsd) 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
Received on Sat Jun 02 2001 - 16:07:11 CDT