代价分析.ppt

* 4、散列连接算法代价分析 Say R2：B(R2)=500, 每个bucket中平均有 500/33 ? 16个 blocks memory G0(R1) G1(R1) ... 16 33-2=31 R2 M＝101 blocks For each t in R2 If h(t) =G0 or G1 then Join t with all R1’s tuples in G0 or G1 Else Write into R2’s 31 buckets * 4、散列连接算法代价分析 Next to Join 31 R1’s buckets with 31 R2’s buckets Just as before Memory Gi(R2) out ... 16 33-2=31 Output ... 31 33-2=31 R2 buckets R1 buckets one full R2 bucket one R1 buffer !Note: the 2 buckets in memory needn’t to be joined! * 4、散列连接算法代价分析 Called hybrid hash join Cost estimation For R1, say k buckets, m of which in memory 每个桶大小＝B(R1) / k 对于m个buckets中的每一块，节省2次IO，共节省 2(m/k)(B(R1)+B(R2)) 次IO Cost＝(3－2(m/k))(B(R1)+B(R2)) memory G0(R1) G1(R1) m k-m m =?, k=? ? * 4、散列连接算法代价分析 m = 1, k= B(R1) / M -- see textbook m =?, k=? ? Cost = (3 － 2M / B(R1)) (B(R1) + B(R2)) -- Suppose B(R1) ? B(R2) 内存中除了k－m个缓冲区外的所有块都可以用于存放常驻内存的元组，因此k要尽可能得小，这样可用于存放常驻元组的缓冲区就越大。对于给定R，如果一个桶的空间越大，则桶数k越小。桶的最大空间为M（因为桶必须将来能够容纳于内存中），因此得到 k= B(R1) / M，此时最多只能在内存中常驻1个桶，即m＝1 通常意义上的混合散列连接 都是常驻1个桶在内存 * 5、连接算法总结 算法 1 Cost M Nested Loop Join B(R2)+B(R1)B(R2)/M ? 2 Merge Join 5(B(R1)+B(R2)) Merge Join (improved) 3(B(R1)+B(R2)) Index Join B(R2)+T(R1)T(R2)/V(R1,C) LB(R1.C) 2 Hash Join 3(B(R1)+B(R2)) Hash Join (improved) (3 － 2M / B(R2)) (B(R1) + B(R2)) 1: suppose B(R2) ? B(R1) 2: suppose index fits in memory * 5、连接算法总结 Nested loop ok for “small” relations (relative to memory size) For equi-join, where relations not sorted and no indexes exist, hash join usually best Sort + merge join good for non-equi-join (e.g., R1.C R2.C) If relations already sorted, use merge join If index exists, it could be useful (depends on expected result size) * * The tuples of R spread in different blocks. The maximum number of blocks that R occupies may be T(R), in which case each tuple of R is located in respective block. * * Each block of each relation will be read exactly once. * * R2的每个元组在R1中有p个元组匹配，因此，每次循环都需要p次I/O读入R1的元组。总共T（R2）×p次I/O * * 选择先将小的关系读入，