GurV GurV - 3 years ago 353
SQL Question

Filter by numeric column start with a given number

I'd like to filter my rows based on a numeric column. Problem is that the input is a pattern - for e.g. - a number starting with 2045 or something. I could do this:

select *
from my_table
where num_column like '2045%';

It works but that doesn't let me use index on the column. The implicit
on num_column reduces the performance.

Is there a more suitable way in which I could query the same and still use indexes?

This assumes the number always start with the given input (not in between or end) and the value in the column is of variable number of digits.

Answer Source

If the number is in a particular range, then you could do something like:

where num_column >= 2045000 and num_column < 2046000

I am guessing that is not the case . . . although you could extend this:

where (num_column >= 2045 and num_column < 2046) or
      (num_column >= 20450 and num_column < 20460) or
      (num_column >= 204500 and num_column < 204600) or
      . . .

I'm not sure if Oracle would really use an index for a complicated or.

There is another solution. Create an index on an expression and use the expression:

create index idx_mytable_numcolumn_str on my_table(to_char(num_column));

Then you can do:

where to_char(num_column) like '2045%'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download