at some point, I'll have to say "buy the book" :)

<quote src=Effective Oracle by Design>
Top-N Query Processing with ROWNUM

In a top-N query, you are generally interested in taking some complex query, sorting it, and then retrieving just the first N rows (the top-N rows). ROWNUM has a top-N optimization (in Oracle9i Release 1 and up only) that facilitates this type of query. It can be used to avoid a massive sort of large sets. We’ll discuss how it does this conceptually, and then look at an example.
Suppose you have a query in this form:

Select … From …. Where …. Order by columns

Assume this query returns a lot of data: thousands, hundreds of thousands, or more rows. However, you are interested only in the top-N, say the top-10 or top-100. There are two ways to attack this:

· Have the client application open that query and fetch just the first N rows.

· Use that query as an inline view and use ROWNUM to limit the results, as in SELECT * FROM (your_query_here) WHERE ROWNUM <= N.

The second approach is by far superior to the first for two reasons. The lesser of the two reasons is that it requires less work by the client. The database will take care of limiting the result set. The most important reason is the special processing the database can do in order to give you just the top-N rows. Using the top-N query means you have given the database extra information. You have told it, “I’m only interested in getting N rows; I’ll never consider the rest.” Now, that doesn’t sound too earth-shattering until you think about sorting—how sorts work and what the server would need to do. Let’s walk through the two approaches with a sample query.

select * from t order by unindexed_column;

Now, assume T is a big table, with more than one million records, and each record is “fat,” say 100 or more bytes. Also assume that UNINDEXED_COLUMN is, as its name implies, a column that is not indexed. We are interested in getting just the first ten rows. Oracle would do the following:

1. Full-scan T.
2. Sort T by UNINDEXED_COLUMN. This is a full sort.
3. Presumably run out of sort area memory and need to swap temporary extents to disk.
4. Merge the temporary extents back to get the first ten records when they are requested.
5. Clean up (release) the temporary extents as we are finished with them.

Now, that is a lot of I/O. Oracle has most likely copied the entire table into TEMP and written it out, just to get the first ten rows.

Next, we’ll look at what Oracle can do conceptually with a top-N query:

select *
from (Select * from t order by unindexed_column)
where ROWNUM < :N;

In this case, Oracle will take these steps:

1. Full-scan T, as before (we cannot avoid this step).
2. In an array of :N elements (presumably in memory this time), sort only :N rows.

The first N rows will populate this array of rows in sorted order. When the N+1 row is fetched, it will be compared to the last row in the array. If it would go into slot N+1 in the array, it gets thrown out. Otherwise, it is added, sorted, to this array and one of the existing rows is discarded. Our sort area holds N rows maximum, so instead of sorting one million rows, we sort N rows.
This seeming small detail of using an array concept and just sorting N rows can lead to huge gains in performance and resource usage. It takes a lot less RAM to sort ten rows than it does to sort one million rows (not to mention TEMP space usage!).
</quote>

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#SQLRF00255 <code>

Yes:

<quote>
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:

SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;


In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.
</quote>

umm, there be no rownum in your query???

so, skip step 2 in my list and you havethe list of how it'll conceptually be processed.

that would be the wrong way though.

ANYTIME you can use rownum
use it, there are special top-n optimizations just for it

USE ROW_NUMBER() only if rownum does not suffice.


in the above, you would get the first 10 after sorting by employee_id - but the order might not be assured after that, it would probably be OK, but....