ITPEC FE Morning October 2023 Question 27

It chooses the most efficient way to execute a query — the optimizer picks an execution plan, not the result.

SQL says what data is wanted, not how to fetch it. The optimizer compares possible plans (which indexes to use, the order and method of joins, full scan or index scan), estimates their cost from table statistics, and hands the cheapest plan to the executor. Every plan returns the same rows.

Answer (a)

Why not others:

• •(b) — the number of result rows is fixed by the query's conditions; the optimizer must return exactly the same rows whatever plan it chooses
• •(c) — parsing is done by the parser, and the optimizer has nothing to do with network transmission
• •(d) — keeping results of frequent queries is a result cache, a separate mechanism

Key rule: The optimizer changes how a query runs, never what it returns.

Databases · AI-written, may contain errors