db2&unix使用总结（四）

1、索引：

建立索引是用来提高查询性能最常用的方法。对于一个特定的查询，可以为某一个表所有出现在查询中的列建立一个联合索引，包括出现在 select 子句和条件语句中的列。但简单的建立一个覆盖所有列的索引并不一定能有效提高查询，因为在多列索引中列的顺序是非常重要的。这个特性是由于索引的 B+ 树结构决定的。一般情况下，要根据谓词的选择度来排列索引中各列的位置，选择度大的谓词所使用的列放在索引的前面，把那些只存在与 select 子句中的列放在索引的最后，因为这会造成Non-matchingIndex Scan扫描和matching Index Scan 不同的结果。

例如，对于：select add_date from temp.customer where city = 'WASHINGTON' andcntry_code = 'USA';

应建立索引的顺序为：index1(city,cntry_code,add_date)，而不能把add_date放在前面。

避免在建有索引的列上使用函数

在那些需要被排序的列上创建索引：distinct 和 group by和order by

一般情况下，当查询的 where 子句中存在主键的谓词我们就可以创建带有 include 列的唯一索引，形成纯索引访问来提高查询性能。注意 include 只能用在创建唯一性索引中

创建降序索引可以提高max函数性能：

create indextemp.employee_i1 on temp.employee(add_date desc)

同理还有升序索引，双向索引ALLOW REVERSE SCANS，但以后数据更新时的维护成本较高。

重组索引：REORG INDEXES ALL FOR TABLE table_name

根据某索引重组表：REORG TABLE table_name INDEX index_name

Not in 由内向外，子查询的复杂度影响其性能

Not exist 由外向内，嵌套的复杂度影响其性能

2、游标的使用：

declarecontinue handler for not found

   begin

     set v_notfound = 1;

   end;

 

declare cursor1 cursor with hold forselect market_code from tb_market_code    for update;

open cursor1;

set v_notfound=0;

fetch cursor1 into v_market_code;

while v_notfound=0 Do

          --work

          set v_notfound=0;

fetch cursor1 into v_market_code;

end while;

close cursor1;

修改游标的当前记录：update tb_market_code set market_code='0' where current of cursor1;

   前提是游标是可修改的：declarecursor1 cursor for select market_code from tb_market_code for update;

3、计算某调度流程一段时间的5日平均运行时长，并与当前运行时长一起展示，形成类似于股票行情的K线图。

SCHEDULE.RUNTIMEFLOWLOG表结构如下：

名称	数据类型	长度	备注
LOGID	DECIMAL	20	ID，主键
FLOWID	DECIMAL	11	流程ID
STATUS	DECIMAL	11	流程状态
STARTTIME	TIMESTAMP	10	开始时间
ENDTIME	TIMESTAMP	10	结束时间
DATATIME	VARCHAR	32	数据时间
FLOWTYPE	DECIMAL	11

两个计算字段：
cursec：当前运行时长，单位：秒
avgsec：5日平均运行时长，单位：秒
SQL如下（以DB2为例）：

SELECT logid,
status,
datatime,
starttime,
MIDNIGHT_SECONDS (ENDTIME) - MIDNIGHT_SECONDS (STARTTIME) cursec,
AVG(MIDNIGHT_SECONDS (ENDTIME) - MIDNIGHT_SECONDS (STARTTIME))
OVER (PARTITION BY flowid, status
ORDER BY logid DESC
ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING) avgsec
FROM SCHEDULE.RUNTIMEFLOWLOG
WHERE FLOWID = 471

ORDER BY logid DESC;

如为Oracle，只需要将cursec和avgsec计算秒数的部分做相应调整：
(CAST(ENDTIME AS DATE)-CAST(STARTTIME AS DATE))*3600*24
而分析函数的用法此处Oracle和DB2均通用，不需要任何调整。