OracleSource

• The idea behind the Top-N SQL feature is that you do not need to sort a full set if, for example, you are only interested in the four highest (or lowest) values.
• If a set is too big to be sorted in memory, the performance is significantly and especially degrading. This is caused by the I/O to and from temporary storage of intermediate results on disk.
• If you only want to know the four highest values, you only need an array with four slots to scan the set and keep the four highest values in that array.
• The WHERE clause of the statement above is merged into the in-line view to prevent the full EMPLOYEES table from being sorted by SALARY. This appears in the execution plan as follows:

SQL> select *
2 from (select emp_id
3 , last_name
4 , first_name
5 , salary
6 from employees
7 ORDER BY salary desc)
8 where ROWNUM <= 5;

Execution Plan
------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE
COUNT (STOPKEY)
VIEW
SORT (ORDER BY STOPKEY)
TABLE ACCESS (FULL) OF 'EMPLOYEES'