OraFAQ Forum: Performance Tuning » Substr,LTrim,Rtrim making query run slow

Home » RDBMS Server » Performance Tuning » Substr,LTrim,Rtrim making query run slow (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Substr,LTrim,Rtrim making query run slow [message #377507]

Tue, 23 December 2008 05:06

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

Report message to a moderator

Re: Substr,LTrim,Rtrim making query run slow [message #377520 is a reply to message #377507]

Tue, 23 December 2008 05:33

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

Report message to a moderator

Re: Substr,LTrim,Rtrim making query run slow [message #377527 is a reply to message #377520]

Tue, 23 December 2008 05:49

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.

Report message to a moderator

Re: Substr,LTrim,Rtrim making query run slow [message #377555 is a reply to message #377507]

Tue, 23 December 2008 08:17

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Look for FUNCTION BASED index feature.

HTH.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 18:24:35 CST 2024