CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 5 of 5 · 4 min read

Exercises

Easy

Easy 1: Name the three stages (15 minutes)

Name, in order, the three stages a SQL statement passes through before execution.

Done when: parsing, logical plan, and physical plan are correctly named in order.

Hint

Parsing, logical plan, physical plan.

Easy 2: Name what a logical plan commits to (15 minutes)

State whether a logical plan commits to a specific algorithm or index.

Done when: no is correctly stated, with the plan described as stating only what operations are needed.

Hint

No; a logical plan states only what operations are needed, not how to carry them out.

Easy 3: Name what EXPLAIN shows (15 minutes)

State which of the three stages EXPLAIN, covered in Level 5, actually displays.

Done when: the physical plan is correctly named.

Hint

The physical plan.

Medium

Medium 1: Distinguish logical from physical (25 minutes)

Explain the difference between "filter orders where customer_id = 42" and "index scan using idx_orders_customer."

Done when: the first is correctly identified as part of the logical plan, and the second as part of the physical plan, with the difference correctly tied to committing to a specific access method.

Hint

"Filter where customer_id = 42" is logical, stating only what is needed; "index scan using idx_orders_customer" is physical, naming a specific access method.

Medium 2: Explain parsing's validation role (25 minutes)

Explain what parsing would catch if a query referenced a column that does not exist on the table.

Done when: the explanation correctly states parsing checks that every referenced table and column exists, catching this error before later stages run.

Hint

Parsing checks that every referenced table and column exists, so a nonexistent column would be caught and reported at this stage.

Medium 3: Explain why one logical plan can produce different physical plans (30 minutes)

Explain why the same logical plan, "filter orders where customer_id = 42," might produce a different physical plan on a table with an index on customer_id than on an identical table with no such index.

Done when: the explanation correctly states the optimizer chooses the physical plan based on what is actually available and estimated cheapest, so the available index changes which physical plan gets chosen.

Hint

The optimizer's physical-plan choice depends on what access methods are actually available and estimated cheapest, so the presence or absence of an index changes which physical plan gets picked, even though the logical plan is identical.

Hard

Hard 1: Explain why the optimizer needs cost estimates (30 minutes)

Explain why the optimizer needs some notion of estimated cost to choose between a full scan and an index scan, rather than always preferring one or the other.

Done when: the explanation correctly notes that the right choice depends on the actual data and query, so a fixed rule would sometimes pick the slower plan; cost estimates, covered later in this level, let the optimizer compare specific alternatives for the specific query.

Hint

Which plan is actually faster depends on the specific data and query, such as how selective a filter is, so a fixed rule would sometimes choose a slower plan; cost estimates let the optimizer compare the specific alternatives for this specific case.

Hard 2: Trace a query through all three stages (35 minutes)

Trace SELECT * FROM orders WHERE customer_id = 42; through parsing, the logical plan, and the physical plan, describing what happens at each stage.

Done when: the trace correctly describes parsing validating the statement, the logical plan stating a filter operation is needed, and the optimizer choosing a specific scan method to produce the physical plan.

Hint

Parsing validates the statement's syntax and references; the logical plan states that a filter on customer_id = 42 is needed; the optimizer then chooses a specific scan method, producing the physical plan.

Hard 3: Explain why separating logical and physical plans is useful (40 minutes)

Explain why it is useful for a database to keep the logical plan, what is needed, separate from the physical plan, how it will be done, rather than deciding the execution method immediately while parsing.

Done when: the explanation correctly notes that separating the two lets the optimizer consider several different physical plans for the same logical need and pick the cheapest, which would not be possible if the execution method were fixed immediately during parsing.

Hint

Keeping the two separate lets the optimizer evaluate multiple possible physical plans for the same logical need and choose the cheapest one, a flexibility that would be lost if the execution method were locked in immediately during parsing.