Reading a Query Plan · seed 1 · A4, ink-friendly. The answer key prints on its own page for grown-ups.

Reading the plan behind a slow query

Computing · Data & Databases · ages 19-21
Name ______________________   Date ____________

  1. A query needs 5 rows out of a million. Which access method fits?

    • An index scan to those rows
    • A full scan of the table
    • A full scan, then a sort of all rows

  2. What is a query plan?

    • The query's SQL text, tidied and reformatted
    • A log of how long each past query took
    • Its chosen methods, order and row estimates

  3. Most rows of the table qualify for the query. Which method usually wins?

    • Reading the server log
    • A full scan
    • An index on another table

  4. Two tiny tables join. Which method do you expect?

    • Hash join, because it always wins on joins
    • Sort merge, because tiny inputs are already sorted
    • Nested loop, because its setup stays tiny

  5. One plan step estimates 50 rows but processes 400,000. What do you conclude?

    • That step likely picked the wrong method
    • The query uses no joins at all
    • The row counts are close enough to ignore here

  6. The step that does the most work of its own is always the one that runs last.

    Circle one:   True   False

  7. Switching a big join from nested loop to hash can help once inputs grow.

    Circle one:   True   False

  8. A slow plan shows a nested loop over a step estimated at 20 rows with 200,000 real rows. What would you change?

    • Fix the wrong estimate or add the missing index
    • Add two more joins to the query
    • Delete the table statistics
LightMySky · lightmysky.comW1-mt_spbbzsCoBB-s1

Answer key

For grown-ups. Fold this page away before handing over the rest.

Reading the plan behind a slow query W1-mt_spbbzsCoBB-s1

  1. An index scan to those rows · Few needed rows means following the index beats reading everything.
  2. Its chosen methods, order and row estimates · The plan records the access method, join method, order, and estimates.
  3. A full scan · When nearly all rows qualify, reading the whole table in order wins.
  4. Nested loop, because its setup stays tiny · On small inputs the loop with almost no setup is cheapest.
  5. That step likely picked the wrong method · A bad estimate leads to a method sized for the wrong input.
  6. False · Cost follows work, not position in the plan, so read each step's own cost.
  7. True · Hash pays setup once and then scales better on big inputs.
  8. Fix the wrong estimate or add the missing index · The estimate gap picked the wrong method, so repair the estimate or the access path.
Worksheet · LightMySky