DB2中SQL语句的执行计划是DB2优化器根据统计信息来制定的,有的时候,需要人工干预,比如需要强制使用索引扫描。这时候可以使用优化概要来实现。下面的例子中,SQL原本使用了表扫描,在使用了优化概要强制规定使用索引扫描之后,执行计划变成了索引扫描。
测试环境
$ db2level
DB21085I This instance or install (instance name, where applicable: "e105q5a") uses "64" bits and DB2 code release "SQL10055" with level identifier "0606010E".
Informational tokens are "DB2 v10.5.0.5", "s141128", "IP23626", and Fix Pack "5".
Product is installed at "/opt/IBM/db2/V10.5.5".
未使用优化概要时的执行计划
新建一张分区表,并建立非分区索引,发现SQL语句的访问计划并未使用索引扫描,而是使用了TBSCAN
$ db2 connect to sample
$ db2 "create tablespace tbs1"
$ db2 "create tablespace tbs2"
$ db2 "create tablespace tbs3"
$ db2 "CREATE TABLE T1 (ID INTEGER , NAME CHAR(20) ) PARTITION BY RANGE(ID) (PART P1 STARTING(1) ENDING(40) IN TBS1, PART P2 STARTING(41) ENDING(80) IN TBS2, PART P3 STARTING(81) ENDING(120) IN TBS3)"
$ db2 "CREATE INDEX IDX1 ON T1 (ID) NOT PARTITIONED"
$ db2 "insert into t1 values(1,'a'),(2,'b'),(41,'c'),(42,'d'),(81,'e'),(82,'f')"
$ db2 "runstats on table t1 and indexes all"
$ db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL
$ db2 "set current explain mode explain"
$ db2 "select name from t1 where id = 42"
$ db2 "set current explain mode no"
$ db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -o output1.txt
执行计划如下:
$ cat output1.txt
Original Statement:
------------------
select
name
from
t1
where
id = 42
Optimized Statement:
-------------------
SELECT
Q1.NAME AS "NAME"
FROM
E105Q5A.T1 AS Q1
WHERE
(Q1.ID = 42)
Access Plan:
-----------
Total Cost: 6.81003
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
6.81003
1
|
6
DP-TABLE: E105Q5A
T1
Q1
创建优化概要文件
需要先创建一个表systools.opt_profile。还要有一个xml文件:a1.xml中的E105Q5A是实例名,也是默认的模式名。OPTGUIDELINES规定表T1使用索引扫描,使用的索引为IDX1
$ db2 "create table systools.opt_profile (schema VARCHAR(128) not null, name varchar(128) not null, profile blob (2M) not null, primary key (schema, name))"
$ cat a1.xml
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="10.5.0.5">
<STMTPROFILE ID="Use Index Scan instead of Table Scan">
<STMTKEY SCHEMA="E105Q5A">
<![CDATA[select name from t1 where id = 42]]>
</STMTKEY>
<OPTGUIDELINES>
<IXSCAN TABLE="T1" INDEX="IDX1"/>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
$ cat insert.del
"E105Q5A", "PROF1", "a1.xml"
$ db2 "import from insert.del of del modified by lobsinfile insert into systools.opt_profile"
$ db2set DB2_OPTPROFILE=YES
$ db2 terminate
$ db2stop
$ db2start
使用优化概要文件
$ db2 connect to sample
$ db2 set current optimization profile='PROF1'
$ db2 set current explain mode explain
$ db2 "select name from t1 where id = 42"
$ db2 set current explain mode no
$ db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -o output2.txt
再次查看执行计划,发现使用了索引扫描:
$ cat output2.txt
Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
E105Q5A.PROF1
STMTPROF: (Statement Profile Name)
Use Index Scan instead of Table Scan
Original Statement:
------------------
select
name
from
t1
where
id = 42
Optimized Statement:
-------------------
SELECT
Q1.NAME AS "NAME"
FROM
E105Q5A.T1 AS Q1
WHERE
(Q1.ID = 42)
Access Plan:
-----------
Total Cost: 6.81152
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
FETCH
( 2)
6.81152
1
/-----+-----\
1 6
IXSCAN DP-TABLE: E105Q5A
( 3) T1
0.00173777 Q1
0
|
6
INDEX: E105Q5A
IDX1
Q1
可以看到,根据DB2优化器的估计,这里使用IXSCAN比使用TBSCAN的cost还要高,所以DB2选择了表扫描。在后续的测试中,发现当表的记录数很多的时候,即使没有使用优化概要,DB2也会选择索引扫描。
参考链接
http://www-01.ibm.com/support/docview.wss?uid=swg21430976其他的说明:
https://www.ibm.com/developerworks/data/library/techarticle/dm-0612chen/
https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1008pengxq/
https://www.ibm.com/developerworks/data/library/techarticle/dm-1202storedprocedure/
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0060612.html