db2exfmt 建议索引_简化DB2 for i5 / OS索引建议

DB2 for i5 / OS索引顾问

DB2 for i5 / OS V5R4引入了Index Advisor，它记录了有关系统索引需求的统计信息。 每次执行查询或SQL语句时，索引顾问都会记录查询优化器建议的任何索引，以可能提高性能。 有关DB2 i5 / OS版索引建议主题的新知识，可以参考“ DB2 i5 / OS版按需索引建议 ”。 该基础知识将有助于理解此新的索引建议冷凝器。

每次建议使用索引时，都会在系统索引建议表QIX2中的SYSIXADV中添加新行。 假设以前没有建议过该索引。 索引顾问检查以下列中的属性，以确定优化器是否已建议索引：

• LEADING_COLUMN_KEYS
• KEY_COLUMNS_ADVISED
• TABLE_NAME
• TABLE_SCHEMA
• INDEX_TYPE
• PARTITION_NAME
• NLSS_TABLE_NAME
• NLSS_TABLE_SCHEMA

表3中可以找到SYSIXADV表的完整布局。 如果表中存在匹配建议，则会更新现有行。 例如，“建议的次数”条目增加，并且“建议用于查询的最后建议”被更新为当前时间。

原始形式的索引建议可能不必要地冗长。 尽管表中的所有条目都是唯一的，但某些条目可能是多余的。 当建议使用相同的列但顺序不同并且这些键的顺序无关紧要时，就会出现一个很好的例子。 在这种情况下，可以排列这些列以创建一个覆盖两个建议实例的索引。 通过“独立于领先的密钥顺序”列指示了对建议密钥进行重新排序的机会。 此列中列出的键是前导键，可以重新排序，同时仍满足建议的索引。

建议的索引示例

查看一些索引建议示例，为冷凝器简化工作打下基础。 以下示例假定所有建议位于同一表，同一分区，索引类型和排序顺序相同。

表1.建议索引示例1

建议按键	前导键，与订单无关
C1，C2，C3	C1，C2，C3
C1，C3，C2	C1

在此示例中，第二条建议是不柔和的。 为了满足索引建议，永久索引将需要具有{C1，C3，C2}顺序的键。 但是，第一行建议具有很大的灵活性。 前导密钥顺序无关的详细信息表明密钥可以是任意顺序，因此您可以通过将{C1，C2，C3}重新排序为{C1，C3，C2}将两行建议压缩为一个永久索引。

如果将表2中的示例扩展为包含第三行建议，您仍然可以得出结论，添加单个永久索引{C1，C3，C2，C4}应该可以为优化器提供永久改善性能的所有条件。这些查询的性能。

表2.建议索引示例2

建议按键	领先的钥匙，与订单无关
C1，C2，C3	C1，C2，C3
C1，C3，C2	C1
C1，C3，C2，C4	C1，C2，C3，C4

对比指数顾问和简明索引建议

冷凝索引建议技术要求在服务器上安装以下V5R4M0 5722-SS1 PTF：SI25391，SI25469和SI25470。

当索引建议被压缩时，原始索引建议将被置于摘要形式。 原始建议中的某些字段是特定于建议实例的，没有摘要形式的值。 其他领域对于保留和强调简明建议的重要性非常重要。 表3每列说明了索引建议合并期间原始索引建议会发生什么。 浓缩索引建议允许与在索引顾问下找到的iSeries Navigator操作相同的操作，但有一个例外。 由于精简索引建议不存在永久形式，因此没有“从列表中删除”操作。

表3.冷凝器塔的过渡

栏名	列标题	冷凝动作
TABLE_NAME	建议索引的表格	保留不变
TABLE_SCHEMA	包含表的架构	保留不变
SYSTEM_TABLE_NAME	建议使用索引的系统表名称	保留不变
PARTITION_NAME	索引的分区详细信息	保留不变
KEY_COLUMNS_ADVISED	建议索引的列名	产生的
LEADING_COLUMN_KEYS	前导，顺序无关的键，位于Key_Columns_Advised字段开头的键可以重新排序，但仍满足建议的索引	消耗并丢弃
INDEX_TYPE	基数（默认）或编码矢量索引（EVI）	保留不变
LAST_ADVISED	上次更新此行	最近使用的建议时间戳
TIMES_ADVISED	已建议该指数的次数	通过求和产生
ESTIMATED_CREATION_TIME	创建索引的估计秒数	使用的最大值
REASON_ADVISED	建议索引的编码原因	舍弃
LOGICAL_PAGE_SIZE	建议的索引页面大小	使用的最大值
MOST_EXPENSIVE_QUERY	查询的执行时间（以秒为单位）	使用的最大值
AVERAGE_QUERY_ESTIMATE	查询的平均执行时间（以秒为单位）	通过平均产生
TABLE_SIZE	建议索引时表中的行数	最新使用建议的价值
NLSS_TABLE_NAME	排序序列表用于索引	保留不变
NLSS_TABLE_SCHEMA	排序顺序表的库名称	保留不变
MTI_USED	因为不存在匹配的永久索引，所以使用了与建议的定义相匹配的维持临时索引（MTI）（自治索引）的次数	舍弃
MTI_CREATED	该特定索引建议用于创建MTI的次数	舍弃
LAST_MTI_USED	上次使用MTI是因为不存在匹配的永久索引	舍弃

执行以下SQL语句以生成图1a和图1b中包含的原始索引建议。

清单1.用于生成索引建议的数据库和查询

-- Create sample database in CONDENSE schema
 CALL QSYS.CREATE_SQL_SAMPLE('CONDENSE');
 SET SCHEMA Condense;

 SELECT e.firstnme, d.deptnmae FROM department d, employee e 
	WHERE e.job = 'DESIGNER' AND YEAR(e.birthdate) > 1950 AND e.sex = 'M';
 SELECT e.firstnme, d.deptnmae FROM department d, employee e 
	WHERE YEAR(e.birthdate) = 1953 AND e.job = 'DESIGNER' AND e.sex IN ('M');
 SELECT e.firstnme, d.deptnmae FROM department d, employee e
        ORDER BY e.job, e.sex, e.birthdate;

图1b仅包含了图1a中不适合的其余索引建议属性。 请注意，“建议的键”下的列顺序是不同的。

图1a。 建议索引输出

图1b。 建议的索引输出（续）

在这种情况下，两个建议索引具有足够的键顺序灵活性，可以将建议压缩为一个索引。 图2显示了压缩索引建议。 除了向用户提供简明的列键顺序{JOB，SEX，BIRTHDATE}，简明建议还包括上下文信息，以帮助确定建议的重要性。 诸如“建议使用查询的时间”和“查询估计的平均值”之类的列提供了一种感觉，即永久索引可能对该环境有多大益处。 而在“估计的索引创建时间”列中，有助于确定索引的创建是否需要是计划的活动。

图2.压缩索引建议

iSeries Navigator冷凝器界面

在iSeries Navigator中，可以在找到Index Advisor的任何位置找到冷凝索引建议操作。 图3显示了冷凝器的图形界面，通过右键单击架构对象可以访问该界面。 还可以从表对象访问冷凝器。

图3. iSeries Navigator冷凝器界面

要将iSeries导航器界面用于冷凝器的能力要求在客户端上安装最新的V5R4M0 iSeries Access Windows Service Pack 。

可编程冷凝器接口

除了iSeries Navigator界面之外，还可以使用SQL语句以编程方式访问索引建议冷凝器。 DB2 i5 / OS版在QSYS2模式中提供了一个新视图CondensedIndexAdvice。 清单2显示了新的CondensedIndexAdvice视图返回的数据。 由于CondensedIndexAdvice视图是通过用户定义的表函数（UDTF）实现的，因此该视图本身是只读的。 任何修改视图的尝试都会失败，并显示SQL0150错误。

清单2. CondensedIndexAdvice视图定义

QSYS2.CONDENSEDINDEXADVICE ( 	
TABLE_NAME              FOR COLUMN TABNAME      VARCHAR(258) CCSID 37 NOT NULL , 
TABLE_SCHEMA            FOR COLUMN TABSCHEMA    CHAR(10) CCSID 37 NOT NULL , 
SYSTEM_TABLE_NAME       FOR COLUMN SYS_TNAME    CHAR(10) CCSID 37 NOT NULL , 
PARTITION_NAME          FOR COLUMN TABPART      VARCHAR(128) CCSID 37 DEFAULT NULL , 
KEY_COLUMNS_ADVISED     FOR COLUMN KEYSADV      VARCHAR(16000) CCSID 37 DEFAULT NULL , 
INDEX_TYPE                                      CHAR(14) CCSID 37 DEFAULT NULL , 
LAST_ADVISED            FOR COLUMN LASTADV      TIMESTAMP DEFAULT NULL , 
TIMES_ADVISED           FOR COLUMN TIMESADV     BIGINT DEFAULT NULL , 
ESTIMATED_CREATION_TIME FOR COLUMN ESTTIME      INTEGER DEFAULT NULL , 
LOGICAL_PAGE_SIZE       FOR COLUMN "PAGESIZE"   INTEGER DEFAULT NULL , 
MOST_EXPENSIVE_QUERY    FOR COLUMN QUERYCOST    INTEGER DEFAULT NULL , 
AVERAGE_QUERY_ESTIMATE  FOR COLUMN QUERYEST     INTEGER DEFAULT NULL , 
TABLE_SIZE                                      BIGINT DEFAULT NULL , 
NLSS_TABLE_NAME         FOR COLUMN NLSSNAME     CHAR(10) CCSID 37 DEFAULT NULL , 
NLSS_TABLE_SCHEMA       FOR COLUMN NLSSSCHEMA   CHAR(10) CCSID 37 DEFAULT NULL )

从下面的SELECT语句可以看到，编写SQL语句来访问压缩建议是很简单的。 如前所述，压缩索引建议分析的范围可以是表级别或架构级别。

清单3. CondensedIndexAdvice查询示例

-- Condensed index advice for an entire schema, named CONDENSE
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE WHERE table_schema = 'CONDENSE' ;

-- Condensed index advice for a specific table, named EMPLOYEE in CONDENSE schema
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE 
       WHERE table_name = 'EMPLOYEE'  AND table_schema = 'CONDENSE';

-- Condensed index advice for a range of schemas, where the average 
-- query estimate driving the index advice is greater than 10 seconds
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE 
   WHERE table_schema LIKE 'Q%' AND average_query_estimate > 10 ;

新的CondensedIndexAdvice视图使用的Condense_Advice UDTF也可以由用户查询直接访问。 这是表函数的定义以及一个演示如何使用UDTF的简单示例。

清单4. Condense_Advice UDTF

CREATE FUNCTION QSYS2.Condense_Advice(TABLE_SCHEMA VARCHAR(128), 
                                      TABLE_NAME VARCHAR(128) ) 
  RETURNS TABLE(TABLE_PARTITION VARCHAR(128), KEY_COLUMNS_ADVISED VARCHAR(16000), 
                INDEX_TYPE CHAR(14), LAST_ADVISED TIMESTAMP, TIMES_ADVISED BIGINT, 
                ESTIMATED_CREATION_TIME INTEGER, LOGICAL_PAGE_SIZE INTEGER,
                MOST_EXPENSIVE_QUERY INTEGER, AVERAGE_QUERY_ESTIMATE INTEGER,
                TABLE_SIZE BIGINT, NLSS_TABLE_NAME CHAR(10),NLSS_TABLE_SCHEMA CHAR(10))
  LANGUAGE C 
  NOT DETERMINISTIC 
  READS SQL DATA 
  CALLED ON NULL INPUT 
  SCRATCHPAD 325064 
  DISALLOW PARALLEL 	
  FINAL CALL 
  CARDINALITY 1 
  EXTERNAL NAME 'QSYS/QDBSSUDF2(CONDENSE_ADVICE)' 
  PARAMETER STYLE DB2SQL;   


-- Query the condenser UDTF directly, providing selection criteria and ordering 
-- the results of the 15 most important condensed entries
     SELECT * FROM TABLE(QSYS2.CONDENSE_ADVICE('CRPDTA','B123456')) AS a
       WHERE a.table_size >(1024*1024) AND MONTH(last_advised)= MONTH(CURRENT TIMESTAMP)
     ORDER BY average_query_estimate DESC 
     FETCH FIRST 15 ROWS ONLY ;

摘要

使用索引建议（无论是压缩索引还是原始索引）都是改进任何索引策略的强大资源。 在执行此建议之前要考虑的重要组成部分是查看现有索引及其使用情况统计信息。 由于任何索引都存在隐含的维护成本，因此在进行性能调整时通常会尝试限制永久索引的数量。 此故事的士气是查看浓缩索引建议以识别改进的机会，同时还要在进行任何更改之前和之后查看索引使用情况统计信息。

---

翻译自: https://www.ibm.com/developerworks/data/library/techarticle/dm-0701forstie/index.html