Performance between SELECT MAX(col_name) and ROWNUM = 1

Question

Dear all,

I doubt on the speed and result of below queries. Could anyone give me the explanation on them? (These queries written for Oracle database)

Let say I have a table table1(ID, itemID, trnx_date, balance, ...). I want to get the latest balance of an item.

Query 1:

SELECT balance FROM table1 WHERE ID = (SELECT MAX(ID) from table1 WHERE itemID = *item_id*);

Query 2:

SELECT balance FROM table1 WHERE itemID = *item_id* AND rownum = 1 ORDER BY ID DESC;

where *item_id* is the variable.

Thus, do these two queries give the same result? And which one is faster or is there any other query that is faster than them?

Thanks

Answer 1

Rownum in Oracle is calculated before the sort (my mistake earlier, I haven't used Oracle in a bit). So, this query is identical to the first:

SELECT 
    balance
FROM 
    (SELECT balance FROM table1 WHERE itemID = *item_id* ORDER BY ID DESC)
WHERE
    rownum = 1;

In this case, given an index on ID, this may be slightly faster.

Why haven't you just run them yourself to benchmark them?