All Forums Database
samit_9999 37 posts Joined 03/06
24 Apr 2006
Substr causing problem-killing query performance

Hi,I need to use Substr as part of my join condition.However its killing the performance of the query significantly.Is there any way i can improve performance of the query?SELECT s.col1FROM ABC sINNER JOIN XYZ tON substr(col3,10,2) = t.cols1AND substr(col3,15,2) = t.cols2Thanks,Sam

Sunil Agrawal 12 posts Joined 07/04
02 May 2006

You might want to define col3 as value order NUSI for improving the performance.But, it is a good practice to keep the substring data substr(col3,10,2) and substr(col3,15,2) as separate columns in source table (ABC here). Because you will be doing this substr operation once but you will be accessing these columns multiple time like in where clause here.For example, you might want to keep Area Code column separately from phone number columns. When you require to access area code, you can directly access this column without substring it every time.Hope this helps.

DGiabbai 47 posts Joined 07/04
02 May 2006

LDM's first normal form requires "atomicity" of each field... as stated by Sunil.

You must sign in to leave a comment.