By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

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

P: n/a
Dear experts,

I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO)
and TEST1 (COL1,COL2,REC_NO).

Both tables are unique-indexed on (COL1,COL2,REC_NO).

I think the following SQL commands will return the same result but one
of my friends don't think so. He said "QUERY 1" will return 1 unsorted
record (ROWNUM < 2 ) first then sort the result (ORDER BY COL1 ASC,
COL2 ASC, REC_NO ASC).
In "QUERY 2" , "UNION" will sort first then return the first
distincted record.
But I can't duplicate the situation he said.

AM I WRONG ??? Please advise and thank you.

KENNY CHEN

============ QUERY 1 =============
( SELECT COL1 , COL2 ,REC_NO FROM TEST
UNION ALL
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
============ QUERY 2 =============
(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
Jul 19 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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
Jul 19 '05 #2

P: n/a
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.

===============
fl***@imail.de (Vladimir Andreev) wrote in message news:<7b*************************@posting.google.c om>...
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

Jul 19 '05 #3

P: n/a
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.

Thank you,

KENNY CHEN
=============
fl***@imail.de (Vladimir Andreev) wrote in message news:<7b*************************@posting.google.c om>...
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

Jul 19 '05 #4

P: n/a
ke*********@yahoo.com (KENNY L. CHEN) wrote in message news:<83**************************@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
Jul 19 '05 #5

P: n/a
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.
==================
sy******@yahoo.com wrote in message news:<a1**************************@posting.google. com>...
ke*********@yahoo.com (KENNY L. CHEN) wrote in message news:<83**************************@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

Jul 19 '05 #6

P: n/a
Sorry about the delay -- I was a bit busy...
Anyway,here I am now:
If you read my initial proposal carefully, you'll notice that the
where-clauses are *inside* the subqueries. That way, you'd first limit
the intermediate result sets, and then sort them. There comes your
performance gain:
SELECT * from
(SELECT COL1 , COL2 ,REC_NO FROM TEST
WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3
UNION ALL
SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST
WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3
ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC
)
where ROWNUM < 2

Now you should expect to see two PK index range scans, concatenated,
and sorted with a STOPKEY option in the execution plan.

Some notes on the results: If two identical rows exist in the tables
(one in each table), and they both pass the where-clause criteria, and
they have the minimum values in col1, col2, and rec_no, this query
will return only one of them. Which one, is undefined. You may not
care about this, since the records are identical, but you might want
to make this distinction, if in your *production* version you select
more than the key columns of these tables.
IOW, the question answered by this query is: I want at most one record
from either of the tables TEST and TMP_TEST. This record should have
the minimum values in col1, col2, and rec_no among all records that
pass my predicates. I don't care from which table that record comes.
As you notice, this is not exactly "the top record". It's only one of
the (potentially two, because of the unique constraints) top records.

If you decide that you actually want both such records, you need
another solution. Using rownum<3 will not do the trick.
Let me know if this is the case. Or better yet, read Tom Kyte's
"Effective Oracle by Design"

Regards,
Flado
Jul 19 '05 #7

P: n/a
fl***@imail.de (Vladimir Andreev) wrote in message news:<7b**************************@posting.google. com>...
Sorry about the delay -- I was a bit busy...
Anyway,here I am now:
If you read my initial proposal carefully, you'll notice that the
where-clauses are *inside* the subqueries. That way, you'd first limit
the intermediate result sets, and then sort them. There comes your
performance gain:
SELECT * from
(SELECT COL1 , COL2 ,REC_NO FROM TEST
WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3
UNION ALL
SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST
WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3
ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC
)
where ROWNUM < 2

Now you should expect to see two PK index range scans, concatenated,
and sorted with a STOPKEY option in the execution plan.

Some notes on the results: If two identical rows exist in the tables
(one in each table), and they both pass the where-clause criteria, and
they have the minimum values in col1, col2, and rec_no, this query
will return only one of them. Which one, is undefined. You may not
care about this, since the records are identical, but you might want
to make this distinction, if in your *production* version you select
more than the key columns of these tables.
IOW, the question answered by this query is: I want at most one record
from either of the tables TEST and TMP_TEST. This record should have
the minimum values in col1, col2, and rec_no among all records that
pass my predicates. I don't care from which table that record comes.
As you notice, this is not exactly "the top record". It's only one of
the (potentially two, because of the unique constraints) top records.

If you decide that you actually want both such records, you need
another solution. Using rownum<3 will not do the trick.
Let me know if this is the case. Or better yet, read Tom Kyte's
"Effective Oracle by Design"

Regards,
Flado


======================
Thank you Flado.

My problem is I need to know exactly "the top" record then perform
sequential update later. That is why the original design is like this:
(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

And it causes performance problem when the records is getting huge.
(In my case, millions of records in each table.)

I think if I can get "the top" record from one table and the other
separately,
then get the exactly "top one" record by comparing the 2 records.
However,
just like "UNION", sorting seems can't be avoided if I need to get the
"top one" from each table. Again, that generates performance problem
when the records is huge.

May be like what you said , read the book and see if can get some good
ideas......
Jul 19 '05 #8

P: n/a
ke*********@yahoo.com (KENNY L. CHEN) wrote in message news:<83**************************@posting.google. com>...
fl***@imail.de (Vladimir Andreev) wrote in message news:<7b**************************@posting.google. com>...
Sorry about the delay -- I was a bit busy...
Anyway,here I am now:
If you read my initial proposal carefully, you'll notice that the
where-clauses are *inside* the subqueries. That way, you'd first limit
the intermediate result sets, and then sort them. There comes your
performance gain:
SELECT * from
(SELECT COL1 , COL2 ,REC_NO FROM TEST
WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3
UNION ALL
SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST
WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3
ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC
)
where ROWNUM < 2

Now you should expect to see two PK index range scans, concatenated,
and sorted with a STOPKEY option in the execution plan.

Some notes on the results: If two identical rows exist in the tables
(one in each table), and they both pass the where-clause criteria, and
they have the minimum values in col1, col2, and rec_no, this query
will return only one of them. Which one, is undefined. You may not
care about this, since the records are identical, but you might want
to make this distinction, if in your *production* version you select
more than the key columns of these tables.
IOW, the question answered by this query is: I want at most one record
from either of the tables TEST and TMP_TEST. This record should have
the minimum values in col1, col2, and rec_no among all records that
pass my predicates. I don't care from which table that record comes.
As you notice, this is not exactly "the top record". It's only one of
the (potentially two, because of the unique constraints) top records.

If you decide that you actually want both such records, you need
another solution. Using rownum<3 will not do the trick.
Let me know if this is the case. Or better yet, read Tom Kyte's
"Effective Oracle by Design"

Regards,
Flado


======================
Thank you Flado.

My problem is I need to know exactly "the top" record then perform
sequential update later. That is why the original design is like this:
(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

And it causes performance problem when the records is getting huge.
(In my case, millions of records in each table.)

I think if I can get "the top" record from one table and the other
separately,
then get the exactly "top one" record by comparing the 2 records.
However,
just like "UNION", sorting seems can't be avoided if I need to get the
"top one" from each table. Again, that generates performance problem
when the records is huge.

May be like what you said , read the book and see if can get some good
ideas......


==========================
What if I create unique index on those table
create unique index idx_test_k1 on test (col1 ,col2, rec_no)
and create unique index idx_tmp_test_k1 on tmp_test (col1,col2,rec_no)
then use hint in my SQL command...

(SELECT /*+ INDEX(TEST idx_test_k1) */ COL1 , COL2 ,REC_NO FROM TEST
UNION ALL
SELECT /*+ INDEX(TEST idx_tmp_test_k1) */ 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

I tried several time seems work as I expected... But not sure it is exactly right.
Jul 19 '05 #9

P: n/a
>
==========================
What if I create unique index on those table
create unique index idx_test_k1 on test (col1 ,col2, rec_no)
and create unique index idx_tmp_test_k1 on tmp_test (col1,col2,rec_no)
I've assumed you already have unique indexes on these columns (you
said in your initial post that they were unique).

(SELECT /*+ INDEX(TEST idx_test_k1) */ COL1 , COL2 ,REC_NO FROM TEST
UNION ALL
SELECT /*+ INDEX(TEST idx_tmp_test_k1) */ 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

I tried several time seems work as I expected... But not sure it is exactly right.


It isn't. Your WHERE clause is applied only to the second select (from
tmp_test).
So, if I understand correctly, you want this result:
(col1, col2, rec_no, t_name), where t_name is either 'TEST' or
'TMP_TEST'.
If you are on 8i Enterprise or Personal Edition, or on 9i (any
edition), use analytics:

select col1,col2,rec_no,t_name
from (
select t.*, rank() over (order by col1,col2,rec_no) rn
from (
select test.*, 'TEST' t_name from test
union all
select tmp_test.*, 'TMP_TEST' t_name from tmp_test) t
where COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3)
where rn=1;

Otherwise (if you cannot use analytics), you'll need to pack and
unpack the three columns, and use MIN(). You don't really want to go
there, unless it is absolutely necessary.

HTH,
Flado
Jul 19 '05 #10

P: n/a
ke*********@yahoo.com (KENNY L. CHEN) wrote in message news:<83**************************@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
Jun 27 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.