db2像oracle一樣使用hints（guidelines）

db2像oracle一樣使用hints（guidelines）

db2像oracle一樣使用hints（guidelines）C:\DB2>more f3.sqlSELECT d.DEPTNAME,e.FIRSTNME,e.LASTNAMEFROM DEPARTMENT D,EMPLOYEE EWHERE d.DEPTNO = e.WORKDEPT AND e.EMPNO LIKE '0001%' /* */;
C:\DB2>C:\DB2>db2expln -d sample -f f3.sql -g -t -z ";"
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012Licensed Material - Program Property of IBMIBM DB2 Universal Database SQL and XQUERY Explain Tool
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012Licensed Material - Program Property of IBMIBM 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", "LIAO"

Statement:
SELECT d.DEPTNAME, e.FIRSTNME, e.LASTNAME FROM DEPARTMENT D, EMPLOYEE E WHERE d.DEPTNO =e.WORKDEPT AND e.EMPNO LIKE '0001%' /* */

Section Code Page = 1208
Estimated Cost = 13.634712Estimated Cardinality = 9.816054
Access Table Name = LIAO.EMPLOYEE ID = 2,6| Index Scan: Name = LIAO.PK_EMPLOYEE ID = 1| | Regular Index (Not Clustered)| | Index Columns:| | | 1: EMPNO (Ascending)| #Columns = 4| Skip Inserted Rows| Avoid Locking Committed Data| Currently Committed for Cursor Stability| Evaluate Predicates Before Locking for Key| #Key Columns = 1| | Start Key: Inclusive Value| | | 1: '0001'| | Stop Key: Inclusive Value| | | 1: '0001　　'| Data Prefetch: Sequential(0), Readahead| Index Prefetch: None| Lock Intents| | Table: Intent Share| | Row : Next Key Share| Sargable Predicate(s)| | Process Build Table for Hash JoinHash Join| Early Out: Single Match Per Inner Row| Estimated Build Size: 4000| Estimated Probe Size: 4000| Access Table Name = LIAO.DEPARTMENT ID = 2,5| | #Columns = 2| | Skip Inserted Rows| | Avoid Locking Committed Data| | Currently Committed for Cusor Stability| | May participate in Scan Sharing structures| | Scan may start anywhere and wrap, for completion| | Fast scan, for purposes of scan sharing management| | Scan can be throttled in scan sharing management| | Relation Scan| | | Prefetch: Eligible| | Lock Intents| | | Table: Intent Share| | | Row : Next Key Share| | Sargable Predicate(s)| | | Process Probe Table for Hash JoinReturn Data to Application| #Columns = 3
End of section

Optimizer Plan:
Rows Operator (ID) Cost
9.81605 RETURN ( 1) 13.6347 | 9.81605 HSJOIN ( 2) 13.6347 / \ 14 9.81605 TBSCAN FETCH ( 3) ( 4) 6.81583 6.8181 | / \ 14 9.81605 42 Table: IXSCAN Table: LIAO ( 5) LIAO DEPARTMENT 0.00986447 EMPLOYEE | 42 Index: LIAO PK_EMPLOYEE


C:\DB2>db2set DB2_OPTPROFILE=YES
C:\DB2>db2set -all[e] DB2PATH=C:\v105\IBM\SQLLIB[i] DB2_OPTPROFILE=YES[i] DB2INSTOWNER=LIAO-PC[i] DB2PORTRANGE=60000:60005[i] DB2INSTPROF=C:\V105\PROGRAMDATA\\IBM\DB2\DB2COPY1[i] DB2COMM=TCPIP[g] DB2_EXTSECURITY=NO[g] DB2_COMMON_APP_DATA_PATH=C:\v105\ProgramData\[g] DB2SYSTEM=LIAO-PC[g] DB2PATH=C:\v105\IBM\SQLLIB[g] DB2INSTDEF=DB2[g] DB2ADMINSERVER=DB2DAS00
C:\DB2>db2stop2015-12-04 00:43:34 0 0 SQL1025N 未停止數據庫，因為數據庫仍是活動的。SQL1025N 未停止數據庫，因為數據庫仍是活動的。
C:\DB2>db2stop force2015-12-04 00:43:40 0 0 SQL1064N DB2STOP 處理成功。SQL1064N DB2STOP 處理成功。
C:\DB2>db2start2015-12-04 00:43:48 0 0 SQL1063N DB2START 處理成功。SQL1063N DB2START 處理成功。
C:\DB2>db2expln -d sample -f f3.sql -g -t -z ";"
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012Licensed Material - Program Property of IBMIBM DB2 Universal Database SQL and XQUERY Explain Tool
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012Licensed Material - Program Property of IBMIBM 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", "LIAO"

Statement:
SELECT d.DEPTNAME, e.FIRSTNME, e.LASTNAME FROM DEPARTMENT D, EMPLOYEE E WHERE d.DEPTNO =e.WORKDEPT AND e.EMPNO LIKE '0001%' /* */

Section Code Page = 1208
Estimated Cost = 13.648438Estimated Cardinality = 9.816054
Access Table Name = LIAO.DEPARTMENT ID = 2,5| #Columns = 2| Skip Inserted Rows| Avoid Locking Committed Data| Currently Committed for Cursor Stability| May participate in Scan Sharing structures| Scan may start anywhere and wrap, for completion| Fast scan, for purposes of scan sharing management| Scan can be throttled in scan sharing management| Relation Scan| | Prefetch: Eligible| Lock Intents| | Table: Intent Share| | Row : Next Key Share| Sargable Predicate(s)| | Insert Into Sorted Temp Table ID = t1| | | #Columns = 2| | | #Sort Key Columns = 1| | | | Key 1: DEPTNO (Ascending)| | | Sortheap Allocation Parameters:| | | | #Rows = 14.000000| | | | Row Width = 28| | | PipedSorted Temp Table Completion ID = t1Access Temp Table ID = t1| #Columns = 2| Relation Scan| | Prefetch: EligibleMerge Join| Access Table Name = LIAO.EMPLOYEE ID = 2,6| | #Columns = 4| | Skip Inserted Rows| | Avoid Locking Committed Data| | Currently Committed for Cursor Stability| | May participate in Scan Sharing structures| | Scan may start anywhere and wrap, for completion| | Fast scan, for purposes of scan sharing management| | Scan can be throttled in scan sharing management| | Relation Scan| | | Prefetch: Eligible| | Lock Intents| | | Table: Intent Share| | | Row : Next Key Share| | Sargable Predicate(s)| | | #Predicates = 1| | | Insert Into Sorted Temp Table ID = t2| | | | #Columns = 3| | | | #Sort Key Columns = 1| | | | | Key 1: WORKDEPT (Ascending)| | | | Sortheap Allocation Parameters:| | | | | #Rows = 10.000000| | | | | Row Width = 32| | | | Piped| Sorted Temp Table Completion ID = t2| Access Temp Table ID = t2| | #Columns = 3| | Relation Scan| | | Prefetch: EligibleReturn Data to Application| #Columns = 3
End of section

Optimizer Plan:
Rows Operator (ID) Cost
9.81605 RETURN ( 1) 13.6484 | 9.81605 MSJOIN ( 2) 13.6484 / \-\ 14 * TBSCAN | ( 3) 9.81605 6.81692 TBSCAN | ( 7) 14 6.83002 SORT | ( 4) 9.81605 6.81674 SORT | ( 8) 14 6.82983 TBSCAN | ( 5) 9.81605 6.81583 TBSCAN | ( 9) 14 6.82912 Table: | LIAO 42 DEPARTMENT Table: LIAO EMPLOYEE



C:\DB2>