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 scan of the log file

  2. What is a query plan?

    • A copy of the table data
    • A list of user passwords
    • The chosen methods and order, with 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 memory is free
    • Sort merge, because disks spin fast
    • Nested loop, because its setup stays tiny

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

    • That step likely picked the wrong method
    • The query uses no joins at all
    • The database server is idle

  6. The costliest plan step 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. The chosen methods and order, with 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. Read the costs, not the order.
  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