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  12819 
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.goo gle.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
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.goo gle.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 ke*********@yah oo.com (KENNY L. CHEN) wrote in message news:<83******* *************** ****@posting.go ogle.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.go ogle.com>... ke*********@yah oo.com (KENNY L. CHEN) wrote in message news:<83******* *************** ****@posting.go ogle.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.go ogle.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*********@yah oo.com (KENNY L. CHEN) wrote in message news:<83******* *************** ****@posting.go ogle.com>... fl***@imail.de (Vladimir Andreev) wrote in message news:<7b******* *************** ****@posting.go ogle.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_n o,t_name
from (
select t.*, rank() over (order by col1,col2,rec_n o) 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 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
dataset schema...but it's not!!
When loading the schema (ReadXmlSchema method) you get this error
message:
doesn't support 'union' or 'list' as simpleType
|
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 the
SimpleType enumeration EWethnicCategoryType.
EWethnicCategoryType is defined as a union with the CommonEthnicCategoryType
SimpleType enumeration.
Now when I use the XSD tool to create a C#...
|
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 tables:
Services:
Date datetime
Entry text
Amount number
(should be 4 records)
|
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
{
public float m_asFloat;
|
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 the Prologue
section of the .y file as:
| | |
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 char
variables. In other words, I want the following two statements to put the
same value into the same byte in the structure:
myStruct.Vars = 5;
myStruct.Var1 = 5;
|
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 change a bit by setting that boolean manually. Either
way, the same 16 bits is occupied in memory.
Something like...
typedef struct
{
bool bSetLed15;
|
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 union and select ..
the page doesn't get posted and it shows error page not found on this
server
i googled and found some people use union and select to hack sites
(mysql injection)
i guess the...
|
by: Jenniferdb2 |
last post by:
Hello All,
create table a (i int);
create table b(i int);
insert into a values(1);
insert into b values(2);
select * from a union all select * from b; ...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
