Home » RDBMS Server » Performance Tuning » Urgent help needed in tuning the query
Urgent help needed in tuning the query [message #267594] Thu, 13 September 2007 16:41 Go to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi,

I have a query which has set of select queries with union all and i am trying to use this query to insert into one table with order by one of the column in all the select statements.

Here is the example

insert into tall
select
c1,
c2,c3
from t1
union all
select
c4,c5,c6
from t2
union all
select
c7,c8,c9
from t3
order by 1

I have 500,000 records which has to be inserted.This insert is almost taking an hour to do that.Can some one explain me what could be the reason for this ?

even if i try to run the query with order by clause it is taking lot of time but if i comment the order by clause it is executing in seconds.it means order by clause is casuing the issue but how can i overcome that because i cant remove the order by clause in order to generate the sequence according to date.

Please help me out in how can i improve the performance of the query .

Thanks
R



Re: Urgent help needed in tuning the query [message #267595 is a reply to message #267594] Thu, 13 September 2007 16:48 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
Since you decided to not read or not follow the posting guidelines as stated in the STICK post at top of this forum,
we can decide to not provide any answers until you comply.

Please explain why it is URGENT for ME to solve this problem for you.
Re: Urgent help needed in tuning the query [message #267601 is a reply to message #267595] Thu, 13 September 2007 17:59 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi,

I apologize for the wordings.Will see that i wont happen again.

can you help me out now atleast?

Thanks
R
Re: Urgent help needed in tuning the query [message #267621 is a reply to message #267601] Thu, 13 September 2007 22:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why is necessary to sort the rows before they are inserted? Tables are just unordered heaps; even if you do insert them in a special order, it cannot deterministically affect any future queries.

Anyway, let's say you do have a magical reason for sorting before the insert. How long does the SELECT take without the insert. You said it takes a long time - how long is that? 20 minutes? If so, tuning the SELECT will still leave 40 minutes of inserting.

ie. Where is all the time spent? SELECTing or INSERTing?

The reason it returns quickly without the ORDER BY is because it is returning the first matching rows of the first part of the UNION ALL. At the time it returns the first row, it hasn't found the entire result-set yet. When you add an ORDER BY, it needs to retrieve the entire result set before the first row can be returned.

Ross Leishman
Re: Urgent help needed in tuning the query [message #267651 is a reply to message #267621] Fri, 14 September 2007 02:03 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Ross,

Thanks for your response.

The reason for sorting it before insertion is i need to generate the sequence(unique id) based on the date on which the record has been inserted into the database.here is the example

c1 c2
a 09/10/2007
b 10/09/2006
c 09/09/1999

I need to insert records by generating a sequence which is as follows

id c1 c2
1 a 09/09/1999
2 b 10/09/2006
3 c 09/10/2007

so in order to get this i am sorting the result set first and then associating a sequence with it.

Let me know if i can achieve this in a better manner.

Thanks for all the help.

Re: Urgent help needed in tuning the query [message #267653 is a reply to message #267594] Fri, 14 September 2007 02:09 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
Rhetorical question - How does "timestamp order" differ from "sequence order"?
Therefore if you have timestamps what value added does "sequence" add?
Re: Urgent help needed in tuning the query [message #267657 is a reply to message #267601] Fri, 14 September 2007 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I apologize for the wordings.Will see that i wont happen again.

Can we trust you? We already told you that and about formatting and you already apologized but see what you post now.

Regards
Michel
Re: Urgent help needed in tuning the query [message #267659 is a reply to message #267653] Fri, 14 September 2007 02:21 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi anacedent,

Thanks for your response.

Let me put it in this way.

I have total two queries.

one query has following output.

c1 c2
a 09/10/2007
b 10/10/2007
c 10/10/2006

other query has follwing output

c1 c2
e 09/11/2006
d 10/10/2008
f 10/05/2005

so if i use a sequence with a union all here directly ,09/10/2007 will have a sequence less than 09/11/2006 which shouldnt be the case.

In order to avoid this i am sorting these results with date column so that they form a correct order then assigning sequence to them.

Hope it isn't too confusing.

thanks
Re: Urgent help needed in tuning the query [message #267661 is a reply to message #267594] Fri, 14 September 2007 02:30 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>so if i use a sequence with a union all here directly ,09/10/2007 will have a sequence less than 09/11/2006 which shouldnt be the case.

Yes, it is confusing.
You introduced the sequence in previous post.
Regardless, You're On Your Own (YOYO)! I quit this thread.
Re: Urgent help needed in tuning the query [message #267676 is a reply to message #267659] Fri, 14 September 2007 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you still don't format.

One more that just posts fake apologies and despise who want to help him.

Regards
Michel
Re: Urgent help needed in tuning the query [message #267955 is a reply to message #267676] Sun, 16 September 2007 23:10 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
INSERT INTO tall
SELECT c1
,      c2
,      to_number(to_char(c2, 'YYYYMMDDHH24MIDD')) * 1000000 + myseq.nextval AS seq
FROM   t1
UNION ALL
....


Exclude the ORDER BY. SEQ will be in date order.

Ross Leishman

[Updated on: Sun, 16 September 2007 23:11]

Report message to a moderator

Previous Topic: statspack
Next Topic: Help - Will Reducing cardinality using histogram make query run faster?
Goto Forum:
  


Current Time: Mon Dec 05 23:55:29 CST 2016

Total time taken to generate the page: 0.14639 seconds