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
Explanation:
Answer should be D,fetch first new feature in 12c
should be C.
look here for execution plan:
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html
Answer should be D.
There is no complexity here regarding creation of view or anything else.
It will simply display 20% of the rows
Not D imo.
The question already IS a TOP-N query. No need to create a TOP-N query from that – it will just be exxactly the same.
C – see http://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php
The given answer, C is wrong. The closest answer to being correct is A. A “row limiting” query is re-written under the covers where a filter is added which uses the ROW_NUMBER() function and analytics to limit the rows returned. This IS explained in the link posted by Robert above. However, the answer still isn’t C because the re-write doesn’t create a VIEW. It creates an INLINE VIEW which is different. An INLINE VIEW is effectively a sub-query with an alias. The C option references a VIEW, not an INLINE VIEW so it is, by definition, incorrect. B is wrong because the returned row set is limited by the database, not the client or middle tier. D is wrong because this has nothing to do with a Top-N query.
H*T*H
SQL> select * from test fetch first 20 rows only;
Execution Plan
———————————————————-
Plan hash value: 4214006225
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 20 | 880 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 20 | 880 | 3 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 31 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST | 1 | 31 | 3 (0)| 00:00:01 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(“from$_subquery$_002”.”rowlimit_$$_rownumber”<=20)
2 – filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=20)
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
29 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
621 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
Excellent demo. Thanks
So answer is C. A view is created to filter 20% rows.
we came across a cool website that you just may well love. Take a look for those who want
please check out the web-sites we stick to, like this a single, as it represents our picks in the web
The data talked about in the article are some of the most beneficial readily available
we came across a cool internet site that you could possibly take pleasure in. Take a search when you want
Every the moment in a although we pick out blogs that we read. Listed below would be the latest web-sites that we pick out
one of our visitors not too long ago proposed the following website
Every the moment in a while we pick blogs that we study. Listed beneath are the most up-to-date web-sites that we decide on
that may be the finish of this write-up. Right here youll locate some web sites that we consider youll appreciate, just click the hyperlinks over
The information mentioned inside the article are a number of the top out there
just beneath, are numerous entirely not related web-sites to ours, having said that, they are certainly worth going over
always a large fan of linking to bloggers that I adore but do not get lots of link love from
just beneath, are several entirely not associated web sites to ours, having said that, they’re certainly really worth going over
the time to study or go to the content material or internet sites we have linked to below the
we prefer to honor many other web internet sites around the net, even though they arent linked to us, by linking to them. Beneath are some webpages really worth checking out
always a large fan of linking to bloggers that I like but do not get a great deal of link appreciate from
please visit the web-sites we follow, like this 1, because it represents our picks through the web
C