Which is quicker for searches in Oracle

Question

I am trying to speed up a database application and I am currently looking for the easy thing which would have low impact to the application.

I was wondering what would be the most effective way to search to see if a String met a condition. Would there be any valid reason for using the first option.

So say there is a field call status and it will only ever store the value as Discontinued.

• status NOT LIKE 'Disc%'
• status != 'Discontinued'
• status <> 'Discontinued'

Answer 1

A column which holds either a single value or a null is unlikely to make any difference to the performance of any query you run against that table, especially if you're searching for rows which don't equal that value.

So any of those options will perform equally well, regardless of whether that column is indexed. However, as an expression of intent - both to your fellow developers and the database optimizer - it would be better to code it as:

status is null   

Answer 2

I'll assume for purposes of discussion that the STATUS field is nullable, and thus will contain two values, "Discontinued" or NULL. If this is the case, none of your queries above will produce the expected results. When NULL is compared to anything it returns NULL instead of TRUE or FALSE, and thus rows where STATUS is NULL will not be returned when the predicate is STATUS <> 'Discontinued' or whatever. To get the results I think you're expecting you'll have to say STATUS IS NULL.

Share and enjoy.

Answer 3

There would be no reason to use a NOT LIKE. Whether you use the != or the <> form of the inequality operator is solely a matter of personal preference.

Answer 4

I would vote for the second one. the first one will force a TABLE SCAN.

Write both, EXPLAIN PLAN, and see.