Archives of the TeradataForum
Message Posted: Wed, 01 Mar 2006 @ 15:00:38 GMT
| Subj: || || Re: Performance problem with View Layering |
| From: || || Geoffrey Rommel |
| ||In any RDBMS using functions on JOIN clause will degrade performance bcos of unnecessary data type conversions.|| |
Srini is right about this. Coalesce() works fine in the select list, but not so well in a join condition.
INNER JOIN DATA_SOURCES_FILTER B
ON COALESCE(TRIM(TGT.DATA_SOURCE_CD),'') =
If we assume that data_source_cd is never = '', we can remove the coalesce by recoding as follows:
ON (TGT.DATA_SOURCE_CD = B.DATA_SOURCE_CD)
or (TGT.DATA_SOURCE_CD IS NULL AND B.DATA_SOURCE_CD IS NULL)
But that reveals another problem. If I'm not mistaken, that will produce a Cartesian product between all rows on TGT and B that have
nulls -- hence the estimate of 200+ million rows.