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

2007-6-16 APWeb/WAIM2007 Huang Shan 2007-6-16 APWeb/WAIM2007 Huang Shan 2007-6-16 Chapter 7 Query Processing Overview Measures of Query Cost Selection Operation Sorting Join Operation Other Operations Evaluation of Expressions Query Processing How does the query processor execute queries? Query ? Query Plan - SQL - expression of methods to realize the query Example SELECT B, D FROM R, S WHERE R.C=S.C AND  R.A = c AND  S.E = 2 How to execute the query? - Do Cartesian product RxS - Select tuples - Do projection Problem? A Cartesian product RxS may be LARGE need to create and examine n x m tuples,where n = |R| and m = |S| For example, n = m = 1000 = 106 records - need more efficient evaluation methods! Relational Algebra - used to describe logical plans Ex: Original logical query plan ?B,D sR.A=“c”? S.E=2 ? R.C=S.C x R S Improved logical query plan ?B,D sR.A = “c” sS.E = 2 R S Physical Query Plan (1) Use R.A index to select tuples of R with R.A = “c” (2) For each R.C value found, use the index on S.C to find matching tuples Algorithms to implement relational operations We concentrate on algorithms Selections Sorts Joins Measures of Query Cost Cost is generally measured as total elapsed time for answering query Many factors contribute to time cost Disk access main cost in most DB CPU trivial compared to disk time Network Communication Main concern in distributed DBs Measures of Query Cost Typically disk access is the predominant cost, and is also relatively easy to estimate. Measured by taking into account Number of seeks // average-seek-cost Number of blocks read //average-block-read-cost Number of blocks written //average-block-write-cost Cost to write a block is greater than cost to read a block Measures of Query Cost (Cont.) For simplicity we just use the number of block transfers from disk and the nu