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:
(Without fetch first)
Execution Plan
———————————————————-
Plan hash value: 2733869014
———————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 90499 | 31M| 2655 (1)| 00:00:01 |
| 1 | VIEW | DBA_OBJECTS | 90499 | 31M| 2655 (1)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 26 | 0 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 30 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
|* 9 | HASH JOIN | | 90813 | 12M| 390 (1)| 00:00:01 |
| 10 | TABLE ACCESS FULL | USER$ | 123 | 2214 | 4 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 90813 | 10M| 386 (1)| 00:00:01 |
| 12 | INDEX FULL SCAN | I_USER2 | 123 | 2952 | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | OBJ$ | 90813 | 9134K| 384 (1)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
| 15 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 |
|* 16 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 106 | 3 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | LINK$ | 1 | 88 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS CLUSTER | USER$ | 1 | 18 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
———————————————————————————————————–
(With fetch first)
Execution Plan
———————————————————-
Plan hash value: 684355358
————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————————-
| 0 | SELECT STATEMENT | | 90499 | 34M| 2655 (1)| 00:00:01 |
|* 1 | VIEW | | 90499 | 34M| 2655 (1)| 00:00:01 |
| 2 | WINDOW BUFFER | | 90499 | 31M| 2655 (1)| 00:00:01 |
| 3 | VIEW | DBA_OBJECTS | 90499 | 31M| 2655 (1)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 26 | 0 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 30 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
|* 10 | FILTER | | | | | |
|* 11 | HASH JOIN | | 90813 | 12M| 390 (1)| 00:00:01 |
| 12 | TABLE ACCESS FULL | USER$ | 123 | 2214 | 4 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 90813 | 10M| 386 (1)| 00:00:01 |
| 14 | INDEX FULL SCAN | I_USER2 | 123 | 2952 | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | OBJ$ | 90813 | 9134K| 384 (1)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
| 17 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 |
|* 18 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 106 | 3 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | LINK$ | 1 | 88 | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS CLUSTER | USER$ | 1 | 18 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
————————————————————————————————————-
C – TRUE – view is created
http://sqlplsql.wordpress.com/2013/07/23/limiting-the-percentage-of-ordered-rows-retrieved-in-oracle-12c/
C
C
As per “http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html”
‘Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set or, alternatively, the first N records after skipping over a set of records, so you can easily paginate through a result set. The diagram in Figure 1 shows the syntax for the row limiting clause.
The row limiting clause is simply added to the end of any SQL SELECT statement to fetch a specific set of records—there is no need for multiple layers of inline views and WHERE clauses that have to be carefully positioned, as there was with ROWNUM and ROW_NUMBER().’
I thinks correct answer is A.
As per “http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html”
This is a good document.. but please complete read this document:
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.
— SO the answer is C.
I don’t think this is very complicated. This is a simplification for creating a Top-N query. This is all that the student guide says on the subject too.
D
I agree.
Whatever it does under the cover – it clearly has option saying TOP-N Query.
Oracle database 12c: New Features for Administrators 24 – 9
Limiting the number or rows returned can be valuable for reporting, analysis, data browsing, and other tasks. Queries that order data and than limit row output are widely used and are often referred as Top-N queries
You can specify the number of rows or percentage of rows to return with FETCH FIRST/NEXT kywords. You can use the OFFSET keyword to specify that the returned rows begin with a row after the first row of the result set.
C is correct answer, but the question is about which METHOD used by the optimizer?
definitely is Top-N queries. D is the correct answer
C – tested and seen in the plan.
It says “Which method is used to limit the rows” and I don’t think “Top-N queries” is a method to limit rows.
The question is about “method a used by the optimizer” so C is better than D.
In earlier release its a known issue where oracle db does not have ‘select top n ‘ function like mssql.
The work around then was to filter via subquery like
SELECT * FROM (select * from suppliers ORDER BY supplier_name) suppliers2
WHERE rownum <= 3 ORDER BY rownum;
I think this question simply trying to show now oracle have this function.
I would choose D