15.00 - Restrictions on Built-In Functions and Join Index Definitions - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Restrictions on Built-In Functions and Join Index Definitions

When you create a join index that specifies a built-in, or system, function in its WHERE clause (see SQL Functions, Operators, Expressions, and Predicates for more information about built‑in functions), the system 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.

As a result, the Optimizer does not use a join index in the access plan for a query that qualifies its WHERE clause with the same built-in function used to define that join index because it cannot determine whether the index covers the query or not. The Optimizer does 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 exception to this is the CURRENT_TIME and CURRENT_TIMESTAMP functions, which are resolved for a query. The resolved value is used to check if the query can be covered by a join index.)

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

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

On January 7, 2010, you perform the following SELECT statement:

     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 ‘2010-01-04’, not the current system date ‘2010-01-07’.

Note that if you had defined curr_date with a predicate of order_date >= CURRENT_DATE instead of order_date = CURRENT_DATE, then the Optimizer could use curr_date to cover the query if it were the least costly way to process the request.

On the other hand, if you were to perform the following SELECT statement on January 7, 2010, or any other date, retaining the original curr_date predicate, 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:

     FROM orders
     WHERE order_date = DATE ‘2010-01-04’;