Why is ORDER BY performing better than MAX in Oracle

user_8329260 0
Viewed 47

I am working with an Oracle DB remotely and need to find the max value of a column for which I ran this query:

SELECT MAX(col1) FROM table1;

The above query always times out while the following one, surprisingly, works:

SELECT col1 FROM table1 WHERE ROWNUM < 2 ORDER BY col1 DESC;

I am unable to understand why this is happening, as both queries are semantically identical. What's more, the aggregate functions are supposed to be optimized for this kind of thing.

Any clarification on this would be appreciated.

aggregate-functions oracle sql
1 Answers

while the following one, surprisingly, works:

SELECT col1 FROM table1 WHERE ROWNUM < 2 ORDER BY col1 DESC;

I am unable to understand why this is happening, as both queries are semantically identical.

The queries are NOT semantically identical as the WHERE filter is applied before the ORDER BY clause is evaluated.

Therefore,

SELECT col1 FROM table1 WHERE ROWNUM < 2 ORDER BY col1 DESC;

Will read a single row from the data file for that table (effectively reading a random row) and then will order that single row; it does NOT return the maximum value (but instead a single random value).

Instead,

SELECT MAX(col1) FROM table1;

Will read all the rows and then return the maximum.

If you want a query that does the same as MAX but using ROWNUM then you need to ORDER BY first and filter on ROWNUM second:

SELECT col1 FROM (
  SELECT col1 FROM table1 ORDER BY col1 DESC
)
WHERE ROWNUM < 2;

Or, you can use the FETCH FIRST syntax introduced in Oracle 12:

SELECT col1 FROM table1 ORDER BY col1 DESC FETCH FIRST ROW ONLY;

db<>fiddle here

user_15092640
Related