Queries: Physical Aspects
Implementation of relational algebra
- File scan vs. index lookup vs. binary search
- Exact-match vs. range queries
- Impact of clustered vs. non-clustered indices
- Join methods:
- Tuple-oriented nested loops (dumb idea)
- Page-oriented nested loops (decent idea)
- Block-oriented nested loops (better idea, if buffers avail.)
Query optimization: cost estimates hard!!!
- Large space of physical, logical alternatives
- Prune space by considering only left-deep plans
- Enables pipelined execution