SQL Coding Best Practices for Developers

1. Platform: DB2 for Linux, UNIX, and Windows SQL Coding Best Practicesfor Developers • Phil Gunning • Principal Consultant, • Gunning Technology Solutions, LLC • Session: G2 • May 23, 2005 • 12:30 – 1:40

2. Outline • Best Practices • Classes of Predicates • Index SARGable • Range Delimiting • Data SARGable • Predicate Best Practices • Local, Order By, Join Predicates • Restricting Results • Restrict before joining • Selectivity • DB2 Catalog Queries/Explain

3. Outline • Index Design • Local, Order By, Join predicates • Include Columns • Uniqueness • DB2 Visual Explain/db2exfmt/Design Advisor • Monitor and Evaluate • Summary

4. Best Practices 1. Use Range Delimiting and Index SARGable Predicates wherever possible 2. Understand DB2 predicate rules 3. Specify most restrictive predicates first 4. Select only columns that are needed 5. Adhere to proper index design techniques 6. Understand inputs to the Optimizer 7. Developers and DBAs collaborate to design proper indexes 8. Evaluate all SQL using Visual Explain/db2exfmt 9. Use Design Advisor to tune SQL/SQL Workloads 10. Consistently monitor and review application performance

5. Application Relational Data Services Residual predicates COST Data Management Services Data SARGable predicates Range Delimiting Index SARGable Index Manager Data

6. Classes of Predicates • Range Delimiting • Index SARGable • Predicates that can use an index for a search argument • Resolved by Index Manager • Data SARGable

7. Predicate Example Index • For the following predicate rule examples, assume that an index has been created on Col A, Col B, and Col C Asc as follows: • ACCT_INDX: Col A Col B Col C

8. Predicates • Range Delimiting • Used to bracket an index scan • Uses start and stop predicates • Evaluated by the Index Manager

9. Range Delimiting Example

10. Predicates • Index SARGable • Are not used to bracket an index scan • Can be evaluated from the index if one is chosen • Evaluated by the Index Manager

11. Index SARGable Example

12. Predicates • Data SARGable • Cannot be evaluated by the Index Manager • Evaluated by Data Management Services • Require the access of individual rows from the base table

13. Data SARGable Example

14. Predicates • Residual Predicates • Cannot be evaluated by the Index Manager • Cannot be evaluated by Data Management Services • Require IO beyond accessing the base table • Predicates such as those using quantified sub-queries (ANY, ALL, SOME, or IN), LONG VARCHAR, or LOB data which is stored separately from the table • Are evaluated by Relational Data Services and are the most expensive type of predicates

15. Residual Predicate Example

16. RULE#1 • Use Range Delimiting and Index SARGable predicates whenever possible

17. Query Rewrite • The DB2 for Linux, UNIX and Windows optimizer contains significant query rewrite capability • Still important to write predicates following the local, order by, join rule • Query rewrite will take care of most transformations that need to be made • However, if predicates are missing or indexes are not available to support the access paths, your SQL will not be able to take advantage of query rewrite

18. Query Rewrite • The DB2 for Linux, UNIX and Windows optimizer contains significant query rewrite capability • Still important to write predicates following the local, order by, join rule • Query rewrite will take care of most transformations that need to be made • However, if predicates are missing or indexes are not available to support the access paths, your SQL will not be able to take advantage of query rewrite

19. Index Review • An index is a data structure that contains column values and a pointer to the table data • Primary key – Unique Index • If a primary key is defined, DB2 automatically creates a unique index to enforce the PK constraint • Secondary Index • Created to support access to frequently referenced columns • Indexes provide efficient access (in terms of CPU and IO) to columns found in the table • Just like an index entry in a book, an index in a database enables rapid lookup of associated table entries

20. Index Characteristics • Index entries are usually much smaller (subset) of all table columns • Can fit more index entries on a page • Allows for more efficient use of buffer pool • Separate index buffer pool • Enables often used index pages to remain in the buffer pool longer • More logical IO than physical IO

21. A Word About Index Structures • B+ -tree used to store index entries • Provides for a tree structure that is balanced to a constant depth from the root to the leaf blocks along every branch • Usually more efficient (less costly) than a table scan

24. Base Table

25. Select deptnumb, deptname from db2admin.org Where deptnumb < 20

26. Range Delimiting Example

28. Table Scan Example Created this index and ran this SQL CREATE INDEX "DB2ADMIN". "YYZZ" ON "DB2ADMIN"."ORG" ("DEPTNUMB" ASC, "DEPTNAME" ASC, "DIVISION" ASC) Select deptnumb, deptname from db2admin.org Where deptnumb =20 and deptname like 'b%' or division = 'midwest' and manager = 88 or location like 'bo%'

29. Table Scan Example

30. Table Scan Rules of Thumb • If > 20-25% of the rows will be read, good likelihood of table scan • If 0.5 – 20% of the rows are read, likely index access but this can vary depending on numerous factors • Exact formulas used are complex and not very useful for practical purposes

31. Rule #2 • Understand and apply DB2 predicate rules

32. WITH DEPT_MGR AS ( SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO FROM DEPARTMENT D, EMPLOYEE E WHERE D.MGRNO=E.EMPNO AND E.JOB='MANAGER' ), DEPT_NO_MGR AS ( SELECT DEPTNO, DEPTNAME, MGRNO AS EMPNO FROM DEPARTMENT EXCEPT ALL SELECT DEPTNO, DEPTNAME, EMPNO FROM DEPT_MGR ), MGR_NO_DEPT (DEPTNO, EMPNO, LASTNAME, FIRSTNME, PHONENO) AS ( SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, PHONENO FROM EMPLOYEE WHERE JOB='MANAGER' EXCEPT ALL SELECT DEPTNO,EMPNO, LASTNAME, FIRSTNME, PHONENO FROM DEPT_MGR ) SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO FROM DEPT_MGR UNION ALL SELECT DEPTNO, DEPTNAME, EMPNO, CAST(NULL AS VARCHAR(15)) AS LASTNAME, CAST(NULL AS VARCHAR(12)) AS FIRSTNME, CAST(NULL AS CHAR(4)) AS PHONENO FROM DEPT_NO_MGR UNION ALL SELECT DEPTNO, CAST(NULL AS VARCHAR(29)) AS DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO FROM MGR_NO_DEPT ORDER BY 4

33. A More “Complicated” Example

34. Table Scan Example

35. Created Two Indexes CREATE INDEX "DB2ADMIN"."AABB" ON "DB2ADMIN"."DEPARTMENT" ("DEPTNO" ASC, "DEPTNAME" ASC, "MGRNO" ASC) PCTFREE 10 CLUSTER MINPCTUSED 10 ALLOW REVERSE SCANS; CREATE INDEX "DB2ADMIN"."CCDD" ON "DB2ADMIN"."EMPLOYEE" ("EMPNO" ASC, "FIRSTNME" ASC, "MIDINIT" ASC, "LASTNAME" ASC, "WORKDEPT" ASC, "PHONENO" ASC) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS;

36. Index Scan Example Index Scan on AABB index

37. Full Index Scan Index Scan of entire index then fetch from table

38. Index on: DEPTNAME,DEPTNO MGRNO RIDS Base Table

39. Selectivity Catalog Queries • SELECT INDNAME, NPAGES, CARD, FIRSTKEYCARD AS FIRSTK, FIRST2KEYCARD AS F2KEY, FIRST3KEYCARD AS F3KEY, FIRST4KEYCARD AS F4KEY, FULLKEYCARD AS FULLKEY, NLEAF, NLEVELS AS NLEV, CLUSTERRATIO AS CR, CLUSTERFACTOR AS CF, UNIQUERULE AS U, T.COLCOUNT AS TBCOL, I.COLCOUNT AS IXCOL FROM SYSCAT.TABLES T, SYSCAT.INDEXES I WHERE T.TABSCHEMA = I.TABSCHEMA AND T.TABSCHEMA = ‘PGUNNING' AND T.TABNAME = I.TABNAME AND CARD >20000 ORDER BY CARD DESC, 1;

40. NLEVELS > 3

41. XBOOKING1 Selectivity = Number of Distinct Values / CARD 1229/389151 = .003 Meets our rule for selectivity < .10 XBOOKING2 Selectivity = Number of Distinct Values / CARD 111217/389151 = .285 Does not meet our rule for selectivity < .10

42. Data Specification • Specify the most restrictive predicates first • Select only those columns needed • Use business sense when developing reports for end users • They should not be so voluminous that the average end user will not be able to use them anyway • Haven’t we all seen these monster reports that consume lots of CPU and IO and never get looked at?

43. Restriction • Restrict before you join • Example: “Select * from acct, dept where acct.nbr = dept.acct_id and acct.loc = 5” • In this example, acct.loc = 5 is a restrictive expression • It will be applied before the join thus the number of rows joined decreases • With just the join expression, the number of rows increase as many rows in dept might match the ones in acct

44. Fast Retrieval • OPTIMIZE FOR N ROWS CLAUSE • Can guide the optimizer to use an access path to quickly return N Rows • Also effects the size of the number of rows blocked in the communications buffer • Useful when the number of rows you want is significantly less than total number of rows that could be returned • Can slow performance if most of the rows are going to be processed

45. Fetch First • FETCH FIRST N ROWS ONLY CLAUSE • Used to restrict fetching to only N rows regardless of number of rows that there may have been in the result set if not specified • FOR FETCH ONLY CLAUSE • Use when no updates are planned • Query can take advantage of row blocking • Only S locks taken on rows retrieved • Improved concurrency

46. Rule #3 & 4 • Specify most restrictive predicates first • Select only those columns needed

47. Selectivity • Selectivity of an index column indicates the number of rows that will satisfy the predicate condition • Formula: • Selectivity = number of distinct values / number of rows in the table • Selectivity of predicates should be < .10, that is will return less than 10% of the table rows to the requesting application or to the intermediate result set if more than a two-way join

48. Index Design • Indexes should be created on local, order by, join predicates and foreign keys • Primary key unique index created by default • Many times the primary key will be used by the optimizer as the driving index due to uniqueness and selectivity • Frequently accessed columns with good selectivity • Avoid Redundant Indexes as they are typically not used or offer additional unnecessary choices to the optimizer • Number of Indexes • Determined by business rules • OLTP • 3 indexes • Fewer indexes offer fewer choices to the optimizer • Mixed (ERP/CRM/SCM) • 3-5 indexes • DW • 5 or more

49. Rule#5 • Adhere to proper index design techniques

50. DB2 Optimizer • What inputs does the Optimizer consider/analyze during statement optimization? • Important to know as some of these inputs can cause suboptimal access paths if not current • RUNSTATS not current • Buffer pool changes • Configuration parameter changes