The datatype of a.col1 and b.col1 matches. But the datatype of a.col2 and b.col2 doesn't match. From performance point of view , they should match. If not then it might lead to product join , even the stats on these columns may not be used.
To overcome this, i need to make their datatypes same. One option is to use cast i.e
from tab1 a inner join tab2
and a.col2=cast(b.col2 as smallint)
note: a.col2 is smallint and v.col2 is char
. another option is to use a derived table and change the datatype there :
from tab1 a inner join (sel col1,cast(col2 as smallint from tab2) b(col1,col2)
is there any difference between these two ? If i use a cast in the join operation, then would it not use the stats on that column or will not use the index (col1 and col2 are PI in bothe tables a and b and have stats). As i understad, if you do any operation on a column in a join, eg substring etc, then the affect of PI and stats is lost. then does it apply for cast as well ?