9 DB2 优化器 DB2 SQL select c1 c2 from ( DB2 )??? DB2?!?, no no DB2 I/O ( transrate overhead ) SQL DML (INSERT UPDATE DELETE) DB2 (access plan)
DB2 (join) SQL DB2 11 SQL DB2 SQL 9.1 DB2 DB2 ( 728 747 ) SQL ( 9-1 9-2) DB2 SQL DB2 DB2 SQL DB2 DB2 SQL DB2 ( DB2 ) DB2 DB2 DB2 SQL DB2 (1) SQL (2) SQL 326
9 DB2 (3) SQL (4) SQL SQL IBM (CBO) DB2 4 CPU I/O DB2 SQL 9-1 DB2 9-2 DB2 327
DB2 9.2 SQL SQL 9-3 SQL SQL 1. 2. 3. 4. 5. 6. SQL 7. Visual Explain db2exfmt db2expln 9-3 SQL 328
9 DB2 9-3 SQL 1. SQL 2. YEAR 3. (avg sum max min count ) 4. ( ) 5. 329
DB2 (avg sum max min count ) (SMP) 6. SQL SQL SQL SQL 7. (section) ( ) ( ) SQL ( ) REOPT ( ) SQL SQL (syscat.statements) db2expln RUNSTATS RUNSTATS RUNSTATS (FPAGES) ACTIVE_BLOCKS 330
9 DB2 9.3 DB2 DB2 Query Rewriter( ) SQL (Query Rewriter) Estimator( ) 3 1. Selectivity rows 0.0~1.0 0 row statistics Estimator selectivity '=' selectivity '<' (statistics) last_name = 'Smith' Estimator last_name distinct ( last_name distinct ) selectivity 2. Cardinality row set 3. Cost Cost Disk I/O CPU usage (timeron) Plan Generator( ) cost 9.3.1 SQL ( ) SQL db2expln dynexpln db2exfmt ( SELECT ) SQL 331
DB2 SELECT SELECT SELECT (avg max sum count min) DISTINCT DISTINCT / SQL OR IN OR IN SQL IN OR 9.3.2 EMPLOYEE $35,000 332
9 DB2 CREATE VIEW EMP_EDUCATION (EMPNO, FIRSTNME, LASTNAME, EDLEVEL) AS SELECT EMPNO, FIRSTNME, LASTNAME, EDLEVEL FROM EMPLOYEE WHERE EDLEVEL > 17 CREATE VIEW EMP_SALARIES (EMPNO, FIRSTNAME, LASTNAME, SALARY) AS SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY > 35000 $35,000 SELECT E1.EMPNO, E1.FIRSTNME, E1.LASTNAME, E1.EDLEVEL, E2.SALARY FROM EMP_EDUCATION E1, EMP_SALARIES E2 WHERE E1.EMPNO = E2.EMPNO SELECT E1.EMPNO, E1.FIRSTNME, E1.LASTNAME, E1.EDLEVEL, E2.SALARY FROM EMPLOYEE E1, EMPLOYEE E2 WHERE E1.EMPNO = E2.EMPNO AND E1.EDLEVEL > 17 AND E2.SALARY > 35000 SELECT SELECT SQL SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO FROM EMPLOYEE WHERE WORKDEPT IN (SELECT DEPTNO FROM DEPARTMENT WHERE DEPTNAME = 'OPERATIONS') SELECT DISTINCT EMPNO, FIRSTNME, LASTNAME, PHONENO FROM EMPLOYEE EMP, DEPARTMENT DEPT WHERE EMP.WORKDEPT = DEPT.DEPTNO AND DEPT.DEPTNAME = 'OPERATIONS' 333
DB2 SELECT E1.EMPNO, E1.FIRSTNME, E1.LASTNAME, E1.EDLEVEL, E2.SALARY FROM EMPLOYEE E1,EMPLOYEE E2 WHERE E1.EMPNO = E2.EMPNO AND E1.EDLEVEL > 17 AND E2.SALARY > 35000 SQL SELECT EMPNO, FIRSTNME, LASTNAME, EDLEVEL, SALARY FROM EMPLOYEE WHERE EDLEVEL > 17 AND SALARY > 35000 EMPLOYEE DEPARTMENT CREATE VIEW PEPLVIEW AS SELECT FIRSTNME, LASTNAME, SALARY, DEPTNO, DEPTNAME, MGRNO FROM EMPLOYEE E DEPARTMENT D WHERE E.WORKDEPT = D.DEPTNO SELECT LASTNAME, SALARY FROM PEPLVIEW SELECT LASTNAME, SALARY FROM EMPLOYEE WHERE WORKDEPT NOT NULL; (avg max sum count min) SQL SELECT SUM(SALARY+BONUS+COMM) AS OSUM, AVG(SALARY+BONUS+COMM) AS OAVG, 334
9 DB2 COUNT(*) AS OCOUNT FROM EMPLOYEE; SELECT OSUM,OSUM/OCOUNT OCOUNT FROM (SELECT SUM(SALARY+BONUS+COMM) AS OSUM COUNT(*) AS OCOUNT FROM EMPLOYEE) AS SHARED_AGG; 2 2 1 1 9.3.3 DISTINCT EMPNO EMPLOYEE SELECT DISTINCT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE DISTINCT SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE SQL DISTINCT ( ) D11 CREATE VIEW D11_EMPLOYEE (EMPNO, FIRSTNME, LASTNAME, PHONENO, SALARY, BONUS, COMM) AS SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO, SALARY, BONUS, COMM FROM EMPLOYEE WHERE WORKDEPT = 'D11' SELECT FIRSTNME, PHONENO FROM D11_EMPLOYEE WHERE LASTNAME = 'BROWN'; LASTNAME = 'BROWN' D11_EMPLOYEE SELECT FIRSTNME, PHONENO FROM EMPLOYEE WHERE LASTNAME = 'BROWN' AND WORKDEPT = 'D11' UNION GROUP BY ( ) 335
DB2 9.3.4 E01 SELECT DEPT.DEPTNAME DEPT.MGRNO, EMP.LASTNAME, PROJ.PROJNAME FROM DEPARTMENT DEPT, EMPLOYEE EMP, PROJECT PROJ WHERE DEPT.ADMRDEPT = 'E01' AND DEPT.MGRNO = EMP.EMPNO AND EMP.EMPNO = PROJ.RESPEMP DEPT.MGRNO = PROJ.RESPEMP E00 SELECT EMPNO, LASTNAME, FIRSTNAME, DEPTNO, DEPTNAME FROM EMPLOYEE EMP, DEPARTMENT DEPT WHERE EMP.WORKDEPT = DEPT.DEPTNO AND DEPT.DEPTNO > 'E00'; EMP.WORKDEPT > 'E00' OR IN OR SELECT * FROM EMPLOYEE WHERE DEPTNO = 'D11' OR DEPTNO = 'D21' OR DEPTNO = 'E21' DEPTNO OR IN SELECT * FROM EMPLOYEE 336
9 DB2 WHERE DEPTNO IN ('D11', 'D21', 'E21') IN OR OR 9.4 SQL SQL SQL SQL WHERE 9.4.1 ( ) ( ) 337
DB2 9.4.2 ( ) ( ) (index only access) ALLOW REVERSE SCANS ( 11 SQL ) 1. WHERE WHERE ( ANY ALL SOME ) ( SELECT) INDEX IX1: NAME ASC, DEPT ASC, MGR DESC, SALARY DESC, YEARS ASC IX1 338
9 DB2 WHERE NAME = :hv1 AND DEPT = :hv2 WHERE MGR = :hv1 AND NAME = :hv2 AND DEPT = :hv3 WHERE ( ) ( ) ALLOW REVERSE SCANS CREATE INDEX iname ON tname (cname DESC) ALLOW REVERSE SCANS cname (iname) WHERE NAME DEPT SALARY YEARS WHERE NAME = :hv1 AND DEPT = :hv2 AND SALARY = :hv4 AND YEARS = :hv5 (MGR) (NAME DEPT) NAME = :hv1 DEPT = :hv2 2. (>) (<) NAME DEPT MGR 339
DB2 WHERE NAME = :hv1 AND DEPT AND DEPT AND MGR > :hv2 < :hv3 < :hv4 >= <= BETWEEN LIKE WHERE NAME = :hv1 AND DEPT >= :hv2 AND DEPT <= :hv3 AND MGR <= :hv4 :hv2 = 404 :hv3 = 406 :hv4 = 12345 404 405 (MGR ) 12345 406 3. ORDER BY DISTINCT GROUP BY = ANY > ALL < ALL INTERSECT EXCEPT UNION WHERE NAME = 'JONES' AND DEPT = 'D93' ORDER BY MGR NAME DEPT WHERE ORDER BY 340
9 DB2 WHERE NAME = 'JONES' AND DEPT = 'D93' ORDER BY NAME, DEPT, MGR ORDER BY UNIQUE INDEX IX0: PROJNO ASC SELECT PROJNO, PROJNAME, DEPTNO FROM PROJECT ORDER BY PROJNO, PROJNAME PROJNAME IX0 PROJNO PROJNO PROJNAME SQL 8 9.5 (outer table) (inner table) Table1 Table2 341
DB2 Table1 Table2 PROJ PROJ_ID PROJ_ID A 1 1 Sam B 2 3 Joe C 3 4 Mary D 4 1 Sue 2 Mike Table1 Table2 SQL SELECT PROJ, x.proj_id, NAME FROM TABLE1 x, TABLE2 y WHERE x.proj_id = y.proj_id PROJ PROJ_ID A 1 Sam A 1 Sue B 2 Mike C 3 Joe D 4 Mary ( INNER LEFT OUTER JOIN) 342
9 DB2 9.5.1 T1 T2 A T1 A T2 A 2 3 3 2 3 2 (1) T1 A 2 (2) T2 ( 2 ) (3) T2 ( 2 ) (4) T2 (5) T1 ( 3 ) (6) T2 ( 3 ) 3 1 343
DB2 344 (7) T2 ( 3 ) (8) T2 (9) T1 ( 3 ) (10) T2 ( 3 ) expr(outer_table.column) relop inner_table.column relop ( = > >= < <=) expr OUTER.C1 + OUTER.C2 <= INNER.C1 OUTER.C4 < INNER.C3 9.5.2 ( table1.column = table2.column ) LONG LOB T1 T2 A
9 DB2 T1 A T2 A 2 1 3 2 3 2 3 3 (1) T1 A 2 (2) T2 (3) T2 (4) T2 3 T1 (5) T1 3 T2 (6) T2 (7) T2 (8) T1 ( T1 T1 T2 3 T2 ) 9.5.3 (hash) table1.columnx = table2.columny CHAR DECIMAL DECFLOAT LONG LOB ( build table) ( sortheap ) Join INNER ( ) ( probe table) 345
DB2 (spill) SMP SMP ( intra-parallel = ON dft_degree >1 ) CPU CPU sortheap sheapthres sheapthres sortheap sheapthres sortheap OLAP SQL OLTP 9.5.4 DB2 DB2 DB2 0 1 2 3 5 7 9 346
9 DB2 1 5 DB2 SQL 9.6 SQL DB2 SQL SQL SQL DB2 7 0 1 DB2/6000 V1 V1 2 5 3 DB2 for MVS/ESA 5 Heuristic Rules( ) 7 Heuristic Rules 9 347
DB2 Heuristic Rules DB2 SQL ( OLTP) 0 1 ( OLTP) 1 OLTP ( OLTP ) 5 ( 1 ) ( ) 9 DB2 9.7 DB2 SQL 9.7.1 DB2 DB2 SQL (Transtrate Overhead) ( ) 348
9 DB2 RUNSTATS DB2 (Control Center) 10 9.7.2 DB2 SQL SELECT LASTNAME, SALARY FROM EMP WHERE EMPNO = '000010' AND DEPTNO = 'D01'; EMPNO Index1 DEPTNO Index2 EMPNO DEPTNO Index3 Index3 DB2 WHERE EMP DB2 INSERT DELETE UPDATE EMPNO DEPTNO SELECT LASTNAME, SALARY FROM EMP WHERE EMPNO = '000010' AND DEPTNO > 'D01'; 349
DB2 EMPNO DEPTNO DB2 (EMPNO) (DEPTNO) ( EMPNO DEPTNO) DB2 (index-only access) SQL DB2 SQL EMPNO DEPTNO LASTNAME SALARY EMPNO DEPTNO LASTNAME SALARY EMP I/O SQL 8 9.7.3 AVG_APPLS( ) DB2 SORTHEAP( ) ( ) LOCKLIST( ) DB2 DB2 (escalate) 350
9 DB2 MAXLOCKS( ) DB2 NUM_FREQVALUES( ) DB2 Runstats DB2 WHERE NUM_QUANTILES( ) DB2 Runstats DB2 DBHEAP( ) CPUSPEED (CPU ) CPU BUFFPAGE 6 9.7.4 SQL OLTP/OLAP 5 3 OLTP OLAP SELECT ( 1 ) 1 0 1 2 30 UNION ALL VIEW( FixPak4 ) 7 9 9.7.5 I/O Transrate Overhead Transrate=(1/ )*1000/1024000*4096( 4KB ) Overhead= +(((1/ )*60*1000)/2) I/O I/O 2 I/O 351
DB2 9.7.6 SQL 11 9.8 DB2 DB2 352