Restriction on Partial Coverage of Outer Join Queries by Row Compressed Join Indexes
Row compressed join indexes (see “Row Compression of Join Indexes” on page 380 and “Physical Join Index Row Compression” on page 381) cannot be used to partially cover an outer join query block. This is true for both single‑table and multitable join indexes.
For example, suppose you have the following join index definition:
CREATE JOIN INDEX no_outerj_cov AS
SELECT (custkey), (orderkey, orderstatus, o_comment)
FROM cust LEFT OUTER JOIN ordertbl ON custkey=o_custkey;
The Optimizer does not use this join index to partially cover the following request because after it rewrites the request, the query block that would use the row compressed join index would need to participate in an outer join operation, and Teradata Database does not support reading row compressed join index rows in outer join steps.
SELECT custkey, orderkey, linenumber, price
LEFT OUTER JOIN ordertbl ON custkey=o_custkey
LEFT OUTER JOIN lineitem ON orderkey=l_orderkey;
This restriction applies to any query block, including spooled subqueries and derived tables.