Understanding Sybase IQ handles join column data types can help you write more efficient queries.
Join columns require like data types for optimal performance. Sybase IQ allows you to make an ad hoc join on any data types for which an implicit conversion exists. Unless join column data types are identical, however, performance can suffer to varying degrees, depending on the data types and the size of the tables. For example, while you can join an INT to a BIGINT column, this join prevents certain types of optimizations. The Sybase IQ index advisor can identify mis-matched join data types that can impact performance in cases like this.
Join keys with smaller data types tend to offer better performance than keys with wider data types; join keys with integer data types tend to be faster than numeric or character data types.
Although these data types may offer better performance, choosing keys with matching data types usually provides more efficient joints that choosing keys with ‘fast’ data types that do not match. If the data types are not the same, Sybase IQ must internally convert one of the data types to make the columns comparable, which can decrease performance.
For tables of implicit data type conversions, see “Moving Data In and Out of Databases” in System Administration Guide: Volume 1.