《数据库系统原理Database System Concepts》双语课件-ch9(Query Optimization).pptVIP

2007-6-16 APWeb/WAIM2007 Huang Shan 2007-6-16 APWeb/WAIM2007 Huang Shan 2007-6-16 Chapter 14: Query Optimization Review of Relational Algebra Introduction Transformation of Relational Expressions Heuristic Optimization Introduction Write the relational algebra expression and SQL statement to query respectively: Find the names of students who have selected ‘C002’ course from student-select-course database. Introduction (cont.) Which factors will determine query speed? CPU time Memory time I/O time Comparing I/O time of Q1, Q2 and Q3. It is assumed 1000 tuples in student table 10000 tuples in SC table 50 students have selected ‘C002’ Introduction (cont.) Query I: ?sname(?student.sno=SC.sno∧cno=‘C002 (student×SC)) Step1: do Cartesian product Step 2: select tuples Step 3: do project I/O Time of Doing Cartesian Product I/O Time of Doing Select I/O Time of Doing Project Introduction (cont.) Query II: Q2= ?sname(?cno=‘C002 (student SC)) Step1: do natural join Step 2: select tuples Step 3: do project Introduction (cont.) Q3= ?sname(student ?cno=‘c002(SC)) Step1: select tuples Step 2: do natural join Step 3: do project Introduction (cont.) If SC table has cno index, and student table sno index I/O time to do select operation is: (index block in memory) SC: 50 x 0.05 = 2.5s (50 tuples in 50 blocks) I/O time to compute natural join is: (index block in memory) student: 50 x 0.05 = 2.5s (read) (50 tuples in 5 blocks) Introduction (cont.) Basic Steps in Query Processing Basic Steps in Query Processing (cont.) A relational algebra expression may have many equivalent expressions E.g., ?balance?2500(?balance(account)) is equivalent to  ?balance(?balance?2500 (account)) Each relational algebra operation can be evaluated using one of several different algorithms Correspondingly, a relational-algebra expression can be evaluated in many ways. Basic Steps in Query Processing (cont.) Annotated expression specif