Previously, columns with CLOB or BLOB data type were not allowed in any set operation query, because hashing on a CLOB or BLOB column is impossible. UNION ALL need not 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:
SELECT b1 FROM t_clob1 UNION ALL SELECT b2 FROM t_clob2;
Result:
*** Query completed. 8 rows found.