How to effectively limit a selection size in a generic manner in Oracle? Let's say, we have the a query to an Oracle database:

SELECT *
FROM FOO f
WHERE f.bar &gt; baz
ORDER BY f.qux;


And we are looking for a way to limit the selection size in a generic manner.

The approach of wrapping a query in the following way may be applied:

SELECT *
FROM (
  SELECT *
  FROM FOO f
  WHERE f.bar &gt; baz
  ORDER BY f.qux
)
WHERE ROWNUM &lt;= 1;


Though, it is easy to employ, it may be shortened to the following one with a small price of the input query analysis (to place AND ROWNUM &lt;= 1 at the right position):

SELECT *
FROM FOO f
WHERE f.bar &gt; baz
    AND ROWNUM &lt;= 1
ORDER BY f.qux;


Would the later query practically perform better? Would it affect caching &amp; optimizing in any significant way?
SELECT *
FROM (
  SELECT *
  FROM FOO f
  WHERE f.bar &gt; baz
  ORDER BY f.qux
)
WHERE ROWNUM &lt;= 1;


Means: order the resultset by f.qux then return the 1st row based on that order.

SELECT *
FROM FOO f
WHERE f.bar &gt; baz
    AND ROWNUM &lt;= 1
ORDER BY f.qux;


Means: get the 1st row by unspecified order, then order the result. Order by has no effect at all in this specific example.

They are different.

Use the first one, or starting with 12c, you can use the below:

SELECT *
FROM FOO f
WHERE f.bar &gt; baz
ORDER BY f.qux
FETCH FIRST 1 ROW ONLY; -- FETCH FIRST 1 ROWS ONLY, FETCH FIRST n ROWS ONLY also works