What would be the most efficient and elegant way to select MAX value from transparent table?
For example, let's try to select such simple table as T100, Messages. Let us imagine we should select maximum message number within some application area.
We can do this in a multiple ways.
The most obvious is using MAX aggregate function
select MAX(msgnr)
from t100
where arbgb = '/ASU/GENERAL'
And the other is using UP TO 1 ROWS clause
select msgnr
from t100
where arbgb = '/ASU/GENERAL'
and ROWNUM = 1
order by msgnr DESC
Herein above, all SQL statements are given in a native Oracle SQL, as I was doing tests in DBACOCKPIT where this is mandatory.
Built-in cockpit tool showed almost identical results for both requests:
- estimated costs is 3 for both of them, however, second query has only 2 steps in plan whereas the first has 3.
- estimated CPU costs for the first query is 21.564 per step, however second has total cost of 21.964.
So we can conclude that second variant is more efficient here.
However, would this be a point in a common case? Would this result be different on different DBs or we can treat it as a rule of thumb?
