Question

1. Consider the relation r (Key, Name, Address). The relation takes 100 blocks on disk and holds 10000 tuples.

a. What is the selectivity of an equality predicate on Key?

b. Given a secondary Index_Name (height = 3) and a range predicate on Name with selectivity of 0.02, estimate the cost of doing the lookup on A.

c. Given a clustered Index_Name (height = 2) and a range predicate on Name with selectivity of 0.02, estimate the cost of doing the lookup on A.

d. Given a predicate P_A with selectivity of 0.3 and a predicate P_B with a selectivity of 0.5, what are the selectivities of:

(NOTE: you just need to compute the selectivity value, you do not need to estimate any costs in this part. You will have to make some assumptions about correlation between P_A and P_B)

1. NOT P_A
2. P_A AND P_B
3. P_A OR P_B
4. P_A AND (NOT P_B)

Answer 1

Query Processing & Optimization Task: Find an efficient physical query plan (aka execution plan) for an SQL query Goal: Minimize the evaluation time for the query, i.e., compute query result as fast as possible Cost Factors: Disk accesses, read/write operations, [I/O, page transfer] (CPU time is typically ignored) Dept. of Computer Science UC Davis 8. Query Processing and Optimization ECS-165A WQ’11 137 Basic Steps in Processing an SQL Query (System Catalogs) SQL Query Relational Algebra Expression Optimizer Statistics Query Result Evaluation Engine Execution Plan Data Files Parser & Translator • Parsing and Translating – Translate the query into its internal form (parse tree). This is then translated into an expression of the relational algebra. – Parser checks syntax, validates relations, attributes and access permissions • Evaluation – The query execution engine takes a physical query plan (aka execution plan), executes the plan, and returns the result. • Optimization: Find the “cheapest” execution plan for a query Dept. of Computer Science UC Davis 8. Query Processing and Optimization ECS-165A WQ’11 138 • A relational algebra expression may have many equivalent expressions, e.g., πCName(σPrice>5000((CUSTOMERS ✶ ORDERS) ✶ OFFERS)) πCName((CUSTOMERS ✶ ORDERS) ✶ (σPrice>5000(OFFERS)))