Better ways than rownum limits on a union all for fallback-style queries?

Question

I have three queries that generates some "reference" statistics and should be tried in order until one returns a result. These are currently coded as three separate calls to the DB, but I want to merge them as one.

I am considering a construct like this:

select * from (
 <query 1>
 union all
 <query 2>
 union all
 <query 3>
) where rownum = 1

The explain plan cost for this seems to be the sum of the three queries, but actually running the construct seems to only incur the cost of the query(/-ies) necessary to produce the row (as explained on AskTom).

Is this a good way to do this? Is there any better alternative?

Answer 1

Maybe like this:

select * from (
 Select ... from ... where rownum = 1
 union all
 Select ... from ... where rownum = 1
 union all
 Select ... from ... where rownum = 1
) where rownum = 1

If you want to retrieve only one row of each table there is no need to read any more and limit the rows as early as possible.