15.00 - Restriction on Partial Coverage of Outer Join Queries by Row Compressed Join Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

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
    FROM cust 
    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.