Re: "Union all" and "UNION" ???? Return the same result or not ??

From: KENNY L. CHEN <kenny_60559_at_yahoo.com>
Date: 23 Jan 2004 20:20:32 -0800
Message-ID: <83f59352.0401232020.24c7b34d_at_posting.google.com>

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 Sat Jan 24 2004 - 05:20:32 CET