1

Does adding a minus sign in front of a column reference in a SQL statement prevent the query processor from using an existing index on that column to process the query?

i.e., would the following use an index on id if one existed, or would it need to perform a table scan?

Select * from myTable
where -id > 23

vs

Select * from myTable
where id < -23

which is definitely SARGable

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • 1
    Oracle or SQL Server? Don't tag products not involved. – jarlh Aug 03 '16 at 08:47
  • General SQL question, not vendor specific – Charles Bretana Aug 03 '16 at 08:49
  • Have you tried? Presumably, you have some data to test this with. On a side note: ouch, negative IDs... :D – Luaan Aug 03 '16 at 08:49
  • 2
    Well, there's no mention of SARGability in the ANSI standard, so this just makes your question too broad. Answers to this are by necessity vendor specific. In fact, they are even *version* specific - different query optimizers might have different approaches. – Luaan Aug 03 '16 at 08:50
  • 1
    ANSI SQL doesn't even mention indexes. – jarlh Aug 03 '16 at 08:52
  • From a SQL Server point of view, the query is almost certainly still SARGable, but YMMV in the query you write or DBMS used. As it stands right now, all we could answer is whether *this specific* query you show is SARGable and in which DBMS it is. – DavidG Aug 03 '16 at 08:53
  • I have done some testing (in SQL Server), but I'm not sure how to test it exactly. In some cases I still get an `Index seek`, with a `compute scalar`. In other cases, I get an index scan... But there are so many other things that can cause an index scan... I'm not sure of how to design a definitive test. – Charles Bretana Aug 03 '16 at 08:57
  • But every query is different, what exactly are you trying to test? – DavidG Aug 03 '16 at 09:26
  • Check the execution plan. –  Aug 03 '16 at 10:13
  • @davidG. Whether changing the reference to a column used by an index from a direct reference to a reference with a unary minus sign precents the processor from using an index-seek (vertically traversing b-trees nodes from root to leaf) to process the query, and forces an index or table scan instrad. – Charles Bretana Aug 03 '16 at 12:23
  • So isn't comparing the query plans on 2 statements (i.e. the ones you show above) enough to tell? – DavidG Aug 03 '16 at 12:27
  • Not definitively, sometimes the optimizer will choose a scan anyway, like, for example, if it guesses based on statistics that your query will need to return more than some percentage of the entire table that N IOs/ row returned (what is required for index seek) will exceed the total rows in the table. That would occur, if say, N (number of levels in b-tree) is 5, when the query would return more than 20% of the table. And that's just one of the exceptions that I am aware of. – Charles Bretana Aug 03 '16 at 12:34
  • Another is for small tables, where all the data is on a single or a very few pages. What that threshold is I'm not sure. – Charles Bretana Aug 03 '16 at 12:35

0 Answers0