Home » SQL & PL/SQL » SQL & PL/SQL » SQL query tunning
SQL query tunning [message #239486] Tue, 22 May 2007 08:47 Go to next message
manjiri
Messages: 6
Registered: May 2007
Location: Mumbai
Junior Member
Dear All,

Unable to tune attached two queries. Eventhough query is written properly it takes lot of time for execution. Query includes function also. This query is used for generating report in crystal report.

Please suggest how do I tune this query.
Re: SQL query tunning [message #239549 is a reply to message #239486] Tue, 22 May 2007 11:06 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
post the Explain plan of query
Re: SQL query tunning [message #239758 is a reply to message #239549] Wed, 23 May 2007 04:30 Go to previous messageGo to next message
manjiri
Messages: 6
Registered: May 2007
Location: Mumbai
Junior Member
Please find explain plan for first query.
Re: SQL query tunning [message #239760 is a reply to message #239758] Wed, 23 May 2007 04:33 Go to previous messageGo to next message
manjiri
Messages: 6
Registered: May 2007
Location: Mumbai
Junior Member
Please find explain plan for second query.
Re: SQL query tunning [message #239799 is a reply to message #239486] Wed, 23 May 2007 06:04 Go to previous messageGo to next message
vsc021077
Messages: 2
Registered: May 2007
Junior Member
Here tables WMS_CLIENT_INVESTMENT, WMS_HOLDINGS and WMS_SECURITY are showing FTS.
Can you post the indexes created on these tables if any?
Re: SQL query tunning [message #239887 is a reply to message #239799] Wed, 23 May 2007 09:54 Go to previous messageGo to next message
manjiri
Messages: 6
Registered: May 2007
Location: Mumbai
Junior Member
There is no index created on these tables.
Re: SQL query tunning [message #239906 is a reply to message #239887] Wed, 23 May 2007 10:57 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
no indexes????
Re: SQL query tunning [message #239925 is a reply to message #239906] Wed, 23 May 2007 12:15 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
The FTS's are kind of coherent with the hash joins. You might want to try to hint it into using nested loops.
[Edit] Oops, forgot that there are no indexes, nested loops won't do you any good. Forget I mentioned this. Maybe without indexes this is as fast as it gets (or add more hardware...).
[end edit]

Just out of curiosity: what does the following where clause do:
        AND    h.holdings_ason_date BETWEEN
               to_date('01/01/1900'
                       ,'dd/mm/yyyy') AND
               to_date('12/07/2006'
                      ,'dd/mm/yyyy')

Is this to keep any records BC out of the resultset? How many of those do you have?

And this one is even more intriguing:
WHERE  h.holdings_to_date >= to_date('01/12/2099'
                                    ,'DD/MM/YYYY')

Either this is a mistake or there is some dummy value involved...

[Updated on: Wed, 23 May 2007 12:17]

Report message to a moderator

Previous Topic: Left join problem
Next Topic: multiple values in a string causing stored procedure to hang even after commiting the data
Goto Forum:
  


Current Time: Sat Dec 03 03:52:14 CST 2016

Total time taken to generate the page: 0.11457 seconds