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
10  12708 
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
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
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 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
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
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
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...... 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.
> ==========================
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 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: thomasfj |
last post by:
Hi,
Accroding to MS documentation the use of union and list elements in
XSD simpleType (valid according to W3C standards) is valid used as an...
|
by: Codex Twin |
last post by:
hello group:
The following is a fragment from a schema which defines the
EWethnicCategoryStructure type. As you can see, its type is defined by...
|
by: CrystalDBA |
last post by:
I usually design applications in SQL Server and Crystal Reports. I
now need to create a crystal report on an MS Access database.
I have two...
|
by: Yair |
last post by:
Hi,
I need to shift the bits of a float type.
In order to do so, I declared the following struct:
private struct unionIntFloatType
{
...
|
by: John Sasso |
last post by:
In my Yacc .y file I defined:
%union {
int value;
struct Symbol Sym;
}
The Symbol struct I defined in a header file I #included in...
|
by: Bob Altman |
last post by:
Hi all,
I'm struggling to do something really basic. How do I declare a structure
that has a union that maps an array of 3 chars with 3 distinct...
|
by: benn686 |
last post by:
Id like to group 16 booleans into a u16 such that I can either set all
16 variables at once with just a single u16 assigment, or I can
individually...
|
by: php_mysql_beginer911 |
last post by:
Hi .. hope someone will help
i am trying to figure it out why i cannot post string "union select"
every time i try to post data which content...
|
by: Jenniferdb2 |
last post by:
Hello All,
create table a (i int);
create table b(i int); ...
|
by: tammygombez |
last post by:
Hey fellow JavaFX developers,
I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
|
by: tammygombez |
last post by:
Hey everyone!
I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
|
by: better678 |
last post by:
Question:
Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct?
Answer:
Java is an object-oriented...
|
by: teenabhardwaj |
last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
|
by: CD Tom |
last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
|
by: Naresh1 |
last post by:
What is WebLogic Admin Training?
WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was...
|
by: Matthew3360 |
last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
|
by: AndyPSV |
last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
| |
