Home » RDBMS Server » Performance Tuning » better way to write or tune a query with joins
icon7.gif  better way to write or tune a query with joins [message #388377] Tue, 24 February 2009 08:51 Go to next message
abs_amit
Messages: 59
Registered: January 2006
Location: delhi
Member
Hi all,

Below query is running for infinite time. is there any better way to write this query ?

Tables size is 10 to 12 GB each. and biggest table GLOBAL_KEYS.SK_INQUIRY_LN has 1.2 Bln records.

Detailed SQL statement and Execution plan is in the attached file.
Re: better way to write or tune a query with joins [message #388398 is a reply to message #388377] Tue, 24 February 2009 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results
Re: better way to write or tune a query with joins [message #388576 is a reply to message #388377] Wed, 25 February 2009 08:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It isn't running for infinite time.
How long have you let it run for?

Suggestions:
1) Loose the hints - there's no way on earth this query wants to be using any more nested loops than it has to.
2) Check your stats are up to date.
3) Increase the Sort area size and Hash area size
4) If the number of rows where mod(SAVEDCART_LINE_INC.SAVEDCART_LINE_ID, 10) = 0 is true is small, consider creating an index on that function. I strongly suspect that about 10% of the values will match it, meaning it's almost certainly not worth indexing, and that you're just using that to try to get a sample of the data, in which case you might want to look at the SAMPLE clause.
Re: better way to write or tune a query with joins [message #388956 is a reply to message #388576] Thu, 26 February 2009 14:59 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's all pretty good advice - especially about the hints and hash area. I'm not totally sold on the benefits of the index. I reckon 10% of the data will at best break-even with an index vs. full table scan.

If you are joining very big tables, it pays to do partition-wise joins - see this article for details.

Ross Leishman
Previous Topic: unable to reduce the cost of the query
Next Topic: Tuning analytical function query
Goto Forum:
  


Current Time: Fri Dec 09 03:58:43 CST 2016

Total time taken to generate the page: 0.23494 seconds