DB2技巧:强制优化器的使用

原创|其它|编辑:郝浩|2009-06-16 11:25:17.000|阅读 440 次

概述:很多开发和数据库管理人员都在为优化器问题烦恼不已。尽管很多时候优化器问题都是可以通过常规手段解决的,但是在某些特殊情况下,或者紧急情况(没有时间完整地分析问题)下。

# 界面/图表报表/文档/IDE等千款热门软控件火热销售中 >>

  很多开发和数据库管理人员都在为优化器问题烦恼不已。尽管很多时候优化器问题都是可以通过常规手段解决的,但是在某些特殊情况下,或者紧急情况(没有时间完整地分析问题)下,用户可以使用profile暂时强制优化器使用某些特定的操作。。。

  下面是一个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.

  <---------别忘了runstats

  D:\TEMP>db2 "runstats on table taoewang.mytable"
  DB20000I The RUNSTATS command completed successfully.
  D:\TEMP>db2 "runstats on table taoewang.mytable for indexes all"
  DB20000I The RUNSTATS command completed successfully.

  <-----------试试看

  D:\TEMP>db2 "SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000"
  NAME
  ID SALARY
  PHONE
  -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------ --------------------
  whiterain
  123 +1.02000000000000E+002 123-458
  1 record(s) selected.

  <--------创建explain表

  D:\TEMP>cd D:\Program Files\IBM\SQLLIB\MISC
  D:\Program Files\IBM\SQLLIB\MISC>db2 -tvf EXPLAIN.DDL
  .....
  D:\Program Files\IBM\SQLLIB\MISC>cd D:\temp

  <----------看一看现在的访问计划

  D:\TEMP>db2 set current explain mode explain
  DB20000I The SQL command completed successfully.
  D:\TEMP>db2 "SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000"
  SQL0217W The statement was not executed as only Explain information requests
  are being processed. SQLSTATE=01604
  D:\TEMP>db2 set current explain mode no
  DB20000I The SQL command completed successfully.
  D:\TEMP>db2exfmt -d sampel2 -g TIC -w -1 -n % -s % -# 0 -o output.txt
  DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
  Licensed Material - Program Property of IBM
  IBM DATABASE 2 Explain Table Format Tool
  Connecting to the Database.
  Connect to Database Successful.
  Binding package - Bind was Successful
  Output is in output.txt.
  Executing Connect Reset -- Connect Reset was Successful.
  D:\TEMP>uedit32 output.txt

  <-------------现在优化器用了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


标签:

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,如果存在内容上的异议请邮件反馈至chenjj@evget.com

文章转载自:IT专家网论坛

为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
扫码咨询


添加微信 立即咨询

电话咨询

客服热线
023-68661681

TOP