DB2性能优化.pptVIP

* Join Predicates Response time - determined mostly by the number of rows participating in the join Provide accurate join predicates Never use a JOIN without a predicate Join ON indexed columns Use Joins over subqueries * Join Predicates (cont.) When the results of a join must be sorted - limiting the ORDER BY to columns of a single table can avoid a sort specifying columns from multiple tables causes a sort Favor coding explicit INNER and LEFT OUT joins over RIGHT OUTER joins EXPLAIN converts RIGHT to LEFT join * Example: Outer Join With A Local Predicate SELECT emp.empno, emp.lastname, dept.deptname FROM emp LEFT OUTER JOIN dept ON emp.workdept = dept.deptno WHERE emp.salary 50000.00; Works correctly but… the outer join is performed first, before any rows are filtered out. * Example: Outer Join Using An Inline View SELECT emp.empno, emp.lastname, dept.deptname FROM (SELECT empno, lastname FROM emp WHERE salary 50000.00) as e LEFT OUTER JOIN dept ON emp.workdept = dept.deptno Works better… applies the inner join predicates first, reducing number of rows to be joined * OR vs. UNION OR requires Stage 2 processing Consider rewriting the query as the union of 2 SELECTs, making index access possible UNION ALL avoids the sort, but duplicates are included Monitor and EXPLAIN the query to decide which is best * Use BETWEEN BETWEEN is usually more efficient than = predicate and the = predicate Except when comparing a host variable to 2 columns Stage 2 : WHERE :hostvar BETWEEN col1 and col2 Stage 1: WHERE Col1 = :hostvar AND col2 = :hostvar * Use IN Instead of Like If you know that only a certain number of values exist and can be put in a list Use IN or BETWEEN IN (‘Value1’, ‘Value2’, ‘Value3’) BETWEEN :valuelow AND :valuehigh Rather than: LIKE ‘Value_’ * Use LIKE With Care Avoid the % or the _ at the beginning because it prevents DB2 from using a matching index and may cause a scan Use the % or the _ at the end to encourage index usage * Avoi