Datatype mismatch in Joining Columns

Database
Enthusiast

Datatype mismatch in Joining Columns

Hello all,

Is there any performance issue if I use columns to join and both the column's datatypes are not same.

For ex. Joining float column with Integer column.

4 REPLIES
Enthusiast

Re: Datatype mismatch in Joining Columns

Yes, There will be performance issue because of implicity datatype conversion.

Enthusiast

Re: Datatype mismatch in Joining Columns

Thanks Hariprasath for replying..

Could you please elaborate on this ?

Supporter

Re: Datatype mismatch in Joining Columns

The internal representation of float and integer is different. 

So a plain bit comparison - and this is how it always end up - would be always false even if the values stored are the same. Therefore TD will convert one of the columns to the other format to be able to check if the values stored are the same. You should see this in the explain as well.

This is an additional calculation which need resources and therefore might impact query performance. 

This will be espacially true if both columns are PIs on the two tables which are joined. If both PIs would have the same data type (data type class) a join would not need a redistribution and a merge join could be done. But if the data types are different at least one table would need to be redistributed after the implicit conversion.

Enthusiast

Re: Datatype mismatch in Joining Columns

Thank you for explaination.

It really helps.. :)