Re: Query with 'LIKE' comparison questions

From: Stryjewski <me_at_seebelow.org>
Date: Tue, 11 Oct 2005 21:25:21 GMT
Message-ID: <55W2f.92719$lI5.26566@tornado.ohiordc.rr.com>

Sybrand Bakker wrote:
> 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

Sybrand,

Thanks for the help. Unfortunately, it's not my application to fix and I whole heartily agree with you. However, I've been saddled with the task. A little more detail: the project is to basically replace the application. I have a flat file, with old account - new account (well formed and verified). I need to query the db for old account (the leading zero issue), find the records, then populate another column in the record with the new account.

I have a range of rows that contain the "bad" data. My thoughts with a new table, was to select the range of rows out of the source table, making the to_number conversion into the new table. Contained in this new table, would be a column that is a unique index from the source table. Then query the new table with the account_number without leading zeros, get the unique index, update the primary table using the unique index.

Just here to pick up the pieces. Also, I can really change the structure of the existing table or tablespace. I just need to be a "user" and try to fix this mess.

Pete

pstryjew at att dot net Received on Tue Oct 11 2005 - 16:25:21 CDT