DB2中使用SELECTIVITY子句影响优化器

　　本文适用于 DB2 Universal Database TM version 8.1 for Linux，UNIX 和 Windows。

　　自从我积极参与研究 Oracle 到 DB2 的移植以来，就经常被问到，DB2 是否像 Oracle 一样支持 SQL 提示。简短的回答就是“不”。

　　下面是更详细的回答：

　　在 IBM 里，我们有关于 SQL 优化的不同原则。如果 DB2 UDB 没有选择最优访问方案，而且并非是查询中的内在局限导致了较差的访问方案，我们就把它看作是产品的缺陷并且愿意在源头就解决该问题，以使所有的 DB2 用户都能从中受益。因此，您将发现在 DB2 中就不那么需要以提示开始。而对于其他情形，查询中的内在局限使得难以选择合适的访问方案，您可以通过提供附加的选择性(selectivity)信息来影响 DB2 SQL 优化器。

　　例如，考虑下面这个 SQL 语句：

　　SELECT * FROM T1 where col1 >= ?

　　如果表 T1 中 coll 的值是从 0 到 100，那么由参数标志符提供的值可以极大地改变谓词的实际选择性。即，如果 coll 中存储的值均匀分布在 0 到 100 之间，那么当参数标志符的值为 10 的时候，满足 WHERE 子句条件的记录行数将大大不同于参数标志符的值为 90 时的记录行数。

　　然而，您可以通过使用 selectivity 子句提供期望的选择性来影响优化器。在希望提倡(或阻止)DB2 使用某种索引的情况下，这样做将十分有用。使用 DFT_QUERYOPT=5 (默认设置)似乎最能发挥该功能的预期作用。为了支持 selectivity 子句，您首先必须设置注册表变量 DB2_SELECTIVITY=YES 并且重新启动该实例以使该变量生效。

　　db2set DB2_SELECTIVITY=YES

　　db2 force application all (终止所有已连接的用户)

　　db2stop

　　db2start

　　SELECTIVITY 子句只能与基本谓词(在 SQL 参考大全中定义的)一起使用，而不能与 LIKE 或 BETWEEN 这样的谓词一起使用。较低的选择性值(非常小的数)将告诉 DB2，只有较少的记录行将符合谓词要求(并提倡使用在其列上定义的索引)。而较高的选择性值(接近 1)将表示相反的意思。

　　例子： SELECT c1, c2, c3, FROM T1, T2, T3

　　WHERE T1.x = T2.x AND

　　T2.y=T3.y AND

　　T1.x >= ? selectivity 0.00001 AND

　　T2.y < ? selectivity 0.5 AND

　　T3.z = ? selectivity 0.2 AND

　　T3.w = ?

　　SELECTIVITY 起作用的示例

　　*提示

　　DB2 的 Visual Explain 工具允许使用参数标志符。

　　注意：

• 随 DB2 一同提供的 SAMPLE 数据库提供了 EMPLOYEE 和 DEPARTMENT 表。
• 已在 department 表的 DEPTNO 列上创建索引 DEPTNOIDX，用以演示该示例。

　　下面是最初的查询：

　　SELECT * FROM EMPLOYEE e, DEPARTMENT d

　　WHERE e.workdept = d.deptno

　　AND d.deptno = ?

　　下面是用不同的 selectivity 子句修改后的相同查询：

　　SELECT * FROM EMPLOYEE e, DEPARTMENT d

　　WHERE e.workdept = d.deptno

　　AND d.deptno = ? selectivity 0.9

　　SELECT * FROM EMPLOYEE e, DEPARTMENT d

　　WHERE e.workdept = d.deptno

　　AND d.deptno = ? selectivity 0.25

　　现在来比较 visual explain 的输出。请注意我们是如何影响索引的使用的。

　　选择性为 0.9(较低的选择性)

　　 

　　选择性为 0.25(较高的选择性)

　　应该将使用 selectivity 子句作为最后一招考虑，在使用它之前：

• 用不同的 SQL 优化类进行实验。默认的优化类是由数据库配置文件中的 DFT_QUERYOPT 参数控制的。
• 通过确保收集到正确的数据库统计信息来尝试解决性能问题。统计信息越详细，优化器就能执行得越好。(请参阅 DB2 Command Reference 中的 RUNSTATS)。
• 如果较差的访问方案是由于快速修改表的特征导致的(例如，表增长太快以致统计信息很快就过时了)，试使用 altER TABLE 命令将表标记为 VOLATILE。
• 尝试在谓词中使用字面值而非参数标志符来解释查询。如果在使用参数标志符时得到了不同的访问方案，它将帮助您更好地理解性能问题的本质。您可以发现，在应用程序中使用字面值将会以 SQL 编译开销为代价产生更好的方案(因而获得更好的性能)。
• 尝试使用 DB2 的索引建议器(db2advis)查看是否忽略了一些有用的索引。

查看本文来源