Restrictions on Built-In Functions and Join Index Definitions - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

When you create a join index that specifies a built-in (system) function in its WHERE clause (see Built-In Functions ), the system resolves the function at the time the join index is created and stores the result as part of the index definition rather than evaluating it dynamically at the time the Optimizer may use the index to build a query plan.

Therefore, 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 the Optimizer cannot determine whether the index covers the query. 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'.

If you define curr_date with a predicate of order_date >= CURRENT_DATE instead of order_date = CURRENT_DATE, the Optimizer can use curr_date to cover the query if it is the least costly way to process the request.

However, if you perform the following SELECT statement on January 7, 2010 (or any date retaining the original curr_date predicate), the Optimizer uses join index curr_date for the query plan, because the statement explicitly specifies the date that was stored with the join index definition when it was created:

     SELECT *
     FROM orders
     WHERE order_date = DATE '2010-01-0';