DB2 (join) SQL DB2 11 SQL DB2 SQL 9.1 DB2 DB2 ( ) SQL ( ) DB2 SQL DB2 DB2 SQL DB2 DB2 SQL DB2 ( DB2 ) DB2 DB2 DB2 SQL DB2 (1) SQL (2) S

Size: px

Start display at page:

Download "DB2 (join) SQL DB2 11 SQL DB2 SQL 9.1 DB2 DB2 ( ) SQL ( ) DB2 SQL DB2 DB2 SQL DB2 DB2 SQL DB2 ( DB2 ) DB2 DB2 DB2 SQL DB2 (1) SQL (2) S"

致纭於
5 years ago
Views:

1 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)

2 DB2 (join) SQL DB2 11 SQL DB2 SQL 9.1 DB2 DB2 ( ) SQL ( ) DB2 SQL DB2 DB2 SQL DB2 DB2 SQL DB2 ( DB2 ) DB2 DB2 DB2 SQL DB2 (1) SQL (2) SQL 326

3 9 DB2 (3) SQL (4) SQL SQL IBM (CBO) DB2 4 CPU I/O DB2 SQL 9-1 DB2 9-2 DB2 327

4 DB2 9.2 SQL SQL 9-3 SQL SQL SQL 7. Visual Explain db2exfmt db2expln 9-3 SQL 328

5 9 DB2 9-3 SQL 1. SQL 2. YEAR 3. (avg sum max min count ) 4. ( )

6 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

7 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 SQL ( ) SQL db2expln dynexpln db2exfmt ( SELECT ) SQL 331

8 DB2 SELECT SELECT SELECT (avg max sum count min) DISTINCT DISTINCT / SQL OR IN OR IN SQL IN OR EMPLOYEE $35,

9 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 > $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 > 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

10 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 > SQL SELECT EMPNO, FIRSTNME, LASTNAME, EDLEVEL, SALARY FROM EMPLOYEE WHERE EDLEVEL > 17 AND SALARY > 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

11 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; 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

12 DB 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

13 9 DB2 WHERE DEPTNO IN ('D11', 'D21', 'E21') IN OR OR 9.4 SQL SQL SQL SQL WHERE ( ) ( ) 337

14 DB ( ) ( ) (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

15 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

16 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 = (MGR ) 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

17 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 (outer table) (inner table) Table1 Table2 341

18 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

19 9 DB T1 T2 A T1 A T2 A (1) T1 A 2 (2) T2 ( 2 ) (3) T2 ( 2 ) (4) T2 (5) T1 ( 3 ) (6) T2 ( 3 )

20 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.C ( table1.column = table2.column ) LONG LOB T1 T2 A

21 9 DB2 T1 A T2 A (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 ) (hash) table1.columnx = table2.columny CHAR DECIMAL DECFLOAT LONG LOB ( build table) ( sortheap ) Join INNER ( ) ( probe table) 345

22 DB2 (spill) SMP SMP ( intra-parallel = ON dft_degree >1 ) CPU CPU sortheap sheapthres sheapthres sortheap sheapthres sortheap OLAP SQL OLTP DB2 DB2 DB

23 9 DB2 1 5 DB2 SQL 9.6 SQL DB2 SQL SQL SQL DB DB2/6000 V1 V DB2 for MVS/ESA 5 Heuristic Rules( ) 7 Heuristic Rules 9 347

24 DB2 Heuristic Rules DB2 SQL ( OLTP) 0 1 ( OLTP) 1 OLTP ( OLTP ) 5 ( 1 ) ( ) 9 DB2 9.7 DB2 SQL DB2 DB2 SQL (Transtrate Overhead) ( ) 348

25 9 DB2 RUNSTATS DB2 (Control Center) 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

26 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 AVG_APPLS( ) DB2 SORTHEAP( ) ( ) LOCKLIST( ) DB2 DB2 (escalate) 350

27 9 DB2 MAXLOCKS( ) DB2 NUM_FREQVALUES( ) DB2 Runstats DB2 WHERE NUM_QUANTILES( ) DB2 Runstats DB2 DBHEAP( ) CPUSPEED (CPU ) CPU BUFFPAGE SQL OLTP/OLAP 5 3 OLTP OLAP SELECT ( 1 ) UNION ALL VIEW( FixPak4 ) I/O Transrate Overhead Transrate=(1/ )*1000/ *4096( 4KB ) Overhead= +(((1/ )*60*1000)/2) I/O I/O 2 I/O 351

28 DB SQL DB2 DB2 352

Oracle9i 的查询优化

Oracle9i 的查询优化 Oracle9i Oracle 2002 2 Oracle9i...4...4...4 Oracle?...4 SQL...5...6...6...6...7...8...9...9 CUBE...10...11...11...11 OR...12...12...14...14...15...15...16...16...18...18...18...19...19...19...20...20 OLAP...20...21...21