Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

db2expln

Posted on 2009-07-16 17:34 Prayer 阅读(607) 评论(0)  编辑 收藏 引用 所属分类: DB2

d:\>db2expln -d wz20901 -u wzgladm wzglpass -t
-q "update MAT_MATERIAL set GATHERPLAN_ID=null  where GATHERPLAN_ID =2005178  or GATHERPLAN_ID=32 "

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "WZGLADM"


SQL Statement:

  update MAT_MATERIAL set GATHERPLAN_ID=null
  where GATHERPLAN_ID =2005178 or GATHERPLAN_ID=32


Section Code Page = 1386

Estimated Cost = 15083.518555
Estimated Cardinality = 940.719971

Access Table Name = WZGLADM.MAT_MATERIAL  ID = 5,27
|  #Columns = 1
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Exclusive
|  |  Row  : Exclusive
Nested Loop Join
|  Data Stream 1:
|  |  Not Piped
|  |  Table Constructor
|  |  |  2-Row(s)
|  |  Insert Into Temp Table  ID = t1
|  |  |  #Columns = 0
|  End of Data Stream 1
|  Access Temp Table  ID = t1
|  |  #Columns = 0
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
Update:  Table Name = WZGLADM.MAT_MATERIAL  ID = 5,27

End of section

 

d:\>

 


脚本:


d:\>db2expln -d wz20901 -u wzgladm wzglpass -t -z ; -f tmp.sql

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "WZGLADM"


SQL Statement:

  select GATHERPLAN_ID
  from MAT_MATERIAL
  where GATHERPLAN_ID=2005178 or GATHERPLAN_ID=5 or GATHERPLAN_ID=3


Section Code Page = 1386

Estimated Cost = 128.615417
Estimated Cardinality = 1411.079956

Table Constructor
|  3-Row(s)
Nested Loop Join
|  Access Table Name = WZGLADM.MAT_MATERIAL  ID = 5,27
|  |  Index Scan:  Name = WZGLADM.MAT_IDX_GPLAN_ID  ID = 4
|  |  |  Regular Index (Not Clustered)
|  |  |  Index Columns:
|  |  |  |  1: GATHERPLAN_ID (Ascending)
|  |  #Columns = 1
|  |  #Key Columns = 1
|  |  |  Start Key: Inclusive Value
|  |  |  |  |  1: ?
|  |  |  Stop Key: Inclusive Value
|  |  |  |  |  1: ?
|  |  Index-Only Access
|  |  Index Prefetch: None
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Index Predicate(s)
|  |  |  Return Data to Application
|  |  |  |  #Columns = 1
Return Data Completion

End of section

 


对于稍微复杂的sql,建议使用questcentral中的 SQL turning 功能,比较直观。


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/njhart2003/archive/2008/07/23/2696295.aspx


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理