Functions in Join Conditions

Database
Enthusiast

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 !!

4 REPLIES
Enthusiast

Re: Functions in Join Conditions

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

Senior Apprentice

Re: Functions in Join Conditions

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.

Enthusiast

Re: Functions in Join Conditions

I assume that performance is the same in both cases. 

Roland Wenzlofsky
Enthusiast

Re: Functions in Join Conditions

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