Mastering Basic SQL Performance: Optimizer, Indexes, Access Paths

1. SQL Performance Valerie Traurig January, 2004

2. Content __________________________________________ • Part 1: Basic SQL Performance • Part 2: Complex SQL Performance • Part 3: Advanced PerformanceTopics

3. Basic SQL Performance

4. __________________________________________ PART 1: Objectives Learn the main features of basic SQL performance for database tuning: • Nature of DB2 • Optimizer • Indexes • Predicates • Three Basic Access Paths • Table(space) Scans • Matching index scans • Non-matching index scans • EXPLAIN Usage • Invoking EXPLAIN • Interpreting output

5. Nature of DB2 __________________________________________ • Access to data determined by DB2 • Physical data independence • Tell DB2 what to retrieve, not how • Can access data with or without indexes

6. Introduction to the Optimizer _________________________________________ SQL Statement Available Indexes Statistics Environment DB2 Optimizer Decides the access path Cost-base optimizer

7. Columns: LASTNAME CHAR(30) NOT NULL FIRSTNAME CHAR(15) NOT NULL MI CHAR(1) ADDRESS VARCHAR(40) PHONENO CHAR(8) NOT NULL LASTNAME FIRSTNAME MI ADDRESS PHONENO Miller Sue A 469 Baltimore St. 313-8976 Davis John P 11 M St., NW 313-7777 Greuter Alvin L 2800 Pooks Rd. 313-1234 Frederick John S 28 Hill St. 314-5723 Alner Fred J 578 Mills Rd. 318-5192 Harrison Sophie G 4 Parkway St. 314-9551 Davis John P 83 Orleans Dr. 314-2922 Anderson Ben A 221 Beacon Ave. 314-7551 Kline Calvin B 9 Royal Hill Dr. 314-2738 Phone Book Example __________________________________________ PHONE BOOK TABLE DEFINITION TABLE DATA

8. What is an index? __________________________________________ Index Key LASTNAME FIRSTNAME MI Abrams Terry S Baker Bubba S Key values RIDs Davis Susan A Davis John R Davis John W An index is a separate physical file, a copy of certain data values. An index is an __________ set of key values with one or more pointers (RIDS) to the rows containing the key values in the table. Indexes are used to improve data access performance.

9. LASTNAME FIRSTNAME MI ADDRESS PHONENO Miller Sue A 469 Baltimore St. 313-8976 Davis John P 11 M St., NW 313-7777 Greuter Alvin L 2800 Pooks Rd. 313-1234 Frederick John S 28 Hill St. 314-5723 Alner Fred J 578 Mills Rd. 318-5192 Harrison Sophie G 4 Parkway St. 314-9551 Davis John P 83 Orleans Dr. 314-2922 Anderson Ben A 221 Beacon Ave. 314-7551 Kline Calvin B 9 Royal Hill Dr. 314-2738 Unique/Non-unique Indexes _________________________________________ ONE RID PER UNIQUE KEY VALUE MULTIPLE RIDS UNIQUE INDEX NON-UNIQUE INDEX LASTNAME, FIRSTNAME PHONENO 313-8976 DAVIS, JOHN

10. Unique Where Not Null Indexes _________________________________________ ONE RID PER NON NULL UNIQUE VALUE UNIQUE WHERE NOT NULL INDEX PAGERNO 3456 LASTNAME FIRSTNAME MI ADDRESS PHONENO PAGERNO Miller Sue A 469 Baltimore St. 313-8976 1234 Davis John P 11 M St., NW 313-7777 ---- Greuter Alvin L 2800 Pooks Rd. 313-1234 3456 Frederick John S 28 Hill St. 314-5723 ---- Alner Fred J 578 Mills Rd. 318-5192 ---- Harrison Sophie G 4 Parkway St. 314-9551 ---- Davis John P 83 Orleans Dr. 314-2922 8241 Anderson Ben A 221 Beacon Ave. 314-7551 ---- Kline Calvin B 9 Royal Hill Dr. 314-2738 ----

11. ___________________________________________ Miller, Del (4) Smith, Steve (5) Ziffel, Arnold (6) 2 Benson, Jack (1) Efram, Abigail (2) James, Jesse (3) 1 James, Jesse (1) Ziffel, Arnold (2) Jeffers, Mike (1,1) (10,1) Jones, Tommy (7,2) Klein, Calvin (4,2) Kleine, Ann (3,2) Lauren, Ralph (5,2) Miller, Del (2,4) 4 Frank, Frank (10,4) Frank, Mary (1,2) Goins, Bob (2,3) Howard, Ann (3,1) Ingram, Laura (9,3) James, Jesse (7,5) 3 Abrams, Shirley (9,1) Adams, John (2,1) (8,3) Alvin, Carla (10,3) Baker, Sue (7,1) Barber, Tony (4,3) (5,1) Benson, Jack 3,5) 1 Carlson, Sophia (10,2) Cook, Patti (3,3) Davidson, John (5,4) (9,4) Davis, John (9,2) Davis, Barbara (1,3) Efram, Abigail (2,2) 2 Miller, Russell (1,4) Mitchell, Ted (7,4) Peters, Reed (6,2) Quinn, Anthony (8,4) Randall, Tony (3,4) Smith, Steve (8,2) 5 Jeffers, Mike Frank, Mary Davis, Barbara Miller, Russell 1 Thomas, Marlo Klein, Calvin Barber, Tony Davis, John 4 Barber, Tony Lauren, Ralph Stevens, Connie Davidson, John 5 Index Structure Root Page Non-Leaf Pages Leaf Pages Smith, Terry (6,3) Stevens, Connie (5,3) Thomas, Marlo (4,1) (7,3) Ulrich, Robert (8,1) Valentino, Rudy (6,5) Ziffel, Arnold (6,4) 6 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Howard, Ann Kleine, Ann Cook, Patti Randall, Tony Benson, Jack 3 Adams, John Efram, Abigail Goins, Bob Miller, Delbert 2 Data Pages Abrams, Shirley Peters, Reed Smith, Terry Ziffel, Arnold Valentino, Rudy 6 Baker, Sue Jones, Tommy Thomas, Marlo Mitchell, Ted James, Jesse 7 Ulrich, Robert Smith, Steve Adams, John Quinn, Anthony 8 Abrams, Shirley Davis, John Ingram, Laura Davidson, John 9 Jeffers, Mike Carlson, Sophia Alvin, Carla Frank, Frank 10

12. 6 18 31 71 81 93 31 65 93 6 18 31 34 50 65 71 81 93 34 50 65 31 65 93 Clustered and Non-Clustered Index Access __________________________________________ Clustered Index Non-clustered Index Root Page Non-Leaf Pages Leaf Pages DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW DATA ROW

13. Predicate = One condition in a WHERE clause Predicates __________________________________________ WHERE A = B and C = D or E > F predicate predicate predicate

14. Predicate Order of Execution ________________________________________ • Order of the index controls the order of the application of the predicates • Predicate values outside of the index are applied in this order: • Equal predicates • Range predicates • IN, LIKE EXAMPLE: Assume index on ACCOUNT WHERE JOB = ‘CLERK’ AND DEPT > ‘C01’ AND SEX = ‘1’ AND ACCOUNT = ‘6871’ WHERE JOB = ‘CLERK’ AND DEPT > ‘C01’ AND SEX IN (‘1’,’2’) AND ACCOUNT = ‘5678’

15. 1 2 3 Basic Access Paths __________________________________________ • Table(space) scan • Sequential prefetch • Matching index scan • Non-matching index scan • Other variations • Index only access • Direct index lookup • Index screening • Index scan vs. sort • One fetch access • IN-LIST index scan

16. 1 2 LASTNAME FIRSTNAME MI ADDRESS PHONENO Miller Sue A 469 Baltimore St. 313-8976 Davis John P 11 M St., NW 313-7777 Greuter Alvin L 2800 Pooks Rd. 313-1234 Frederick John S 28 Hill St. 314-5723 Alner Fred J 578 Mills Rd. 318-5192 Harrison Sophie G 4 Parkway St. 314-9551 Davis John P 83 Orleans Dr. 314-2922 Anderson Ben A 221 Beacon Ave. 314-7551 Kline Calvin B 9 Royal Hill Dr. 314-2738 Table(space) Scan __________________________________________ SELECT * FROM PHONEBOOK WHERE ADDRESS LIKE ‘%AVE%’ SELECT * FROM PHONEBOOK PHONEBOOK Phonebook has an index on LASTNAME, FIRSTNAME

17. Segmented vs. Non-Segmented __________________________________________ Table scan (segmented) Tablespace scan (simple/partitioned) Limited Partition scan (partitioned)

18. Sequential Prefetch ________________________________________ • Read many (typically 32) pages at a time • I/O time per page roughly 1 ms per 4K page • I/O time overlapped with CPU time I/O CPU

19. Howard, Ann (1) Peters, Reed (2) Ziffel, Arnold (3) Abrams, Shirley (1,1) Adams, John (1,2) (1,3) Baker, Sue (1,4) Carlson, Sophia (2,1) Davis, John (2,2) Howard, Ann (2,3) 1 James, Jesse (3,1) Jones, Tommy (3,2) Lauren, Ralph (4,1) Miller, Del (4,2) Miller, Russell (5,1) Peters, Reed (5,2) 2 Quinn, Anthony (6,1) Randall, Tony (6,2) Smith, Steve (7,1) Smith, Terry (7,2) Stevens, Connie (7,3) Ziffel, Arnold (8,11) 3 Abrams, Shirley Adams, John Adams, John Baker, Sue 1 Carlson, Sophia Davis, Johnl Howard, Ann 2 James, Jesse Jones, Tommy 3 Lauren, Ralph Miller, Del 4 Quinn, Anthony Randall, Tony 6 Miller, Russell Peters, Reed 5 Smith, Steve Smith, Terry Stevens, Connie 7 Ziffel, Arnold 8 _________________________________________ Matching Index Scan SELECT LASTNAME, FIRSTNAME, PHONENO FROM PHONEBOOK WHERE LASTNAME LIKE ‘S%’ Root Page Leaf Pages _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Data Pages *Index on LASTNAME, FIRSTNAME

20. Howard, Ann (1) Peters, Reed (2) Ziffel, Arnold (3) Abrams, Shirley (7,2) Adams, John (2,1) (5,2) Baker, Sue (2,3) Carlson, Sophia (5,1) Davis, John (3,1) Howard, Ann (3,2) 1 James, Jesse (4,1) Jones, Tommy (1,1) Lauren, Ralph (4,2) Miller, Del (7,1) Miller, Russell (2,2) Peters, Reed (1,3) 2 Quinn, Anthony (6,2) Randall, Tony (1,2) Smith, Steve (8,2) Smith, Terry (8,1) Stevens, Connie (6,3) Ziffel, Arnold (6,1) 3 Jones, Tommy Randall, Tony Peters, Reed 1 Adams, John Miller, Russelll Baker, Sue 2 Davis, John Howard, Ann 3 James, Jesse Lauren, Ralph 4 Ziffel, Arnold Quinn, Anthony Stevens, Connie 6 Carlson, Sophia Adams, John 5 Miller, Del Abrams, Shirley 7 Smith, Terry Smith, Steve 8 _________________________________________ Matching Index Scan SELECT LASTNAME, FIRSTNAME, PHONENO FROM PHONEBOOK WHERE LASTNAME LIKE ‘S%’ Root Page Leaf Pages _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Data Pages *Index on LASTNAME, FIRSTNAME

21. Howard, Ann (1) Peters, Reed (2) Zwicky, Arnold (3) Jones, Tommy Randall, Tony Peters, Reed 1 Adams, John Miller, Russelll Baker, Sue 2 Davis, John Howard, Ann 3 James, Jesse Lauren, Ralph 4 Zwicky, Arnold Quinn, Anthony Stevens, Connie 6 Carlson, Sophia Adams, John 5 Miller, Del Abrams, Shirley 7 Smith, Terry Smith, Steve 8 _________________________________________ Matching Index Only Scan SELECT FIRSTNAME FROM PHONEBOOK WHERE LASTNAME LIKE ‘S%’ Root Page Abrams, Shirley (7,2) Adams, John (2,1) (5,2) Baker, Sue (2,3) Carlson, Sophia (5,1) Davis, John (3,1) Howard, Ann (3,2) 1 James, Jesse (4,1) Jones, Tommy (1,1) Lauren, Ralph (4,2) Miller, Del (7,1) Miller, Russell (2,2) Peters, Reed (1,3) 2 Quinn, Anthony (6,2) Randall, Tony (1,2) Smith, Steve (8,2) Smith, Terry (8,1) Stevens, Connie (6,3) Zwicky, Arnold (6,1) 3 Leaf Pages _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Data Pages *Index on LASTNAME, FIRSTNAME

22. Howard, Ann (1) Peters, Reed (2) Zwicky, Arnold (3) James, Jesse (4,1) Jones, Tommy (1,1) Lauren, Ralph (4,2) Miller, Del (7,1) Miller, Russell (2,2) Peters, Reed (1,3) 2 Quinn, Anthony (6,2) Randall, Tony (1,2) Smith, Steve (8,2) Smith, Terry (8,1) Stevens, Connie (6,3) Zwicky, Arnold (6,1) 3 Jones, Tommy Randall, Tony Peters, Reed 1 Adams, John Miller, Russelll Baker, Sue 2 Davis, John Howard, Ann 3 James, Jesse Lauren, Ralph 4 Zwicky, Arnold Quinn, Anthony Stevens, Connie 6 Carlson, Sophia Adams, John 5 Miller, Del Abrams, Shirley 7 Smith, Terry Smith, Steve 8 _________________________________________ Non-Matching Index Scan SELECT LASTNAME, FIRSTNAME, PHONENO FROM PHONEBOOK WHERE FIRSTNAME = ‘JESSE’ Root Page Abrams, Shirley (7,2) Adams, John (2,1) (5,2) Baker, Sue (2,3) Carlson, Sophia (5,1) Davis, John (3,1) Howard, Ann (3,2) 1 Leaf Pages _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Data Pages *Index on LASTNAME, FIRSTNAME

23. Howard, Ann (1) Peters, Reed (2) Zwicky, Arnold (3) James, Jesse (4,1) Jones, Tommy (1,1) Lauren, Ralph (4,2) Miller, Del (7,1) Miller, Russell (2,2) Peters, Reed (1,3) 2 Quinn, Anthony (6,2) Randall, Tony (1,2) Smith, Steve (8,2) Smith, Terry (8,1) Stevens, Connie (6,3) Zwicky, Arnold (6,1) 3 Jones, Tommy Randall, Tony Peters, Reed 1 Adams, John Miller, Russelll Baker, Sue 2 Davis, John Howard, Ann 3 James, Jesse Lauren, Ralph 4 Zwicky, Arnold Quinn, Anthony Stevens, Connie 6 Carlson, Sophia Adams, John 5 Miller, Del Abrams, Shirley 7 Smith, Terry Smith, Steve 8 _________________________________________ Non-Matching Index Only Scan SELECT LASTNAME, FIRSTNAME FROM PHONEBOOK WHERE FIRSTNAME = ‘JESSE’ Root Page Abrams, Shirley (7,2) Adams, John (2,1) (5,2) Baker, Sue (2,3) Carlson, Sophia (5,1) Davis, John (3,1) Howard, Ann (3,2) 1 Leaf Pages _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Data Pages *Index on LASTNAME, FIRSTNAME

24. 1 2 3 Which Access Path? _________________________________________

25. Howard, Ann (1) Peters, Reed (2) Ziffel, Arnold (3) Abrams, Shirley (7,2) Adams, John (2,1) (5,2) Baker, Sue (2,3) Carlson, Sophia (5,1) Davis, John (3,1) Howard, Ann (3,2) 1 James, Jesse (4,1) Jones, Tommy (1,1) Lauren, Ralph (4,2) Miller, Del (7,1) Miller, Russell (2,2) Peters, Reed (1,3) 2 Quinn, Anthony (6,2) Randall, Tony (1,2) Smith, Steve (8,2) Smith, Terry (8,1) Stevens, Connie (6,3) Ziffel, Arnold (6,1) 3 Jones, Tommy Randall, Tony Peters, Reed 1 Adams, John Miller, Russelll Baker, Sue 2 Davis, John Howard, Ann 3 James, Jesse Lauren, Ralph 4 Ziffel, Arnold Quinn, Anthony Stevens, Connie 6 Carlson, Sophia Adams, John 5 Miller, Del Abrams, Shirley 7 Smith, Terry Smith, Steve 8 _________________________________________ Direct Index Lookup SELECT LASTNAME, FIRSTNAME, PHONENO FROM PHONEBOOK WHERE LASTNAME = ‘Smith’ AND FIRSTNAME = ‘Terry’ Root Page Leaf Pages _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Data Pages *Index on LASTNAME, FIRSTNAME

26. 1 2 3 4 Access Path Exercise __________________________________________ LASTNAME FIRSTNAME MI RID Abner Joshua A … SELECT * FROM PHONEBOOK WHERE LASTNAME = ‘LEE’ Costner Kevin L … SELECT LASTNAME FROM PHONEBOOK WHERE FIRSTNAME = ‘DAVID’ Gallagher David J … Lee Ann P Lee Ben A SELECT * FROM PHONEBOOK WHERE LASTNAME = ‘LEE’ AND FIRSTNAME > ‘DEANN’ Lee David B J Lee Deborah B Lee Dennis Lee DeAnn P SELECT * FROM PHONEBOOK WHERE LASTNAME = ‘LEE’ AND MI > ‘J’ Lee Deanne L Lee Donald S J Lee Donna Lee Doug G … Miller Ben A … *Index on PHONEBOOK

27. phonebook INDEX Index Screening _______________________________________ Lastname, Firstname, MI SELECT * FROM PHONEBOOK WHERE LASTNAME = ‘LEE’ AND MI > ‘J’

28. SELECT * FROM PHONEBOOK WHERE FIRSTNAME = ‘BOB’ ORDER BY LASTNAME Index Scan versus Sort _______________________________________ INDEX Abner Avon DATA Carlson Baker Davis

29. SELECT MIN(FIRSTNAME) FROM PHONEBOOK WHERE LASTNAME = ‘LEE’ Lastname, firstname phonebook INDEX One Fetch Access _________________________________________

30. SELECT ADDRESS FROM PHONEBOOK WHERE LASTNAME = ‘LEE’ AND FIRSTNAME IN (‘DAVID’, ‘JOHN’) Lastname, firstname phonebook INDEX INDEX IN-List Index Scan _________________________________________ Performed as two queries with matching index scan: … FIRSTNAME = ‘DAVID’ … FIRSTNAME = ‘JOHN’

31. Which Access Path? ________________________________________ SELECT COUNT(*) FROM PHONEBOOK - Index on LASTNAME, FIRSTNAME

32. Most Efficient Access Paths ________________________________________ Three basic paths: 1) Matching Index Scan • Non-matching Index Scan • Table(space) Scan Hierarchical Variations • Index-only direct index lookup • Direct index lookup with data access • Index-only matching index scan • Matching clustered index access • Matching non-clustered index access • Non-matching clustered index access • Non-matching non-clustered index access • Partitioned tablespace scan (partition scan) • Segmented tablespace scan (table scan) • Entire partitioned or simple tablespace scan

33. Which Access Path? ________________________________________ EXPLAIN User DB2 Optimizer

34. How EXPLAIN Works ________________________________________ EXPLAIN SQL Statement BIND…EXPLAIN (YES) All SQL statements of plan/package One SQL statement EXPLAIN Access paths Cost estimates PLAN_TABLE DSN_STATEMNT_TABLE

35. _______________________________________ Setting Up EXPLAIN • CREATE TABLE userid.PLAN_TABLE …; CREATE TABLE userid.DSN_STATEMNT_TABLE …; • DELETE FROM PLAN_TABLE WHERE QUERYNO = xx; DELETE FROM DSN_STATEMNT_TABLE WHERE QUERYNO = xx; • EXPLAIN ALL SET QUERYNO = xx FOR SELECT …; • SELECT columns FROM PLAN_TABLE WHERE QUERYNO = xx; SELECT columns FROM DSN_STATEMNT_TABLE WHERE QUERYNO = xx;

36. SQL EXPLAIN _______________________________________ Setting Up EXPLAIN USER • Creates userid.PLAN_TABLE • Requests EXPLAIN • SELECT * FROM PLAN_TABLE OR BIND Inserts rows

37. EXPLAIN ALL SET QUERYNO = nn FOR SELECT LASTNAME, FIRSTNAME FROM PHONEBOOK WHERE ADDRESS LIKE ‘%AVE%’ SQL EXPLAIN Invocation ________________________________________

38. . . . EXEC SQL SELECT FIRSTNAME, LASTNAME, PHONENO INTO :FIRSTNAME, :LASTNAME, :PHONENO FROM EMP WHERE EMPNO = :EMPNO END-EXEC. . . . EXPLAIN – Table(space) Scan ______________________________________________________________ USERA.PLAN_TABLE QUERY NO QBLOCK NO PLAN NO ACCESS TYPE APPLNAME PROGNAME COLLID VERSION CREATOR TNAME PREFETCH PLANA DBRM1 90 1 1 USERA EMP R S BIND PLAN (PLANA) MEMBER (DBRM1) EXPLAIN (YES) USERA.EMP EMPNO FIRSTNAME LASTNAME PHONENO . . .

39. . . . EXEC SQL SELECT FIRSTNAME, LASTNAME, PHONENO INTO :FIRSTNAME, :LASTNAME, :PHONENO FROM EMP WHERE EMPNO = :EMPNO END-EXEC. . . . EXPLAIN – Matching Index Scan ______________________________________________________________ USERA.PLAN_TABLE QUERY NO QBLOCK NO PLAN NO INDEX ONLY ACCESS TYPE ACCESS CREATOR ACCESS NAME MATCH COLS APPLNAME PROGNAME COLLID VERSION CREATOR TNAME DBRM1 COLL1 90 1 1 USERA EMP N I USERA XEMP 1 BIND PACKAGE (COLL1) MEMBER (DBRM1) EXPLAIN (YES) CREATE INDEX XEMP ON EMP (EMPNO) USERA.EMP EMPNO FIRSTNAME LASTNAME PHONENO . . .

40. QUERY NO QBLOCK NO PLAN NO INDEX ONLY ACCESS TYPE ACCESS CREATOR ACCESS NAME MATCH COLS CREATOR TNAME 6 1 1 USERA EMP Y I USERA XEMP 0 SELECT COUNT (*) FROM EMP CREATE INDEX XEMP ON EMP (EMPNO) USERA.EMP EMPNO FIRSTNAME LASTNAME PHONENO . . . EXPLAIN – Non-Matching Index Scan ______________________________________________________________ USERA.PLAN_TABLE APPLNAME PROGNAME COLLID VERSION EXPLAIN ALL SET QUERYNO = 6 FOR SELECT COUNT (*) FROM EMP

41. M E T H P R E F SORTSORT NNNN CCCC UJOG UJOG COL FN EVAL MIX OP SEQ QUERY NO QBLOCK NO PLAN NO INDEX ONLY ACCESS TYPE ACCESS NAME MATCH COLS JOIN TYPE TNAME 11 1 1 1 PHONE R 0 NNNN NNNN 11 1 2 3 PHONE PHONE 0 NNNN NNYN SELECT LASTNAME, FIRSTNAME FROM PHONE WHERE ADDRESS LIKE ‘%AVE%’ ORDER BY LASTNAME, FIRSTNAME SELECT WORKDEPT, AVG(SALARY) FROM PHONE GROUP BY WORKDEPT M E T H P R E F SORTSORT NNNN CCCC UJOG UJOG COL FN EVAL MIX OP SEQ QUERY NO QBLOCK NO PLAN NO INDEX ONLY ACCESS TYPE ACCESS NAME MATCH COLS JOIN TYPE TNAME 12 1 1 1 PHONE R 0 NNNN NNNN 12 1 2 3 0 NNNN NNNY Other EXPLAIN Examples _______________________________________________________________ 0 Sorting: ORDER BY specified in query 0 Sorting: GROUP BY specified in query *index on LASTNAME, FIRSTNAME

42. M E T H M E T H M E T H P R E F P R E F P R E F SORTSORT NNNN CCCC UJOG UJOG SORTSORT NNNN CCCC UJOG UJOG SORTSORT NNNN CCCC UJOG UJOG COL FN EVAL COL FN EVAL COL FN EVAL MIX OP SEQ MIX OP SEQ MIX OP SEQ QUERY NO QUERY NO QUERY NO QBLOCK NO QBLOCK NO QBLOCK NO PLAN NO PLAN NO PLAN NO INDEX ONLY INDEX ONLY INDEX ONLY ACCESS TYPE ACCESS TYPE ACCESS TYPE ACCESS NAME ACCESS NAME ACCESS NAME MATCH COLS MATCH COLS MATCH COLS JOIN TYPE JOIN TYPE JOIN TYPE TNAME TNAME TNAME 5 5 0 0 PHONE PHONE I1 I1 XPHONE XPHONE NNNN NNNN NNNN NNNN 0 0 One Fetch Index Access One Fetch Index Access SELECT MIN(FIRSTNAME) FROM PHONE WHERE LASTNAME = ‘LEE’ SELECT MIN(FIRSTNAME) FROM PHONE WHERE LASTNAME = ‘LEE’ SELECT ADDRESS FROM PHONE WHERE LASTNAME = ‘LEE’ AND FIRSTNAME IN (‘DAVID’, ‘JOHN’) Other EXPLAIN Examples _______________________________________________________________ R 1 1 Y 0 6 1 1 0 PHONE N XPHONE 0 NNNN NNNN 0 Index access with IN-List *index on LASTNAME, FIRSTNAME

43. _______________________________________ PLAN_TABLE: Access Type Information Access Type ACCESSTYPE MATCHCOLS INDXONLY Table(space) scan R Matching index scan I >0 N Non-matching index scan I 0 N Matching index only I >0 Y Non-matching index only I 0 Y Fetch index scan I1 >0 IN list index access N >0

44. ACCESSTYPE = R or ACCESSTYPE = I with low MATCHCOLS Spotting “Bad” Access Paths _______________________________________________________________ Look for: Potential Causes: - No index - Have an index but not used

45. ________________________________________ unique index on C1 SELECT C1, C8, C9, C10, C15 FROM TA WHERE C1 BETWEEN ‘000020000’ AND ‘000020099” AND C8 IN (5, 6, 7) M E T H P R E F SORTSORT NNNN CCCC UJOG UJOG COL FN EVAL MIX OP SEQ QUERY NO QBLOCK NO PLAN NO INDEX ONLY ACCESS TYPE ACCESS NAME MATCH COLS TS LOCKS TNAME PLAN_TABLE Exercise 1 1 1 0 TA N I XC1 1 NNNN NNNN 0 IS QBLKNO : PLANNO : METHOD : TNAME : ACCESSTYPE : MATCHCOLS : ACCESSNAME : INDXONLY : SORTIND : TSLOCKS :

46. Access Path Checkpoint ________________________________________ • An index must always be in order. TRUE FALSE 2. A clustering index will always keep the table data in the same order as the index. TRUE FALSE 3 . What are the three main basic access paths? ______________________________________________________________ 4. The DB2 optimizer is a rule-based optimizer. TRUE FALSE 5. Suppose you have an index on two columns. It is used on a table, and the EXPLAIN shows INDEXONLY=N, ACCESSTYPE= I, and MATCHCOLS=2. This is: a) an index-only matching index scan b) a non-matching index scan c) a direct index lookup d) an IN-List index scan 6. What EXPLAIN ACCESSTYPE indicates a table(space) scan? _______________ 7. What EXPLAIN ACCESSTYPE indicates an IN-List index scan? _____________ 8. DB2 will only use an index on a predicate in a WHERE clause? TRUE FALSE

47. QUERY NO QBLOCK NO PLAN NO INDEX ONLY ACCESS TYPE ACCESS CREATOR ACCESS NAME MATCH COLS CREATOR TNAME 6 1 1 USERA EMP N I USERA XEMP 0 Access Path Checkpoint ________________________________________ 9. What access path does the following indicate? _____________________________ 10. You have an index on LASTNAME, FIRSTNAME, AND MI on the PHONEBOOK table. Given the following, if DB2 uses the index, how many MATCHCOLs will you see in the PLAN_TABLE? SELECT * FROM PHONEBOOK WHERE LASTNAME = ‘FREE’ AND FIRSTNAME > ‘DIANE’ AND MI = ‘A’ 11. The DSN_STATEMNT_TABLE gives you access path information. TRUE FALSE 12. Generally speaking, what is considered to be the best access? ____________________

48. M E T H M E T H P R E F P R E F SORTSORT NNNN CCCC UJOG UJOG SORTSORT NNNN CCCC UJOG UJOG COL FN EVAL COL FN EVAL MIX OP SEQ MIX OP SEQ QUERY NO QUERY NO QBLOCK NO QBLOCK NO PLAN NO PLAN NO INDEX ONLY INDEX ONLY ACCESS TYPE ACCESS TYPE ACCESS NAME ACCESS NAME MATCH COLS MATCH COLS JOIN TYPE JOIN TYPE TNAME TNAME R 5 2 0 0 PHONE PHONE I1 R XPHONE NNNN NNNN NNNN NNNN 0 0 PAGE RANGE 1 1 0 Y Access Path Checkpoint ________________________________________ 13. Does EXPLAIN provide index screening information in the PLAN_TABLE? _______ What is index screening? _______________________________________________ • What access path does the following EXPLAIN show? _______________________ • Is it possible for DB2 to use an index to avoid a sort? __________________ • The following indicates a partitioned table(space) scan. What two columns show this? ________________________________________________________________

49. Access Path Checkpoint ________________________________________ • Table T1 has an index X1 on COL1, COL2, and COL3. RUNSTATS has been run for the table. Given the following query, what access path would you expect? SELECT COL2 FROM T1 WHERE COL2 = ? Access path: ___________________________________________________

50. Access Path Checkpoint ________________________________________ • Table T1 has an index X1 on COL1, COL2, and COL3. RUNSTATS has been run for the table. Given the following query, what access path would you expect? SELECT COL2 FROM T1 WHERE COL2 = ? Access path: ___________________________________________________ However, EXPLAIN indicates that a tablespace scan was used. What might be a reason for this? 1. ________________________________________________________ 2. ________________________________________________________