0

I keep many work-related SQL convenience scripts. For a while I've been using a convention of having several AND clauses in the where statement that I can activate by providing a value or values to search on. For example,

where color like '%&color' and size like '%&size'

When I run such SQL in my preferred client (Golden6) it pops up a dialog box where I can provide values for color, size or both. Very convenient, but the performance of LIKE '%string' is often terrible, often resulting in a full table scan, or so I have read.

Is there some alternative technique for writing and managing these scripts that maintains the convenience of being able to fill in only the arguments I want to use, yet avoids the performance issues around LIKE '%string'? I don't want to have to edit the script each time I use it, because I keep them in git and I don't want to manage having a bunch of locally modified files to sort out all the time.

wytten
  • 2,800
  • 1
  • 21
  • 38
  • Well, I guess the question is, do you *really* need `LIKE`? Are you searching for exact values that you know exist, or are you really wanting anything similar to the values you searched? If you really need to use `LIKE '%...'`, then a full table scan cannot be avoided (it does a full table scan because of the leading wildcard, which makes an index unusable). – Siyual Sep 23 '15 at 13:30
  • I'm using LIKE only as sort of a trick, so that if I (for example) do not provide a value for color, and only for size, that the empty color clause does not prevent anything from matching the where condition. – wytten Sep 23 '15 at 13:39
  • You could always change the `AND` to an `OR` to fix that, rather than using a `LIKE` – Siyual Sep 23 '15 at 13:45
  • see [this post](http://stackoverflow.com/questions/17681428/issue-with-oracle-bind-variables-not-using-index-properly/17702034#17702034) for an approach you might try. – tbone Sep 23 '15 at 13:56

1 Answers1

1

If you want to support optional input parameters then you could try

with data as 
(select '123' col1, 'ABC' col2 from dual union select '124', 'AB' from     dual)
select * from  data a where a.col1 = nvl('&col1', a.col1) and a.col2 = nvl('&col2', a.col2)

Additonal data rows with null values 

with data as
 (select '123' col1, 'ABC' col2
from dual
  union
  select '124', 'AB'
    from dual
  union
  select '123', null from dual
  union
  select '124', null from dual)
select *
  from data
 where ('&col1' is null or '&col1' is not null and '&col1' = col1)
 and ('&col2' is null or '&col2' is not null and '&col2' = col2)
Frank Ockenfuss
  • 2,023
  • 11
  • 26
  • I had to tweak this answer to get the behavior I wanted. Experimenting on Oracle it seems a 'value=value' condition is false if the value is null. This means I have to say _and (size is null or size = nvl('&size', size))_ for each of my optional input parameters. – wytten Sep 23 '15 at 18:59
  • Actually that doesn't work either: When applying that technique now I get values that either match the requested value or are null (obviously I guess). I'm about ready to give up. – wytten Sep 23 '15 at 19:11
  • That's right. But as i expect your example with like operator will also not return null values? To get this columns the is operator is required. – Frank Ockenfuss Sep 24 '15 at 06:45
  • my 2nd sql should solve your problem from 2nd comment. Hope it helps. – Frank Ockenfuss Sep 24 '15 at 09:47
  • That did help, thanks. I added another answer based on your suggestions, but in a way that I find easier to read and understand – wytten Sep 28 '15 at 18:08
  • Thanks for incorporating my feedback, I have deleted my answer and am accepting yours – wytten Sep 29 '15 at 14:06