The Optimizer analyzes all queries for the use of an aggregate join index. In determining if an aggregate join index can be used to process a query, the Optimizer applies the following rules:
- An aggregate join index cannot be used to substitute for the base tables of a non-aggregate query.
- Like a simple join index, an aggregate join index qualifies for inclusion in the plan if it specifies either the same joins or a subset of the joins of the query.
- If an aggregate join index specifies a subset of joins of the query, then all the joined fields of the remaining join conditions on the tables of the aggregate join index must be specified in the GROUP BY clause and in the select list of the aggregate join index definition.
- Even if an aggregate join index specifies the same joins as the query, it does not qualify if its GROUP BY columns are neither the same nor a superset of the GROUP BY columns for the query.
- An aggregate join index must contain all the columns needed by the query for the tables that it replaces. When trying to match expressions from a query to the select list for a join index, the Optimizer applies the following rules:
- Only the SUM, COUNT, MIN, and MAX aggregate functions are valid for an aggregate join index.
- Addition and multiplication are commutative.
- Teradata Database implicitly adds a COUNT(*) or COUNT(non_null_expression) term to the definition of an aggregate join index if neither term is not explicitly defined.
- Teradata Database uses COUNT(*) and COUNT(non-null_expression) interchangeably.
- An aggregate expression must be assigned an alias.
- You cannot base the primary index of an aggregate join index on the alias of an aggregate expression.
- You cannot specify a DISTINCT operator to qualify the operand of an aggregate expression. For example, you cannot specify MIN(DISTINCT column_expression) in the definition of an aggregate join index.
- A numeric expression from the join index that is converted to FLOAT can be used to match any expression of the same type defined with or without the conversion from the query.
- An AVERAGE aggregate expression in a query is converted to a SUM/COUNT expression when an aggregate join index substitutes for it.
- A SUM(0) expression in a query is converted to a constant having value 0.
- A SUM(constant) expression in a query matches with constant * COUNT(*) in a aggregate join index definition.
- The SUM or COUNT function from SUM(CASE expression) or COUNT(CASE expression) can be pushed to all the resulting expressions of the CASE expression to find a match. For example, the following expression can be converted to the expression following it to find a match.
SUM(CASE WHEN x1=1 THEN 1 ELSE 0) CASE WHEN x1=1 THEN SUM(1) ELSE SUM(0)
- Conditions are converted according to the following conversion table.
THIS condition … IS converted to this equivalent expression … expression ≥ ‘YYYY0101’ ‘EXTRACT(YEAR FROM expression) ≥ YYYY’ expression > ‘YYYY1231’ ‘EXTRACT(YEAR FROM expression) > YYYY’ expression < ‘YYYY0101’ ‘EXTRACT(YEAR FROM expression) < YYYY’ expression ≤ ‘YYYY1231’ ‘EXTRACT(YEAR FROM expression) ≤ YYYY’ expression ≥ ‘YYYYMM01’ ‘EXTRACT(YEAR FROM expression) > YYYY’ or
‘(EXTRACT(YEAR FROM expression) = YYYY
AND
EXTRACT(MONTH FROM expression) ≥ MM)’
expression > ‘YYYYMMLD’ where LD is the last day of the month specified by MM
‘EXTRACT(YEAR FROM expression) > YYYY’ or
‘(EXTRACT(YEAR FROM expression) = YYYY
AND
EXTRACT(MONTH FROM expression) > MM)’
expression ≤ ‘YYYYMMLD’ where LD is the last day of the month specified by MM
‘EXTRACT(YEAR FROM expression) < YYYY’ or
‘(EXTRACT(YEAR FROM expression) =YYYY
AND
EXTRACT(MONTH FROM expression) ≤ MM)’
expression < ‘YYYYMM01’ ‘EXTRACT(YEAR FROM expression) < YYYY’ or
‘(EXTRACT(YEAR FROM expression) = YYYY
AND
EXTRACT(MONTH FROM expression) < MM)’