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

From: KENNY L. CHEN <kenny_60559_at_yahoo.com>
Date: 27 Jan 2004 23:44:23 -0800
Message-ID: <83f59352.0401272344.446e3e14@posting.google.com>

May be I didn't make the point clear.

TEST and TMP_TEST are tables with millions of records.

I did try to change the SQL to be like this:

SELECT * from
((SELECT COL1 , COL2 ,REC_NO FROM TEST)
 UNION ALL
 (SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST)  ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC ) WHERE (COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3 ) and ROWNUM < 2

However, the performance is not as good as the this one I used:

(SELECT COL1 , COL2 ,REC_NO FROM TEST
 UNION
 SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST )  WHERE (COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3 ) AND ROWNUM < 2
ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC The execution plan for two queries are virtually the same: Union all the two selection result from the 2 tables (INDEX RANGE SEARCH) first then sort the combined result. The performance will depend on the query result "WHERE (COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3 )" against the two tables.

After testing myself , it seems that is not a good idea to do it in one single
SQL command. I expect is there any experts can have a better idea.

Thank you Mr. Bakker anyway.

---

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0401260037.37fddf96_at_posting.google.com>...
> kenny_60559_at_yahoo.com (KENNY L. CHEN) wrote in message news:<83f59352.0401232325.61c22285_at_posting.google.com>...
> > Thank you for your response.
> >
> > What I expect is the get the top one record from the tables.
> > Originally I use
> > "UNION" to sort the selection result from the tables then pick up the
> > top one record. However, it causes performance problem when the tables
> > become huge.
> > That is why I try to use "UNION ALL" to avoid unnecessary sorting.
> >
> > According to advice from you , it seems "UNION ALL" doesn't work as
> > what I expected. I am looking forward if you or anyone can help me to
> > design a
> > statement to give me the correct result efficiently.
> >
>
> Flado's response is correct. Obviously you didn't even try it. Because
> you don't understand his answer?
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Wed Jan 28 2004 - 01:44:23 CST