All Forums Database
varanasianup 14 posts Joined 09/14
10 Feb 2015
Functions in Join Conditions

Hi All
 

SELECT 

TRIM(UPPER(ABC.HELLO) as HELLO1

FROM ABC

JOIN DEF

HELLO1=DEF.HELLO

 

SELECT 

ABC.HELLO

FROM ABC

JOIN DEF

TRIM(UPPER(ABC.HELLO))=DEF.HELLO

 

Will there be any difference in performance ??

 

Thanks in Advance !!

 

VandeBergB 182 posts Joined 09/06
11 Feb 2015

You're forcing spool either way, check the explain plan for each query.  I think they're both going to be rather ugly.

Some drink from the fountain of knowledge, others just gargle.

dnoeth 4628 posts Joined 11/04
11 Feb 2015

If ABC.HELLO is the PI then TRIM is worst case, disabling local joins.
Are there actually leading blanks? For trailing blanks you don't need TRIM as they are simply ignored during comparison.
If you do UPPER to get a case insensitive join (ANSI session or defined as CASESEPIFIC?) you better add "NOT CASESPECIFIC" on both sides and then remove it.

Dieter

oshun 43 posts Joined 07/10
16 Feb 2015

I assume that performance is the same in both cases. 

visit my private blog at http://www.dwhpro.com

eejimkos 73 posts Joined 01/12
19 Feb 2015

Hi,
I hope that you understund that these are two different queries.This is mainly to the extra function on select statement. There is no logic to compare two diferrent Results Sets , but anyway ...
You may return the same number of rows but still you have to apply a diferrent logic on select.
From a simple test that i made (hello column is part of PI), explain is almost the same, except on the query
SELECT
ABC.HELLO
FROM ABC
JOIN DEF
TRIM(UPPER(ABC.HELLO))=DEF.HELLO

where it doubles the spool space on the last steps . You will see a huge performance if the number of returning rows is really big.
Almost the same if the join is on the pi.
Ending , you have to check the dbqlog in order to be 100% sure .
From my example (ROWS, RESULT SET 491.144 ) , WITH THE COLUMN AS PI
IO : 6,615.00 CPU: 1.52 CPU_SKEW : 35.34 IO_SKEW : 12.27 SPOOL : 33,311,232.00
IO : 7,244.00 CPU: 1.37 CPU_SKEW : 41.55 IO_SKEW : 9.50 SPOOL : 39,391,744.00
The second row is the query
SELECT
ABC.HELLO
FROM ABC
JOIN DEF
TRIM(UPPER(ABC.HELLO))=DEF.HELLO
 
But , this might be an issue of the spool of the result set(less length)

Thanks
 

You must sign in to leave a comment.