DatabaseSystems（资料库系统）7.ppt

Database Systems(資料庫系統) December 5, 2005 Lecture #10 Announcement The submission deadlines for Assignment #4 Practicum #2 are extended to Wednesday. Assignment #5 will be out on the course homepage. It is due in two weeks. TA will explain assignment #5 at the end of the next lecture. Ubicomp Project of the Week Cellular Squirrel (MIT Media Lab) Found cell phones ringing during meeting disruptive? But you don’t want to lose the calls Animatronic device Overview of Query Evaluation Chapter 12 Outline Query evaluation (Overview) Relational Operator Evaluation Algorithms (Overview) Statistics and Catalogs Query Optimization (Overview) Example Tables Sailors(sid, sname, rating, age) Reserves(sid, bid, day, rname) Overview of Query Evaluation (1) Given a SQL query, we would like to find an efficient plan (minimal number of disk I/Os) to evaluate it. What are general steps of a SQL query evaluation? Step 1: a query is translated into a relational algebra tree σ(selection), π (projection), and ∞ (join) SELECT S.sname FROM Reserves R, Sailors S WHERE R.sid=S.sid ^ R.bid=100 ^ S.rating5 Overview of Query Evaluation (2) Step 2: Find a good evaluation plan (Query Optimization) Estimate costs for several alternative equivalent evaluation plans. Different order of evaluations gives different cost (e.g., push selection (bid=100) before join) How does it affect the cost (assume join is computed by cross-product + selection)? Overview of Query Evaluation (3) (continue step 2) An evaluation plan is consisted of choosing access method evaluation algorithm. Selecting an access method to retrieve records for each table on the tree (e.g., file scan, index search, etc.) Selecting an evaluation algorithm for each relational operator on the tree (e.g., index nested loop join, sort-merge join, etc.) Overview of Query Evaluation (4) Two main issues in query optimization: For a given query, what plans are considered? Consider a few plans (considering all is too many expensive), a