Re: Query with 'LIKE' comparison questions

Hi Pete!
Here some suggestions:
1.) use an reverse index and see how it goes. 2.) if you use function on columns, you also have make an function based index on that columns and init.ora-parameter query_rewrite must be true (if I remember correctly, timed_statistics must me true also)

Look at the query plans (execution plan) in every case. Greetings
Bjørn

"Stryjewski" <me_at_seebelow.org> skrev i en meddelelse news:gPU2f.91804$lI5.53477_at_tornado.ohiordc.rr.com...
> 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.
>
> 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.
>
> 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.
>
> 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
Received on Tue Oct 11 2005 - 15:09:33 CDT