以下的文章主要描述的是在实际操作中DB2强制优化器的使用技巧，很多开发与数据库管理人员都在为优化器问题发牢骚。尽管很多时候优化器问题一般都是可以通过常规手段解决的，但是在某些特殊情况下。
或者紧急情况(没有时间完整地分析问题)下，用户可以使用profile暂时强制优化器使用某些特定的操作。。。
下面是一个step by step的例子，简单地说明了怎样DB2强制优化器使用table scan
- DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
- level identifier "02010107".
- Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".
- Product is installed at "D:\PROGRA~1\IBM\SQLLIB\" with DB2 Copy Name
- "DB2COPY1".
创建一个数据库
- D:\TEMP\db2service.perf1>db2 create db sampel2DB20000I The CREATE DATABASE command completed successfully.
- D:\TEMP\db2service.perf1>db2 connect to sampel2
- Database Connection Information
- Database server = DB2/NT 9.1.0
- SQL authorization ID = TAOEWANG
- Local database alias = SAMPEL2
创建优化器系统表
- D:\TEMP\db2service.perf1>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))"- DB20000I The SQL command completed successfully.
- D:\TEMP\db2service.perf1>cd ..
创建用户表
- D:\TEMP>db2 "create table mytable (name varchar(128), id integer, salary float,phone varchar(20))"
- DB20000I The SQL command completed successfully.
插入一些数据
- D:\TEMP>db2 "insert into mytable values ('tao wang', 12345, 100, '123-456')"
- DB20000I The SQL command completed successfully.
- D:\TEMP>db2 "insert into mytable values ('diablo2', 12346, 101, '123-457')"
- DB20000I The SQL command completed successfully.
- D:\TEMP>db2 "insert into mytable values ('whiterain', 123, 102, '123-458')"
- DB20000I The SQL command completed successfully.
- D:\TEMP>db2 "insert into mytable values ('ganquan', 1255, 104, '123-459')"
- DB20000I The SQL command completed successfully.
DB2强制优化器的使用
下面是一个step by step的例子，简单地说明了怎样强制优化器使用table scan
- DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
- level identifier "02010107".
- Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".
- Product is installed at "D:\PROGRA~1\IBM\SQLLIB\" with DB2 Copy Name
- "DB2COPY1".
创建一个数据库
- D:\TEMP\db2service.perf1>db2 create db sampel2DB20000I The CREATE DATABASE command completed successfully.
- D:\TEMP\db2service.perf1>db2 connect to sampel2
- Database Connection Information
- Database server = DB2/NT 9.1.0
- SQL authorization ID = TAOEWANG
- Local database alias = SAMPEL2
创建优化器系统表
- D:\TEMP\db2service.perf1>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))"- DB20000I The SQL command completed successfully.
- D:\TEMP\db2service.perf1>cd ..
创建用户表
- D:\TEMP>db2 "create table mytable (name varchar(128), id integer, salary float,phone varchar(20))"
- DB20000I The SQL command completed successfully.
插入一些数据
- D:\TEMP>db2 "insert into mytable values ('tao wang', 12345, 100, '123-456')"
- DB20000I The SQL command completed successfully.
- D:\TEMP>db2 "insert into mytable values ('diablo2', 12346, 101, '123-457')"
- DB20000I The SQL command completed successfully.
- D:\TEMP>db2 "insert into mytable values ('whiterain', 123, 102, '123-458')"
- DB20000I The SQL command completed successfully.
- D:\TEMP>db2 "insert into mytable values ('ganquan', 1255, 104, '123-459')"
- DB20000I The SQL command completed successfully.
现在优化器用了index scan
- Original Statement:
- ------------------
- SELECT *
- FROM TAOEWANG.MYTABLE
- WHERE ID < 1000
- Optimized Statement:
- -------------------
- SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS
- "PHONE"
- FROM TAOEWANG.MYTABLE AS Q1
- WHERE (Q1.ID < 1000)
- Access Plan:
- -----------
- Total Cost: 7.56853
- Query Degree: 1
- Rows
- RETURN
- ( 1)
- Cost
- I/O
- |
- 1
- FETCH
- ( 2)
- 7.56853
- 1
- /----+---\
- 1 4
- IXSCAN TABLE: TAOEWANG
- ( 3) MYTABLE
- 0.00630865
- 0
- |
- 4
- INDEX: TAOEWANG
- IX1
以上的相关内容就是对DB2强制优化器的使用技的介绍，望你能有所收获。
【编辑推荐】
