Restrictions on Sparse Join Index WHERE Clause Predicates - Teradata Vantage

The OR operator is not a valid way to connect multiple 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 ANDed to at least one equality join condition.
• Inequality join conditions can be specified only for columns having the same data type 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 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;