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

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

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

A.
A filter is added to the table query dynamically using ROWNUM to limit the rows to 20

percent of the total rows

B.
All the rows are returned to the client or middle tier but only the first 20 percent are
returned to the screen or the application.

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

D.
A TOP-N query is created to limit the rows to 20 percent of the total rows

Show Hint

Leave a Reply 5

Your email address will not be published. Required fields are marked *

Bilzerian

Bilzerian

D seems to be the better answer here.

Reply

Santosh Kumar

Santosh Kumar

C seems to be correct answer.

Reference:
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html

Code Listing 2: Simple SELECT query with OFFSET FETCH

SQL> select owner, object_name, object_id
2 from t
3 order by owner, object_name
4 OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
…
—————————————————————————————————————————————————————————————————————————————
| Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time |
—————————————————————————————————————————————————————————————————————————————
| 0|SELECT STATEMENT | | 5| 1450 | 7 (0)|00:00:01|
|* 1| VIEW | | 5| 1450 | 7 (0)|00:00:01|<–view created
|* 2| WINDOW NOSORT STOPKEY | | 5| 180 | 7 (0)|00:00:01|
| 3| TABLE ACCESS BY INDEX ROWID|T |87310| 3069K| 7 (0)|00:00:01|
| 4| INDEX FULL SCAN |T_IDX| 5| | 3 (0)|00:00:01|
—————————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————

1 – filter("from$_subquery$_003"."rowlimit_$$_rownumber"=0)
THEN 5 ELSE 0 END +5 AND “from$_subquery$_003″.”rowlimit_$$_rownumber”>5)
2 – filter(ROW_NUMBER() OVER ( ORDER BY “OWNER”,”OBJECT_NAME”)=0) THEN 5 ELSE 0 END +5)

As you can see in Listing 2, the database, under the covers, is rewriting the query to use inline views and analytics once again—automating something that was previously nonintuitive and complex.

Reply