CS411-11-QueryOptimization - Note 1 - 2 - 3

# G join selection etc physical plan implementation

This preview shows page 1. Sign up to view the full content.

This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Optimization (40 of 64) Completing Physical Query Plan Completing Physical Query Plan (0 of 13) Query Optimization (41 of 64) The difference between index- based join and sort- merge join Completing the Physical Query Plan • Choose algorithm to implement each operator • Need to account for more than cost: R ⋈ S • Decide for each intermediate result: • To materialize • To pipeline index- based join sort- merge join Requireme nt Memory • How much memory do we have ? • Are the input operand(s) sorted ? index on one relation N/A 1 block of R, 1 block of S B(R)+T(R)B(S )/V(S, a) B(R)B(S)<M Cost Completing Physical Query Plan (1 of 13) Query Optimization (42 of 64) 2 5B(R)+5B(S) If memory is small, choose index- based join; if memory is large, choose sort- merge join. The cost of index- based join is possibly much larger than that of sort- merge join. • • Material means the intermediate result will be created whole and stored on disk. Pipelined means the intermediate result will be created only in main memory and not necessarily kept in their entirety at any one time. Steps: Materialize Intermediate Results Between Operators ⋈ V2 ⋈ V1 ⋈ R U T S Completing Physical Query Plan (2 of 13) 1. Generate Hashtable H(S) with the cost of B(S); HashTable ß། S repeat read(R, x) y ß། join(HashTable, x) write(V1, y) 2. Read every tuple of R and join with the H(S). Then write into the disk. The total cost is B(R ⋈ S). HashTable ß། T repeat read(V1, y) z ß། join(HashTable, y) write(V2, z) HashTable ß། U repeat read(V2, z) u ß། join(HashTable, z) write(Answer, u) Query...
View Full Document

## This note was uploaded on 01/28/2014 for the course CS 411 taught by Professor Staff during the Fall '08 term at University of Illinois, Urbana Champaign.

Ask a homework question - tutors are online