Home » RDBMS Server » Performance Tuning » Tuning query (insert using select)
icon4.gif  Tuning query (insert using select) [message #271391] Mon, 01 October 2007 08:58 Go to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Hi All,

i am trying to insert some records using select query from other database where its been running for hours here is the example:

insert /+append*/ into insert_table
(
col1,
col2
)
select /+STAR*/
col1,
col2
from src_tbl1@dblink A, src_tbl2@dblink B
where A.col1=b.col2

i am using hints to get performance still in vain.

Oracle Version :9.2.2


Thanks in advance,,
Re: Tuning query (insert using select) [message #271509 is a reply to message #271391] Mon, 01 October 2007 15:43 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
I would load the remote tables into local temp tables first, then join them.

Also, consider creating indexes on the local temp tables before the final join phase.
Re: Tuning query (insert using select) [message #271513 is a reply to message #271509] Mon, 01 October 2007 16:30 Go to previous messageGo to next message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
That is not necessary, you may instruct Oracle to execute the query at the remote DB using the DRIVING_SITE hint.

Regards
Ignacio

[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]

[Updated on: Thu, 11 October 2007 12:41] by Moderator

Report message to a moderator

Re: Tuning query (insert using select) [message #271539 is a reply to message #271391] Mon, 01 October 2007 23:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Your query is syntaxically wrong
2/ Why do you try to use a STAR hint and what do you think it means?

Regards
Michel
Re: Tuning query (insert using select) [message #271665 is a reply to message #271539] Tue, 02 October 2007 07:46 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
My meaning of using STAR hint is just to give chance to the optimiser to execute the heavy joins last,, i am not that much familier with the HINT's..

Please suggest any better way of using hints..

Thanks in advance
Re: Tuning query (insert using select) [message #271668 is a reply to message #271665] Tue, 02 October 2007 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ STAR hint does not exist
2/ "using STAR hint is just to give chance to the optimiser to execute the heavy joins last", there is only 1 join in your query which one can be first or last but this one?
3/ "i am not that much familier with the HINT's" So why are you using them? It is far better to not use them in this case.

First read How to Identify Performance Problem and Bottleneck , especially the third part.

Regards
Michel
Re: Tuning query (insert using select) [message #271673 is a reply to message #271391] Tue, 02 October 2007 08:11 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Thanks for the quick reply just want to make clear to you that

i have total 8 tables out of this two tables has more than 2 cores of records, thats the reason i have opted hints as per the definition and the usage i have particularly selected STAR hint..




Ur's
Maruthi Narra
Re: Tuning query (insert using select) [message #271676 is a reply to message #271673] Tue, 02 October 2007 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what you posted is not what you want to optimize but you want us to help you to optimize something you do not post with the help of something you posted but is not what you want to optimize but it is the sole information you want to give us and which is not what actually is.
Am I clearly understand your point?

Regards
Michel
Re: Tuning query (insert using select) [message #271681 is a reply to message #271391] Tue, 02 October 2007 08:37 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
I have given example on what i am using

Here i am expecting different kind of options to tuning this kind of bottllenecks,

i have problem with only inserting the data not on selecting or while joining my select query has been working in just 1 to 2 min of time while i am using this to insert its just keep on executing for hours so i have opted this group to share with this to gurus to close this problem.

Anyways Thanks for your quick reply next time i will give much information as i can..
Re: Tuning query (insert using select) [message #271745 is a reply to message #271391] Tue, 02 October 2007 14:08 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Here i am expecting different kind of options to tuning this kind of bottllenecks,
I guess I missed it. Exactly which bottleneck is "this kind of bottllenecks"?

Essentially, you have said, "My query is slow. Tell me how to make faster."

Why do you expect a solution, when you've posted nothing of substance?

Please read & follow posting guidelines as found in the #1 STICKY post at the TOP of this forum.
Previous Topic: open cursors
Next Topic: Outer Join killing Performance
Goto Forum:
  


Current Time: Sat Dec 10 11:13:57 CST 2016

Total time taken to generate the page: 0.08771 seconds