nerdexam
Oracle

1Z0-060 · Question #103

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 C. A view is created during execution and a filter on the view limits the rows to 20 percent of the. This question tests how Oracle 12c's optimizer internally rewrites a query that limits results to a percentage of total rows.

New Features for Performance

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
  • BAll the rows are returned to the client or middle tier but only the first 20 percent are returned to
  • CA view is created during execution and a filter on the view limits the rows to 20 percent of the
  • DA TOP-N query is created to limit the rows to 20 percent of the total rows

How the community answered

(32 responses)
  • A
    13% (4)
  • B
    6% (2)
  • C
    78% (25)
  • D
    3% (1)

Why each option

This question tests how Oracle 12c's optimizer internally rewrites a query that limits results to a percentage of total rows.

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

ROWNUM-based filtering is a pre-12c technique for TOP-N queries by fixed count and does not natively support percentage-based limiting without a subquery knowing the total row count.

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

Fetching all rows to the client and discarding the excess is not how Oracle's optimizer works - it performs server-side row limiting to avoid unnecessary data transfer.

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

When Oracle 12c processes a row-limiting clause using a PERCENT option (e.g., FETCH FIRST 20 PERCENT ROWS ONLY), the optimizer internally rewrites the query by creating an inline view during execution and then applies a filter on that view to restrict results to the specified percentage. This internal rewrite is transparent to the user but is visible in the execution plan, distinguishing it from traditional ROWNUM-based or TOP-N approaches.

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

A classic TOP-N query limits results by a fixed number, not a calculated percentage of total rows, and does not describe the internal view-based mechanism Oracle uses for percentage row limiting.

Concept tested: Oracle 12c row limiting clause internal execution mechanism

Source: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm

Topics

#row limiting#FETCH FIRST#optimizer row processing#TOP-N query

Community Discussion

No community discussion yet for this question.

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