Home » RDBMS Server » Performance Tuning » Substr,LTrim,Rtrim making query run slow (Oracle 10g)
Substr,LTrim,Rtrim making query run slow [message #377507] Tue, 23 December 2008 05:06 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have lots of queries where SUBSTR with TO_CHAR and trimming is used. And due to these functions the query is takng longer time to run. Please advice something on this as how can this be made faster.

update REQUEST SET REQUEST.INVOICE=NULL where REQUEST.ACCT_NUM LIKE '3%'  AND REQUEST.COMMAND <> 'POST_AUTH' AND SUBSTRING(LTRIM(RTRIM(To_CHAR(INTRN_SEQ_NUM))),1,6) = LTRIM(RTRIM(REQUEST.INVOICE))  


Regards,
Mahi
Re: Substr,LTrim,Rtrim making query run slow [message #377520 is a reply to message #377507] Tue, 23 December 2008 05:33 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This page might help http://www.orafaq.com/tuningguide/function%20based.html

Ross Leishman
Re: Substr,LTrim,Rtrim making query run slow [message #377527 is a reply to message #377520] Tue, 23 December 2008 05:49 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I am afraid but the link given doesn't help much with TRIM and SUBSTR as there is already like 'xx%' used in the query.
Re: Substr,LTrim,Rtrim making query run slow [message #377555 is a reply to message #377507] Tue, 23 December 2008 08:17 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Look for FUNCTION BASED index feature.

HTH.


Previous Topic: Full table scan when one type of records
Next Topic: SQL Tuning
Goto Forum:
  


Current Time: Thu Dec 05 16:27:12 CST 2024