Home » RDBMS Server » Performance Tuning » Need help on SQL Query in decode function performance tunning (Oracle 10g)
Need help on SQL Query in decode function performance tunning [message #550230] Sat, 07 April 2012 03:25 Go to next message
kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata
Junior Member
Hi,

I am using 1 sql query where i have to use decode function to compare the null vaues replaced with ? with another table field in the join condition as shown below in bold. When i am using this decode to compare the null values the query is taking long time . Without using this the query was giving output within 5 mins.

Please suggest how to tune this portion of the query , can i replace decode with some other fucntions/alternative to improve the performanxced

PLeasxe suggest.


select col1,col2......
from tab1,tab2......
where conditions,,,,,,
and
decode(tabx.colx,null,'?',tabx.colx) = taby.coly

Report message to a moderator

Re: Need help on SQL Query in decode function performance tunning [message #550231 is a reply to message #550230] Sat, 07 April 2012 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Welcome to the forum.

Can you please read and follow How to use [code] tags and make your code easier to read?

Does taby.coly really contain ? characters?

You're using decode to mimic nvl. So use nvl.

If you want our help with performance you should post the full query along with the explain plan.

Report message to a moderator

Re: Need help on SQL Query in decode function performance tunning [message #550252 is a reply to message #550231] Sat, 07 April 2012 13:18 Go to previous message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
When i am using this decode to compare the null values the query is taking long time . Without using this the query was giving output within 5 mins.


I bet because an index is used in one case and not in the other (nothing with DECOADE function performances).

To complete cookiemonster request: please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
(And use NVL.)

Regards
Michel

Report message to a moderator

Previous Topic: Tuning for slow Oracle query
Next Topic: tuning against count with group by clause
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 26 14:35:08 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.