--- title: "DB2 SQL – Perfect Way to Use Conditions in WHERE" description: "Best rewriting SQL query methods to improve Query performance . https://srinimf.com/2016/09/08/2-simple-sql-queries-on-self-join-and-inner-join/. First write. Split predicates out if possible: SELECT..." url: https://srinimf.com/2015/08/10/db2-split-predicates-to-improve-sql-performance/ date: 2015-08-10 modified: 2026-10-10 author: "Srini" image: https://i0.wp.com/srinimf.com/wp-content/uploads/2015/08/pexels-photo-256417.jpeg?fit=1880%2C1047&ssl=1 categories: ["Mainframe DB2", "SQL"] tags: ["The best DB2 performance tuning and tips"] type: post lang: en --- # DB2 SQL – Perfect Way to Use Conditions in WHERE Best rewriting [SQL query](https://srinimf.com/2019/03/17/3-sql-tuning-ideas-on-table-columns-to-apply/) methods to improve [Query performance](https://srinimf.com/2015/05/20/start-tuning-your-db2-sql-query-part-1/). Related[Two SQL queries on Self-join and Inner-Join](https://srinimf.com/2016/09/08/2-simple-sql-queries-on-self-join-and-inner-join/) **First write** Split predicates out if possible: SELECT DEPTNO, DEPTNAME FROM DEPT WHERE (ADMRDEPT = ‘E01’ AND DEPTNAME LIKE ‘BRANCH%’) OR (DEPTNO = ‘D01’ AND DEPTNAME LIKE ‘BRANCH%’) can also be tried as: **Second write** SELECT DEPTNO, DEPTNAME FROM DEPT WHERE (ADMRDEPT = ‘E01’ OR DEPTNO = ‘D01’) AND DEPTNAME LIKE ‘BRANCH%’ The idea [here is](https://srinimf.com/2014/09/23/db2-performance-tuning-explain-plan/) to see that both examples have the same logic. Many times this will move the optimizer to indexable from non-indexable or possibly move the optimizer to choose a multi-index processing access path, or move from multi-index process. Connecting predicates with an OR creates non-Boolean term predicates, which means neither predicate can totally eliminate a row as false—and it is usually best to stay away from those. If one predicate is false, the other may be true. It is usually more efficient to connect predicates by Anding them together. But in this case, it gives the optimizer more choices. When you have predicates that are connected by an ‘OR’, it is important to code the predicate that matches the most number of rows in the table first. This is because as soon as a row qualifies, predicate evaluation within the OR list stops. **Indexable and non-Indexable** When simple predicates are connected by OR logic, the resulting compound predicate will be evaluated at the higher [stage 1](https://srinimf.com/2014/07/08/advanced-sql-in-db2-part-2/) or 2 of the simple predicates. For example: SELECT EMPNO, LASTNAME, SALARY FROM EMP WHERE WORKDEPT = ‘D01’ — Stage 1 Indexable OR EDLEVEL <> 16 — Stage 1 Non Indexable Because the second predicate is a stage 1 non-indexable predicate, and the other simple predicate is stage 1 indexable, t**he entire compound predicate becomes stage 1 non-indexable:** **Indexable and Stage-2** SELECT EMPNO, LASTNAME, SALARY FROM EMP WHERE WORKDEPT = ‘D01’ — Stage 1 Indexable OR YEAR(HIRE_DATE ) = 1990 — Stage 2 Because the second predicate is a stage 2 non-indexable predicate, and the other simple predicate is stage 1 indexable, t**he entire compound predicate becomes stage 2 non-indexable.** Related Posts - [DB2 SQL Query Performance Tuning (1 of 3)](https://srinimf.com/2019/03/17/3-sql-tuning-ideas-on-table-columns-to-apply/)DateMarch 17, 2019 - [NVL and COALESCE Functions Basic Differences in DB2](https://srinimf.com/2015/11/08/nvl-and-coalesce-functions-basic-differences-in-db2/)DateNovember 8, 2015 - [PLI program using DB2 top ideas](https://srinimf.com/2012/08/23/pl1-db2-program-coding/)DateAugust 23, 2012 ### Share this: - - [Tweet](https://twitter.com/share) - [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2015%2F08%2F10%2Fdb2-split-predicates-to-improve-sql-performance%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2015%2F08%2Fpexels-photo-256417.jpeg%3Ffit%3D1200%252C668%26ssl%3D1&description=DB2%20SQL%20-%20Perfect%20Way%20to%20Use%20Conditions%20in%20WHERE) - - [Share on Tumblr](https://www.tumblr.com/share) - [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2015/08/10/db2-split-predicates-to-improve-sql-performance/?share=jetpack-whatsapp) - [More](#) - - [Print (Opens in new window) Print](https://srinimf.com/2015/08/10/db2-split-predicates-to-improve-sql-performance/?share=print) - [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2015/08/10/db2-split-predicates-to-improve-sql-performance/?share=reddit) - [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2015/08/10/db2-split-predicates-to-improve-sql-performance/?share=telegram) - [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20SQL%20-%20Perfect%20Way%20to%20Use%20Conditions%20in%20WHERE&body=https%3A%2F%2Fsrinimf.com%2F2015%2F08%2F10%2Fdb2-split-predicates-to-improve-sql-performance%2F&share=email) - ### Like this: Like Loading…