本文共 6890 字，大约阅读时间需要 22 分钟。
db2像oracle一样使用hints（guidelines） SELECT d.DEPTNAME,e.FIRSTNME,e.LASTNAME FROM DEPARTMENT D,EMPLOYEE E WHERE d.DEPTNO = e.WORKDEPT AND e.EMPNO LIKE '0001%' /*<OPTGUIDELINES> <TBSCAN TABLE='D'/> <TBSCAN TABLE='E'/> C:\DB2>db2expln -d sample -f f3.sql -g -t -z ";" DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012 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 Intra-Partition Parallel = No SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", SELECT d.DEPTNAME, e.FIRSTNME, e.LASTNAME FROM DEPARTMENT D, EMPLOYEE E WHERE d.DEPTNO =e.WORKDEPT AND e.EMPNO LIKE '0001%' /*<OPTGUIDELINES> <MSJOIN> <TBSCAN TABLE='D' /> <TBSCAN TABLE='E' /> </MSJOIN> </OPTGUIDELINES>*/ Estimated Cost = 13.634712 Estimated Cardinality = 9.816054 Access Table Name = LIAO.EMPLOYEE ID = 2,6 | Index Scan: Name = LIAO.PK_EMPLOYEE ID = 1 | | Regular Index (Not Clustered) | | | 1: EMPNO (Ascending) | Avoid Locking Committed Data | Currently Committed for Cursor Stability | Evaluate Predicates Before Locking for Key | | Start Key: Inclusive Value | | Stop Key: Inclusive Value | Data Prefetch: Sequential(0), Readahead | | Process Build Table for Hash Join | Early Out: Single Match Per Inner Row | Estimated Build Size: 4000 | Estimated Probe Size: 4000 | Access Table Name = LIAO.DEPARTMENT ID = 2,5 | | 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 | | | Table: Intent Share | | | Row : Next Key Share | | Sargable Predicate(s) | | | Process Probe Table for Hash Join Return Data to Application DEPARTMENT 0.00986447 EMPLOYEE C:\DB2>db2set DB2_OPTPROFILE=YES [e] DB2PATH=C:\v105\IBM\SQLLIB [i] DB2PORTRANGE=60000:60005 [i] DB2INSTPROF=C:\V105\PROGRAMDATA\\IBM\DB2\DB2COPY1 [g] DB2_COMMON_APP_DATA_PATH=C:\v105\ProgramData\ [g] DB2PATH=C:\v105\IBM\SQLLIB [g] DB2ADMINSERVER=DB2DAS00 2015-12-04 00:43:34 0 0 SQL1025N 未停止数据库，因为数据库仍是活动的。 SQL1025N 未停止数据库，因为数据库仍是活动的。 2015-12-04 00:43:40 0 0 SQL1064N DB2STOP 处理成功。 2015-12-04 00:43:48 0 0 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, 2012 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012 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 Intra-Partition Parallel = No SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", SELECT d.DEPTNAME, e.FIRSTNME, e.LASTNAME FROM DEPARTMENT D, EMPLOYEE E WHERE d.DEPTNO =e.WORKDEPT AND e.EMPNO LIKE '0001%' /*<OPTGUIDELINES> <MSJOIN> <TBSCAN TABLE='D' /> <TBSCAN TABLE='E' /> </MSJOIN> </OPTGUIDELINES>*/ Estimated Cost = 13.648438 Estimated Cardinality = 9.816054 Access Table Name = LIAO.DEPARTMENT ID = 2,5 | 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 | | Insert Into Sorted Temp Table ID = t1 | | | #Sort Key Columns = 1 | | | | Key 1: DEPTNO (Ascending) | | | Sortheap Allocation Parameters: | | | | #Rows = 14.000000 Sorted Temp Table Completion ID = t1 Access Temp Table ID = t1 | Access Table Name = LIAO.EMPLOYEE ID = 2,6 | | 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 | | | Table: Intent Share | | | Row : Next Key Share | | Sargable Predicate(s) | | | Insert Into Sorted Temp Table ID = t2 | | | | #Sort Key Columns = 1 | | | | | Key 1: WORKDEPT (Ascending) | | | | Sortheap Allocation Parameters: | | | | | #Rows = 10.000000 | Sorted Temp Table Completion ID = t2 | Access Temp Table ID = t2 Return Data to Application 1.Oracle hint在DB2中的实现-访问计划定制 转载地址：http://uktai.baihongyu.com/