Re: Query with 'LIKE' comparison questions

On 2005-10-11, 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.
>
> 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

Pete, use a function based index and revers:

create table foo (
  a varchar2(20),
  b varchar2(20)
);

insert into foo values ('00001234', '1234');
insert into foo values ('00005678', '5678');
insert into foo values ('005678'  , '5678');
insert into foo values ('001234'  , '1234');
insert into foo values ('000789'  , '789' );

create index ix_foo on foo (reverse(a));

explain plan for select * from foo where reverse(a) like '4321%';

select * from table(dbms_xplan.display);

output:

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     2 |    48 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FOO    |     2 |    48 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_FOO |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

As can be seen, the index is used.

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/