Previously, columns with CLOB or BLOB data type were not allowed in any set operation query, as it is not possible to hash on a CLOB or BLOB column. UNION ALL does not need to hash on any column (including a LOB), that restriction is removed with the the UNION ALL optimizations feature.
Assume the following tables:
CREATE TABLE t_blob1 (a1 INTEGER, b1 BLOB(2097088000)); CREATE TABLE t_clob2 (a2 INTEGER, b2 CLOB);
Consider the following query:
-- Prior to Teradata Database 16.0: SELECT b1 FROM t_clob1 UNION ALL SELECT b2 FROM t_clob2; *** Failure 5690 LOBs are not allowed to be hashed. Statement# 1, Info = 0 -- Teradata Database 16.0: SELECT b1 FROM t_clob1 UNION ALL SELECT b2 FROM t_clob2; *** Query completed. 8 rows found.