db2 查看执行计划

--db2执行计划学习
--（1）
vi 1.sql
selecT * from child where chil_id=184
db2expln -d vaccyn -t -f 1.sql


---------执行计划
[db2inst1@localhost ~]$ db2expln -d vaccyn -t -f 1.sql


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


DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY 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", "SYSIBMADM", 
                                   "DB2INST1"




Statement:
  
  selecT * 
  from child 
  where chil_id=184




Section Code Page = 1386


Estimated Cost = 15.176693
Estimated Cardinality = 1.000000


Access Table Name = DB2INST1.CHILD  ID = -6,-32768
|  Index Scan:  Name = SYSIBM.SQL130402190604510  ID = 2
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: CHIL_ID (Ascending)
|  #Columns = 110
|  Clustered by Dimension for Block Index Access
|  Data-Partitioned Table
|  Single Record
|  Fully Qualified Unique Key
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  All data partitions will be accessed
|  Evaluate Predicates Before Locking for Key
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 184
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 184
|  Data Prefetch: None
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Share
|  |  Block: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 111
Return Data Completion
--（2）
--执行计划学习
--
db2expln -d tpch -q "select * from child where chil_id=15053" -g -t;
[db2inst1@localhost ~]$ db2expln -d vaccyn -q "select * from child where chil_id=10843" -t -g


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


DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY 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", "SYSIBMADM", 
                                   "DB2INST1"




Statement:
  
  select * 
  from child 
  where chil_id=10843




Section Code Page = 1386


Estimated Cost = 15.176693
Estimated Cardinality = 1.000000


Access Table Name = DB2INST1.CHILD  ID = -6,-32768
|  Index Scan:  Name = SYSIBM.SQL130402190604510  ID = 2
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: CHIL_ID (Ascending)
|  #Columns = 110
|  Clustered by Dimension for Block Index Access
|  Data-Partitioned Table
|  Single Record
|  Fully Qualified Unique Key
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  All data partitions will be accessed
|  Evaluate Predicates Before Locking for Key
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 10843
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 10843
|  Data Prefetch: None
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Share
|  |  Block: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 111
Return Data Completion


End of section




Optimizer Plan:


                  Rows   
                Operator 
                  (ID)   
                  Cost   
                        
                   1    
                RETURN  
                 ( 1)   
                15.1767 
                  |     
                   1    
                 FETCH  
                 ( 2)   
                15.1767 
               /       \
          1          1.72774e+06 
       IXSCAN        Table:      
        ( 3)         DB2INST1    
       7.61114       CHILD       
         |          
    1.72774e+06     
 Index:             
 SYSIBM             
 SQL130402190604510 
[db2inst1@localhost ~]$