Join Indexes, Expressions, and Built-In Functions - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

When you create a join index that specifies a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP built-in function in its partitioning expression, the default is for Teradata Database to resolve the function at the time the join index is created and then to store the result as part of the index definition rather than evaluating it dynamically at the time the Optimizer would use the index to build a query plan. Note that you cannot reconcile a CURRENT_TIME or TIME function that is specified as part of the partitioning expression for a join index using ALTER TABLE TO CURRENT requests.

This need not be the case, however, because you can reconcile the value of any DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function specified as part of a partitioning expression using the ALTER TABLE TO CURRENT statement (see ALTER TABLE TO CURRENT). A DateTime expression used in this way in a partitioning expression is referred to as an updatable date or updatable timestamp.

When you create a join index that specifies a built-in, or system, function in its WHERE clause, Teradata Database resolves the function at the time the join index is created and then stores the result as part of the index definition rather than evaluating it dynamically at the time the Optimizer would use the index to build a query plan. For more information about built-in functions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

You can use ALTER TABLE TO CURRENT requests to reconcile DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions that are specified in the WHERE clause of a join index definition. The Optimizer use the join index if the query specifies an explicit value in its WHERE clause that matches the resolved value stored in the index definition.

The following built-in functions are relevant to this issue:
  • ACCOUNT
  • DATABASE
  • PROFILE
  • ROLE
  • SESSION
  • TIME
  • USER

For example, suppose you decide to define a join index using the CURRENT_DATE built-in function on January 4, 2007 as follows:

    CREATE JOIN INDEX curr_date AS
      SELECT *
      FROM orders
      WHERE order_date >= CURRENT_DATE;

On January 7, 2007, you perform the following SELECT request:

    SELECT *
    FROM orders
    WHERE order_date >= CURRENT_DATE;

When you EXPLAIN this query, you find that the Optimizer does not use join index curr_date because the date stored in the index definition is the explicit value ‘2007-01-04’, not the current system date ‘2007-01-07’.

On the other hand, if you were to perform the following SELECT request on January 7, 2007, or any other date, the Optimizer does use join index curr_date for the query plan because the statement explicitly specifies the same date that was stored with the join index definition when it was created:

    SELECT *
    FROM orders
    WHERE order_date = DATE '2007-01-04';

You can define join indexes, including aggregate join indexes, on Period and UDT columns; however, you cannot collect statistics on UDT columns. You can also define join indexes that specify other non-aggregate, non-OLAP, and non-UDF expressions in the select list, as well as single-table conditions in the WHERE and ON clauses of the index definition. This includes allowing a join index to be defined using a method that is associated with a UDT column.

The following set of rules defines the restrictions and limitations of specifying expressions and columns with Period and UDT data types in join index definitions.
  • Any expression that you specify in the select list of a join index definition must be defined on at least one column.

    You cannot specify a constant expression in the select list.

    For example, the following request is not valid because it specifies a constant CASE expression in its select list.

         CREATE JOIN INDEX test AS
          SELECT a1, CASE WHEN 1=1
                          THEN 1
                          ELSE 0
                     END AS c
          FROM t1;
  • An expression specified in the select list of a join index definition can reference a set of columns from a single table or from multiple tables.

    Note that Teradata Database does not treat a WHERE clause condition that is composed of a multicolumn expression as a join condition.

    If you are creating a multitable join index, the tables must be joined by an equality condition on a column from each table. Otherwise, Teradata Database returns an error to the requestor.

    For example, the following request is not valid because only satisfiable conditions that have constant or inequality conditions ANDed to at least one equality join between columns from different tables of the same type are valid in the WHERE clause.

         CREATE JOIN INDEX test AS
          SELECT *
          FROM t1,t2
          WHERE a1+b1=a2;
  • An expression specified in the select list of a join index definition must be aliased.

    For example, the following request is not valid because the expression BEGIN(d1) is not aliased.

         CREATE JOIN INDEX test AS
          SELECT a1, BEGIN(d1)
          FROM t1;
  • An expression defined in the select list of an aggregate join index definition must also be specified as part of the GROUP BY clause.

    For example, the following request is not valid because it does not specify a GROUP BY clause that includes the aggregate expression SUM(c1) AS s.

         CREATE JOIN INDEX test AS
          SELECT (a1+b1) AS a, SUM(c1) AS s
          FROM t1;
  • You can define either the primary or secondary for a join index on a UDT column. For example, assume that column t1.d1 has a UDT data type.

    The following CREATE JOIN INDEX request is valid because its primary index is defined on column t1.d1.

         CREATE JOIN INDEX test AS
          SELECT d1, a1
          FROM t1
          PRIMARY INDEX (d1);
  • You can define a compressed join index that specifies a UDT column.

    For example, assume that column t1.d1 has a UDT data type. The following CREATE JOIN INDEX request is valid.

         CREATE JOIN INDEX test AS
          SELECT (b1), (c1,d1)
          FROM t1;

See “CREATE JOIN INDEX” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for examples of how to specify UDT and Period data type columns, BEGIN expressions, and P_INTERSECT expressions correctly in the select list of a join index definition.