nerdexam
Oracle

1Z0-060 · Question #169

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 B. A view is created during execution and a filter on the view limits the rows to 20 percent of the total. Oracle 12c's FETCH FIRST n PERCENT ROWS ONLY syntax is implemented internally by the optimizer creating an inline view over the base query and then applying a filter on that view to restrict the result to the specified percentage.

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

How the community answered

(63 responses)
  • A
    10% (6)
  • B
    83% (52)
  • C
    3% (2)
  • D
    5% (3)

Why each option

Oracle 12c's FETCH FIRST n PERCENT ROWS ONLY syntax is implemented internally by the optimizer creating an inline view over the base query and then applying a filter on that view to restrict the result to the specified percentage.

AA TOP-N query is created during execution and a filter is added to the query dynamically using

A direct dynamic ROWNUM predicate on the table query is not how percentage-based row limiting works; Oracle rewrites the statement using an inline view rather than attaching a ROWNUM filter to the base table scan.

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

When Oracle 12c processes a query with FETCH FIRST 20 PERCENT ROWS ONLY, the optimizer internally rewrites the statement by constructing an inline view that wraps the base query and then places a filter predicate on the view to return only the first 20 percent of rows, which is visible in the execution plan as a VIEW operation with a COUNT STOPKEY or similar filter.

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

Oracle does not fetch all rows to the client or middle tier and then discard extras; the row-limit filter is enforced inside the database engine during execution, preventing unnecessary row retrieval.

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

A dynamic ROWNUM filter added directly to the table query is how a simple TOP-N query works, not how a percentage-based FETCH FIRST PERCENT clause is processed; Oracle uses an inline view rewrite for the percentage variant.

Concept tested: Oracle 12c row-limiting clause internal optimizer rewrite mechanism

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

Topics

#row limiting clause#TOP-N query#FETCH FIRST#optimizer row filtering

Community Discussion

No community discussion yet for this question.

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