nerdexam
Oracle

1Z0-062 · Question #96

You find this query being used in your Oracle 12c database: Which method a used by the optimizer to limit the rows being returned?

The correct answer is D. A TOP-N query is created to limit the rows to 20 percent of the total rows. Oracle 12c's SQL row limiting clause (FETCH FIRST / OFFSET syntax) is implemented by the optimizer as a TOP-N query using an internal SORT ORDER BY STOPKEY operation that halts processing once the required row count or percentage is reached.

Managing Data and Concurrency

Question

You find this query being used in your Oracle 12c database:

Which method a used by the optimizer to limit the rows being returned?

Options

  • AA filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of
  • BAll the rows are returned to the client or middle tier but only the first 20 percent are returned to the
  • CA view is created during execution and a filter on the view limits the rows to 20 percent of the total
  • DA TOP-N query is created to limit the rows to 20 percent of the total rows

How the community answered

(45 responses)
  • A
    7% (3)
  • B
    13% (6)
  • C
    2% (1)
  • D
    78% (35)

Why each option

Oracle 12c's SQL row limiting clause (FETCH FIRST / OFFSET syntax) is implemented by the optimizer as a TOP-N query using an internal SORT ORDER BY STOPKEY operation that halts processing once the required row count or percentage is reached.

AA filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of

ROWNUM injection is the legacy pre-12c workaround for TOP-N queries and is not how the optimizer handles the native FETCH FIRST syntax - the optimizer uses an internal STOPKEY operation path rather than dynamically adding a ROWNUM predicate.

BAll the rows are returned to the client or middle tier but only the first 20 percent are returned to the

The optimizer never transmits the full result set to the client or middle tier only to discard rows - this would be catastrophically inefficient and completely defeats the purpose of the row limiting clause.

CA view is created during execution and a filter on the view limits the rows to 20 percent of the total

No intermediate view is materialized during execution for the row limiting clause - the optimizer applies the STOPKEY operation directly within the access path of the execution plan without creating any view structure.

DA TOP-N query is created to limit the rows to 20 percent of the total rowsCorrect

The Oracle 12c optimizer translates FETCH FIRST n PERCENT ROWS syntax into an internal TOP-N query plan, applying a COUNT STOPKEY or SORT ORDER BY STOPKEY operation to terminate row processing as soon as the percentage threshold is satisfied. This avoids sorting or scanning the full result set and is functionally equivalent to - but more efficient than - the older ROWNUM-based TOP-N pattern, while conforming to the SQL standard syntax.

Concept tested: Oracle 12c row limiting clause optimizer TOP-N execution

Source: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SELECT.html

Topics

#SQL optimizer#TOP-N query#row limiting clause#query processing

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-062 Practice