Union clause casuing the first query to show up on the bottom [message #312020] |
Mon, 07 April 2008 18:45  |
bibsdash
Messages: 47 Registered: April 2008
|
Member |
|
|
HI
I have a sql script which i am creating a output file by spooling.
************************
spool c:\temp\abc.csv
select 'name','dept','description'
from dual
union
select name,dept,descrption
from users;
spool off
*********************
But the output from the first select before union clause is showing up on the bottom half of the spooled file instead of apearing on the top.
It should appear like this:
***************************************
name dept description
Hari Finance Finance Department
Ram Operations Operations Department
*************************************
What may be the problem?
Thanks in advance.
[Updated on: Mon, 07 April 2008 18:47] Report message to a moderator
|
|
|
|
|
Re: Union clause casuing the first query to show up on the bottom [message #312026 is a reply to message #312020] |
Mon, 07 April 2008 18:59   |
bibsdash
Messages: 47 Registered: April 2008
|
Member |
|
|
There is no union in your example. I will presume this is simply a TYPO.
Ans: There is a union
select 'name','dept','description'
from dual
union
select name,dept,descrption
from users
Instead of giving you then answer, let me ask you some questions:
1) how many queries are you executing?
Ans: 2
2) based on #1, how many rowsets are comming back?
Ans: 1
3) what made you think data from the first select should show up first?
Ans: That appears if we run it on sqlplus as a query than creating a sqlplus spool file
4) how do you get rows from a query to come out in a specific sequence?
ans: we use order by clause but it will be more intensive( ont he server side)
|
|
|
Re: Union clause casuing the first query to show up on the bottom [message #312030 is a reply to message #312020] |
Mon, 07 April 2008 19:21   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
OK nice set of replies, at least you tried. so here are my answers.
1) how many queries are you executing?
You said: 2. SORRY the correct answer is 1. You only have one query regardless of the number of selects in them. one query, one answer.
2) based on #1, how many rowsets are comming back?
You said: 1. yes, there is only one rowset being returned (which is because there was only one question asked (query is synonym for question)).
3) what made you think data from the first select should show up first?
You said: That appears if we run it on sqlplus as a query than creating a sqlplus spool file. A common newbie mistake, don't feed bad. This was only a coincidence. You made to bad assumptions: 1) that because the data was returned to you ordered, that oracle must have ordered it? 2) because Oracle did it this way once before, it would do it again the same way next time.
4) how do you get rows from a query to come out in a specific sequence?
you said, we use order by clause but it will be more intensive( ont he server side). Correct and maybe correct. The only way to get data in a specific order from a select in oracle is to use an order by clause. No order by clause; oracle can send the data back to you any way it wants. With an order by clause, Oracle must give the data to you the way you want it.
But... Oracle does not always have to sort data in order to ensure data is given to you in a specific order. There are several alternatives, including the fact that UNION in usually done with a sort by oracle (at least in the past) so if you had an order by clause on your query, oracle could have done the union using a sort that was compatible with your ordering and not needed to sort at the end thus providing rows to you in the order you wanted, without an extra sort. Oracle is getting pretty good at this these days. Read about analytics for another example of smarteness in the oracle optimizer.
This is a very common question by the way so don't feel bad about asking it. Your experience with the two different ordered results is most likely because two different query plans were used to run each query, or failing that, because the moon is in the second house of Jupiter.
Good luck, Kevin
|
|
|
Re: Union clause casuing the first query to show up on the bottom [message #312033 is a reply to message #312030] |
Mon, 07 April 2008 19:40   |
bibsdash
Messages: 47 Registered: April 2008
|
Member |
|
|
1) how many queries are you executing?
You said: 2. SORRY the correct answer is 1. You only have one query regardless of the number of selects in them. one query, one answer.
Ans: Logically there is one query to the Oracle server.But there are two individually queries which are joined by union clause.It is the way you see it.
2) based on #1, how many rowsets are comming back?
You said: 1. yes, there is only one rowset being returned (which is because there was only one question asked (query is synonym for question)).
Ans: That is correct.Spool file also shows one resultset but the first select result appears on the bottom of the resulset whereas it should appear on the top.
3) what made you think data from the first select should show up first?
You said: That appears if we run it on sqlplus as a query than creating a sqlplus spool file. A common newbie mistake, don't feed bad. This was only a coincidence. You made to bad assumptions: 1) that because the data was returned to you ordered, that oracle must have ordered it? 2) because Oracle did it this way once before, it would do it again the same way next time.
Ans: We always look at the way the query displays the data in sqlplus window.Hence we assume the same result will appear in the spool file.But the spool file is showing the resulset of the select before the union clause on the bottom half.
Maybe the way I asked the question was confusing.It must be clear now.
4) how do you get rows from a query to come out in a specific sequence?
you said, we use order by clause but it will be more intensive( ont he server side). Correct and maybe correct. The only way to get data in a specific order from a select in oracle is to use an order by clause. No order by clause; oracle can send the data back to you any way it wants. With an order by clause, Oracle must give the data to you the way you want it.
Ans: Spool file should have same resulset as the query in the sqlplus if the query is not changed.I donot think that i should add any order by or any otehr clause unless the query text is changed.
|
|
|
Re: Union clause casuing the first query to show up on the bottom [message #312036 is a reply to message #312020] |
Mon, 07 April 2008 20:05   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
OK well, don't take this badly as I do not mean any disrespect to you, but... you are wrong.
you are correct when you say "it is the way you see it". Perspective plays a big role in our business. For this situation however, you should do yourself a favor and start thinking about the whole as more important that its parts. One Query, one Answer.
it is a good thing that you test your code in sqllpus before you run it to a spool file. I wish other developers were that mindful of their work. But you are still making many false assumptions. You believe things that are not true. Let me give you some different rules you can rely on.
1) the only way to guarantee order of rows from a query is to use an ORDER BY clause (if you do not believe me, then I suggest you read the manual, for it says this in the manual and has since 1985).
2) queries without an ORDER BY clause can return rows in any order they like.
3) the order of rows from a query without an ORDER BY clause is affected by many things, most of which are out of your control. Hence you have no control of over the order of rows returned by a query unless you use an ORDER BY clause. The two most common factors affecting row ordering returned by a query are: 1) physical order of rows in the table, 2) the query plan used.
4) A) the physical ordering or rows on the table can change if someone deletes and reinsert rows, if someone REBUILDS the table (and several other reasons).
4) B) the query plan of a query can change if someone collects statitics in the database, if someone creates or drops an index on a table in your query (and a host of other reasons).
If you require a specific order of rows from your query, you must use an ORDER BY clause. Otherwise, you get what Oracle gives you because ordering does not matter.
Kevin
|
|
|
|
|
|
Re: Union clause casuing the first query to show up on the bottom [message #312065 is a reply to message #312047] |
Mon, 07 April 2008 21:50   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
UNION eliminates duplicates.
Eliminating duplicates may require a SORT.
Sorting places the entire result set in binary order.
The second SQL in your UNION returns a result that is (in a binary sort) before the result of the first SQL.
Use UNION ALL instead of UNION if you do not want to eliminate duplicates - or you can guarantee that there are no duplicates.
UNION ALL tends to preserve the sequence of SELECTs in the statement, but there are no guarantees (Parallel Query is a good example of an exception).
If you want guaranteed ordered results, use ORDER BY as suggested.
Ross Leishman
|
|
|
|
|