Q

Sargable predicates in SQL

What are examples of sargable or stage 1 predicates and non-sargable or stage 2 predicates? Are they still pertinent in today's SQL?

What are examples of sargable, or stage 1 predicates as they were called, and non-sargable or stage 2 predicates? Are they still pertinent in today's SQL?

Thanks for the question. I had not heard that term for a while. Indeed, it goes back to the beginning, when IBM invented DB2. According to Wikipedia,

A condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument.

So yes, this concept is still relevant today. If we search the Orders table for a specific Customer's orders, the query is sargable if an index exists on the customer_id column.

In general, there are two rules:

  1. avoid NOT
  2. put the column by itself on the left side of the operator

For example, the following is not sargable:

select id
     , cust_id
     , amount
  from orders
 where month(date_ordered) = 05

When a function is performed on a column, the optimizer cannot do an index search. Put the column by itself on the left side:

select id
     , cust_id
     , amount
  from orders
 where date_ordered between '2007-05-01' 
                        and '2007-05-31'

Besides BETWEEN, other sargable operators are =, >, <, >=, <=, and LIKE with a trailing—not leading—wildcard. However, lest anyone get the wrong idea, that does not mean you can do the following:

select id
     , cust_id
     , amount
  from orders
 where date_ordered like '2007-05%'

Nobody would do this, though ... right?

This was first published in August 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close