Type Casting in Join...

Database
l_k
Enthusiast

Type Casting in Join...

Hi All,

I have seen in most of the SQLs having Type cast conversion from varchar to interger and date to integer while processing from staging to next layer..

Code looks like:

from
stg_party
Left join sys_calendar.calendar
on cast(khrdt as integer)=cast(calendar_date as integer)

This impact CPU is in 4 digits everyday for retrieving the data from the table..

I am expecting a suggestion to avoid this type of data type conversion in the Joins and how optimizer will take this query for processing and overhead of this type of query?

Data is coming from flat file to staging table using Mload.In staging table all columns are VARCHAR data type..

Alternative approach is highly appreciated.

Thanks.
2 REPLIES
Enthusiast

Re: Type Casting in Join...

a general rule of thumb is to avoid CAST,COALESCE and CONCATENATION in joins. Optimizer ends up doing internal casting which is time and resource consuming work.
l_k
Enthusiast

Re: Type Casting in Join...

Thanks karam...

Data is coming from flat file to staging table using Mload.In the staging table, all columns are VARCHAR data type..While processing from staging to XV ,cast conversion is happening for the most of the SQLs.

If it's being used in SQls..then what would be the best approach to handle this type of things to increase the performance?