Archives of the TeradataForum

Message Posted: Wed, 22 Jan 2004 @ 00:45:02 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

CREATE JOIN INDEX DB.T1_JI AS
SELECT FIELD_1,
             FIELD_2,
             SUM(COST) (NAMED AGR_COST)
FROM DB.T1
WHERE PA_DAT > '2002-12-31'
GROUP BY 1,2
PRIMARY INDEX (FIELD_2);

Following Carrie's suggestion, I'd like to add a few remarks on the definition of the PI itself. Please note that this is a single-table join, which is a special case and some additional restrictions apply.

I quote from the "SQL Ref vol.4" on the search conditions in PI with my comments between the quotes:

1. "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."

In your condition there is an implicit conversion to DATE, which we see back in the explain plan.

2. "You can only specify a join condition using an inequality operator if you specify multiple join conditions and use the AND logical operator to connect the inequality join condition or conditions with an equality join condition"

It looks like this rule is violated.

3. "Do not specify a WHERE clause for a single-table join index."

Oops ...

All in all, it looks like the definition of the JI needs to be revisited before it can be considered by the optimizer.

Regards,

Victor

	<Prev	Next>		<<First	<Prev	Next>	Last>>