(数据库原理课件）Chapter11 Query Processing教材课程.ppt

(1) Nested-Loop Join To compute the theta join r ? sfor each tuple tr in r do begin for each tuple ts in s do begin test pair (tr,ts) to see if they satisfy the join condition ?  if they do, add tr ? ts to the result. endend r is called the outer relation and s the inner relation of the join. Requires no indices and can be used with any kind of join condition. Expensive since it examines every pair of tuples in the two relations. Nested-Loop Join (Cont.) In the worst case, if there is enough memory only to hold one block of each relation, the estimated cost is  nr ? bs + br block transfers, plus nr + br seeks If the smaller relation fits entirely in memory, use that as the inner relation. Reduces cost to br + bs block transfers and 2 seeks Nested-Loop Join (Cont.) ? Number of records of student: nstudent = 5, 000. ? Number of blocks of student: bstudent = 100. ? Number of records of takes: ntakes = 10, 000. ? Number of blocks of takes: btakes = 400. Assuming worst case memory availability cost estimate is with student as outer relation: 5000 ? 400 + 100 = 2,000,100 block transfers, 5000 + 100 = 5100 seeks with takes as the outer relation 10000 ? 100 + 400 = 1,000,400 block transfers and 10,400 seeks If smaller relation (student) fits entirely in memory, the cost estimate will be 500 block transfers. Block nested-loops algorithm (next slide) is preferable. (2) Block Nested-Loop Join Variant of nested-loop join in which every block of inner relation is paired with every block of outer relation. for each block Br of r do begin for each block Bs of s do begin for each tuple tr in Br do begin for each tuple ts in Bs do begin Check if (tr,ts) satisfy the join condition  if they do, add tr ? ts to the result. end end end end Block Nested-Loop Join (Cont.) Worst case estimate: br ? bs + br block transfers + 2 * br seeks Each block in the inner relation s is read once