by Markus Winand.

Distinguishing Access and Filter-Predicates

No other database provides better information about the predicate evaluation mode than DB2 because it just says in the execution plan whether a predicate is used as the start and/or stop condition for an IXSCAN or as mere filter predicate. Yet it is confusing because it uses an old definition of the term “sarg”.

In the early days, IBM researchers named these kinds of search conditions “sargable predicates” because SARG is a contraction for Search ARGument. In later days, Microsoft and Sybase redefined “sargable” to mean “can be looked up via the index.”

SQL Performance Tuning

I find both definitions pretty useless and avoid the term in my books and articles entirely. However, in DB2 execution plans filter predicates are labeled SARG—thus we have to make it explicit that IBM DB2 uses the “original” definition as mentioned above. That is, of course, backed by the documentation:

  • Index sargable predicates are not used to bracket a search, but are evaluated from the index if one is chosen, because the columns involved in the predicate are part of the index key. […]

  • Data sargable predicates […] require the access of individual rows from a base table. If necessary, DMS will retrieve the columns needed to evaluate the predicate, as well as any others to satisfy the columns in the SELECT list that could not be obtained from the index.

Predicate processing for queries, DB2 LUW 11.1 documentation

That means, in DB2 predicates labeled SARG are generally just filter predicates—either on index level or on table level.

The very nice part about the predicate information shown in DB2 execution plans is that they don’t just label access predicates, but explicitly say which predicates are used as START and/or STOP conditions.

The following example shows all types of predicates as shown by the last_explained view:

Explain Plan
ID | Operation           |                        Rows |  Cost
 1 | RETURN              |                             | 23550
 2 |  GRPBY (COMPLETE)   |        1 of 96480 (   .00%) | 23550
 3 |   IXSCAN SCALE_SLOW | 96480 of 60299800 (   .16%) | 23544

Predicate Information
 3 - START (Q1.SECTION = ?)
      STOP (Q1.SECTION = ?)
      SARG (Q1.ID2 = ?)

Explain plan by Markus Winand - NO WARRANTY

The scanned index range can be determined quite easily from this output—it’s only determined by the START and STOP predicates (which happen to be the same in this case). The third predicate on the ID2 column is labeled SARG and thus just a filter predicate.

While SARG predicates may appear on other operations too (e.g. TBSCAN), START and STOP are exclusive to IXSCAN. The absence of either START or STOP indicates an search with only upper or lower bound (e.g. WHERE x > ?). If neither START nor STOP appears for an IXSCAN, it means that the full index is read.

Previous pageNext page

You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up. Have a look at modern-⁠ as well.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at Previously he made, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license