CS348 Lecture Notes - Lecture 14: Database Tuning, Physical Data Model, Query Optimization

• School: University of Waterloo
• Department: Computer Science
• Course: CS348
• Professor: Grant Weddell

Document Summary

Increases execution time for updates of tuples from r: decreases execution time for deletions of tuples from r. Increases the amount of space required to represent r. Two relations are co-clustered if their tuples are interleaved within the same file: useful for storing hierarchical data (1:n relationships, speeds up joins (especially foreign-key, slows down sequential scans of either relation. It is possible to create an index on several attributes of the same relation. The order in which the attributes appear is the ordering priority for tuples or tuple pointers: multi-attribute indices are useful for related attributes and complex search/join conditions. Join indices allow replacing joins by index lookups: materialized views allow replacing subqueries by index lookups, determine the plan to use and the estimated cost with db2expln and dynexpln. The query optimizer must balance the cost of re-materialization and savings for queries.