Restrictions on Sparse Join Index WHERE Clause Predicates - Teradata Database

Restrictions on Sparse Join Index WHERE Clause Predicates

The following restrictions apply to WHERE clause join conditions specified in a join index definition:

Data types for any columns used in a join condition must be drawn from the same domain because neither explicit nor implicit data type conversions are permitted.

Multiple join conditions must be connected using the AND logical operator.

The OR operator is not a valid way to connect multiple join conditions in a join index definition.

You cannot specify independent inequality WHERE clause join conditions in a join index definition.

The following rules apply to the use of inequality join conditions in a join index definition WHERE clause:

Inequality join conditions are supported only if they are ANDed to at least one equality join condition.

Inequality join conditions can be specified only for columns having the same data type in order to enforce domain integrity.

The only valid comparison operators for an inequality join condition are the following:

<

<=

>

>=

The following join index definition is valid because the WHERE clause inequality join condition on o_totalprice and c_acctbal it specifies is ANDed with the previous equality join condition on o_custkey and c_custkey:

     CREATE JOIN INDEX ord_cust_idx AS

      SELECT c_name, o_orderkey, o_orderdate

      FROM orders, customer

      WHERE o_custkey = c_custkey

      AND   o_totalprice > c_acctbal;

The following join index definition is not valid because the WHERE clause inequality join condition has no logical AND relationship with an equality join condition:

     CREATE JOIN INDEX ord_cust_idx AS

      SELECT c_name, o_orderkey, o_orderdate

      FROM orders, customer

      WHERE o_totalprice > c_acctbal;