To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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:
- avoid NOT
- 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?
|
