Thank you for your answer.
What I expect from the query result is return the top one record from
the
2 tables according to the ORDER BY clause. Originally we use "UNION"
but it causees performance problem when the tables get huge.
flado_at_imail.de (Vladimir Andreev) wrote in message news:<7b208869.0401230947.44caa3a_at_posting.google.com>...
> Either of the queries will return an arbitrary record. Therefore, you
> may get different results by running these queries at different times.
> If you tell us what is the required result set of these queries, we
> could help you design a statement that not only gives the correct
> results every time, but also does so in the most efficient way.
> The main problem with both queries is the combination of ORDER BY and
> ROWNUM<2. ROWNUMs are assigned after the "where" clause is evaluated,
> but *before* the "order by" clause sorts the output. Since your WHERE
> allows at most one row to go through, sorting doesn't make much sense.
> If you are trying to find the top record, you should place the
> subquery (select ... union [all] select ... order by ... ) in an
> inline view:
>
> select * from (
> select ... where ...
> union all
> select ... where ...
> order by ...)
> where rownum<2;
>
> HTH,
> Flado
Received on Fri Jan 23 2004 - 22:20:32 CST
