很多人在写sql或者做优化的时候经常会想，什么样的where语法对于数据是比较友好的呢？什么样的语法才能更好使用索引呢？ 对于这个问题IBM的开发指导的书上给出了下面这个列表，个人感觉很有用，特别是对于SQL优化新手。
| Stage 1 and Indexable Predicates | ||
| Predicate Type | Stage | Indexable |
| COL = | 1 | Yes |
| COL = non-col expr | 1 | Yes |
| COL IS NULL | 1 | Yes |
| COL IS NOT NULL | 1 | Yes |
| COL op value | 1 | Yes |
| COL op non-col expr | 1 | Yes |
| COL BETWEEN value1 AND value 2 | 1 | Yes |
| COL BETWEEN non-col expr1 AND non-col expr2 | 1 | Yes |
| COL BETWEEN expr1 and expr2 | 1 | Yes |
| COL LIKE ‘pattern’ | 1 | Yes |
| COL LIKE :HV | 1 | Yes |
| COL IN (list) | 1 | Yes |
| COL IN (non subq) | 1 | Yes |
| T1.COL = T2.COL | 1 | Yes |
| T1.COL op T2.COL | 1 | Yes |
| COL = (non subquery) | 1 | Yes |
| COL op (non subquery) | 1 | Yes |
| COL op ANY (non subquery) | 1 | Yes |
| COL op ALL (non subquery) | 1 | Yes |
| COL = expression | 1 | Yes |
| (COL1,…COLn) IN (non subquery) | 1 | Yes |
| (COL1,…COLn) = (val1,…valn) | 1 | Yes |
| T1.COL = T2.col expr | 1 | Yes |
| COL IS NOT DISTINCT FROM value | 1 | Yes |
| COL IS NOT DISTINCT FROM non-col expr | 1 | Yes |
| COL IS NOT DISTINCT FROM col expr | 1 | Yes |
| COL IS NOT DISTINCT FROM non subquery | 1 | Yes |
| COL IS NOT DISTINCT FROM T2.COL | 1 | Yes |
| COL IS NOT DISTINCT FROM T2.col expr | 1 | Yes |
| Stage 1 and Nonindexable Predicates | ||
| Predicate Type | Stage | Indexable |
| COL <> value | 1 | No |
| COL <> non-col expr | 1 | No |
| COL NOT BETWEEN value1 AND value2 | 1 | No |
| COL NOT BETWEEN non-col expr1 AND non-col expr2 | 1 | No |
| COL NOT IN (list) | 1 | No |
| COL NOT LIKE ‘ char’ | 1 | No |
| COL NOT LIKE ‘%char’ | 1 | No |
| COL NOT LIKE ‘_char’ | 1 | No |
| T1.COL <> T2.COL | 1 | No |
| T1.COL1 = T1.COL2 | 1 | No |
| COL <> (non subquery) | 1 | No |
| COL IS DISTINCT FROM | 1 | No |
| 注： op操作符下列其一：<=, >=, <, >, or <> non-col expr是指不包含表中字段的任意表达式 Stage 1可以使用and、or、not进行组合 除了上述表格中的Stage 1，不在包含内的都为Stage 2，程序可以认为是Stage 3 |
||
No comments:
Post a Comment