[prev] 15 [next]

Exercise 2: Index Nested Loop Join Cost

Consider executing Join[i=j](S,T) with the following parameters:
  • rS = 1000,  bS = 50,  rT = 3000,  bT = 600
  • S.i is primary key, and T has index on T.j
  • T is sorted on T.j, each S tuple joins with 2 T tuples
  • DBMS has N = 12 buffers available for the join
Calculate the costs for evaluating the above join
  • using block nested loop join
  • using index nested loop join
Costr = # pages read   and   Costj = # join-condition checks