I have one update statement as below:
SET EFFECTIVE_END_DATE = ?CURR_BUS_DATE?,
DW_UPD_LOAD_TIMESTAMP = Current_timestamp(6)
WHERE ~DATABASE_NAME_BASE~.~TABLE_NAME~.ACCM = ~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~.ACCUM
(COALESCE(~DATABASE_NAME_BASE~.~TABLE_NAME~.TYPE_ID,0) <> COALESCE(~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~._TYPE_ID,0)
OR COALESCE(~DATABASE_NAME_BASE~.~TABLE_NAME~.DINTEREST_RATE,0) <> COALESCE(~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~.DINTEREST_RATE,0)
OR ~COALESCE(DATABASE_NAME_BASE~.~TABLE_NAME~.CINTEREST_RATE,0) <> COALESCE(~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~.CINTEREST_RATE,0)
~DATABASE_NAME_BASE~.~TABLE_NAME~.EFFECTIVE_END_DATE = ?HIGH_END_DATE? ;
I have to put coalesce statements because i am expexting Nulls in those columns and we can't compare NULL to a NULL.
But coalesce is high cpu consuming function and i am scared it might throw performance issues.
Same is the case with my inserts.
Do i have any alternative to these?
COALESCE doesn't use much CPU, but it might change the optimizer's plan.
I don't know if rewriting the logic will help:
OR ~DATABASE_NAME_BASE~.~TABLE_NAME~.TYPE_ID <> ~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~._TYPE_ID
OR NOT (~DATABASE_NAME_BASE~.~TABLE_NAME~.TYPE_ID IS NULL AND ~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~._TYPE_ID IS NULL)
If ACCM is the PI of the target table and EFFECTIVE_END_DATE is not a partitioning column a MERGE will provide the most efficient plan.
Thank you for your reply. I had this notion and read at numerous places that Coalesce is troublesome to use, mostly in the join conditions.
Thanks for clarifying that.
Will check with merge once and compare the performance.
COALESCE in joins might change the plan, if it's in a join on the PI-columns when NULLs should be treated equal. In that case there's a recommendation to use ON (t1.col = t2.col OR t1.col IS NULL AND t2.col IS NULL) instead of.