以下的文章主要描述的是在實際操作中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強制優化器的使用技的介紹，望你能有所收獲。
