15.00 - Restrictions on Sparse Join Index WHERE Clause Predicates - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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;