 better way to write or tune a query with joins [message #388377] |
Tue, 24 February 2009 08:51  |
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 #388576 is a reply to message #388377] |
Wed, 25 February 2009 08:18  |
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
