Re: "Union all" and "UNION" ???? Return the same result or not ??
Date: 27 Jan 2004 23:44:23 -0800
Message-ID: <83f59352.0401272344.446e3e14_at_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 - 08:44:23 CET