Re: Query with 'LIKE' comparison questions

comments embedded

On Tue, 11 Oct 2005 19:58:04 GMT, Stryjewski <me_at_seebelow.org> wrote:

>I'm having trouble creating a query that runs at anything more that a
>crawl here.
>
>1) VarChar Column (acct_num) that contains account numbers which have
>leading zeros (input by users). The problem is that the users seem to
>have put varying numbers of leading zeros (one to eight). So account
>1234 may be 0001234 or 000001234. The Column is a index (non-unique). I
>need to find any rows that have this account number, regardless of the
>number of leading zeros.
>

You don't need that. You need to fix the application and convert the varchar to a number.

>2) select <other columns> from table where acct_num LIKE '%1234';
>Is slow as a dog. Of course a where acct_numb='0001234' will return
>right away.

LIKE '%<literal>' will suppress the index. You need to fix the application.

>
>3) I also tried to drop the leading zeros: select <other columns> from
>table where to_number(acct_num)=1234; But that is slow too.
>

a function applied to an indexed column will suppress using the index, unless you use a function based index.
You'd better fix the application.

>Any other hints. I thought about creating another table, with a select
>that uses the to_number and also grabs the other columns I'm interested
>in. Then querying against that table.
>
>Pete
>pstryjew at att dot net

Did I say you need to fix the application and stop using this mess?

--
Sybrand Bakker, Senior Oracle DBA