Home » SQL & PL/SQL » SQL & PL/SQL » Union clause casuing the first query to show up on the bottom (Oracle 8i)
Union clause casuing the first query to show up on the bottom [message #312020] Mon, 07 April 2008 18:45 Go to next message
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 #312021 is a reply to message #312020] Mon, 07 April 2008 18:50 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
PL/SQL Experts
Advanced Oracle PL/SQL questions - stored procedures, functions, packages and triggers. Newbies should not post to this forum!

Why can't you or won't you follow clear instructions?
Re: Union clause casuing the first query to show up on the bottom [message #312022 is a reply to message #312020] Mon, 07 April 2008 18:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There is no union in your example. I will presume this is simply a TYPO.

Instead of giving you then answer, let me ask you some questions:

1) how many queries are you executing?
2) based on #1, how many rowsets are comming back?
3) what made you think data from the first select should show up first?
4) how do you get rows from a query to come out in a specific sequence?

Kevin
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 #312039 is a reply to message #312036] Mon, 07 April 2008 20:08 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Kevin
I donot mind but I liked your approach.
You sound like professional.

I want to avoid order by.
My query will have a lot of rows once they are in production.
I want to avoid that.

Thanks
Re: Union clause casuing the first query to show up on the bottom [message #312045 is a reply to message #312020] Mon, 07 April 2008 20:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have many times wanted to avoid order by too. Problem is, either you need to guarantee the order of rows or you don't. If you do, must use order by, if not, then you don't. Simple as that.

No problem my friend. It is good to discourse with someone who actually takes the time to test their code first.

Kevin
Re: Union clause casuing the first query to show up on the bottom [message #312047 is a reply to message #312039] Mon, 07 April 2008 20:16 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
It is always better to be practical than logical.
That is why I try doing myself than ask someone.

I thought if i can avoid order by clause.
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Union clause casuing the first query to show up on the bottom [message #312073 is a reply to message #312020] Mon, 07 April 2008 22:29 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
rleishman
I also tried UNION ALL it didnot work for me.

I want to avoid Order By for performance issues.

Anyway thanks for all your help.
Re: Union clause casuing the first query to show up on the bottom [message #312286 is a reply to message #312073] Tue, 08 April 2008 07:35 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
spool c:\temp\abc.csv
prompt "name,dept,description"
select name,dept,descrption
from users;
spool off


Ross Leishman
Previous Topic: Converting long procedure with repeated code into package
Next Topic: In need of code ideas (merged)
Goto Forum:
  


Current Time: Tue Dec 06 08:51:07 CST 2016

Total time taken to generate the page: 0.09307 seconds