quey tuning

Database
Junior Supporter

quey tuning

Hi ,

I have a query as under :

sel a.*,b.*

from tab1 a inner join tab2

on a.col1=b.col1

and a.col2=b.col2

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

sel a.*,b.*

from tab1 a inner join tab2

on a.col1=b.col1

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 :

sel a.*,b.*

from tab1 a inner join (sel col1,cast(col2 as smallint from tab2) b(col1,col2)

on a.col1=b.col1

and a.col2=b.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 ?

Tags (1)