What are the modes that an optimizer can create and describe query optimization? ANS: See the 'Rationale' field for sample responses.

REJ: Query optimization is the central activity during the parsing phase in query processing. In this phase, the DBMS must choose what indexes to use, how to perform join operations, which table to use first, and so on$.$ Each DBMS has its own algorithms for determining the most efficient way to access the data. The query optimizer can operate in one of two modes: A rule$-$based optimizer uses preset rules and points to determine the best approach to execute a query. The rules assign a "fixed cost" to each SQL operation; the costs are then added to yield the cost of the execution plan. For example, a full table scan has a set cost of $10,$ while a table accessed by row ID has a set cost of $3.$ A cost$-$based optimizer uses sophisticated algorithms based on statistics about the objects being accessed to determine the best approach to execute a query. In this case, the optimizer process adds up the processing cost, the I$/$O costs, and the resource costs $($RAM and temporary space$)$ to determine the total cost of a given execution plan.

LO: $11\mathrm{.}04$: Differentiate between a rule$-$based optimizer and a cost$-$based optimizer:

A$-$Head: Optimizer Choices

Bloom's: Remember

$\backslash (<mtext></mtext><mi>\backslash </mi><mi>)</mi>$ metadata

$73.$ Your management team wants to know why they need to optimize a DBMS with SQL performance tuning, even though they automatically optimize SQL queries.