Archives of the TeradataForum

Message Posted: Thu, 06 Dec 2007 @ 18:47:21 GMT

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

Kamin,

In your previous email you said: "I have a query which is taking over 5 minutes. I have written 2 join indexes which I think would cover the query. When I look at the explain plan it just picks up one of the 2 join indices. When I drop one of them, it picks up the other one."

Only under very limited situations will the optimizer choose to use two join indexes for a query. Actually, for these types of decisions, the optimizer considers each query block within the query as though it were a separate query. A query block is, for example, a derived table or a non-correlated subquery.

After picking the best join index (usually the one that provides the most coverage of the query), the optimizer may chose to also use up to two additional single table join indexes as a replacement for remaining tables in the query (or query block).

If your hope is to use two multi-table join indexes in the same query (block), then unfortunately, that is not supported.

The optimizer considers the use of a JI for each query block independently.

Thanks, -Carrie

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