Archives of the TeradataForum

Message Posted: Sat, 31 Oct 2015 @ 15:50:17 GMT

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

Hi,

In your specific example the SI is not going to be used and you are correct in your understanding of why not, but whether you have a single multi-column SI or multiple single-column SI's is not a simple answer.

The first thing to think about is "what is the reason for defining this SI?".

An SI is usually used for one of two reasons:

#1 - providing fast access to data in a table

#2 - covering (or partially covering) a query

If the SI is for reason #1 then typically you would use multiple single-column SI's. This allows any one of the SI's to be used by the optimiser or even two or more of them in combination. There is however a wrinkle to this which I'll cover later on.

If the Si is defined for reason #2 then often it will contain multiple columns, simply because the query requires multiple columns from the table and a covering query is there to prevent access to the base table.

The wrinkle that I referred to above is using the 'ORDER BY' clause. When using this option you can only 'order by' on a single column, so although the SI may be a multi-column index the optimiser only needs selection on one column in order to consider using the SI (there may be selection on the other columns as well).

Sorry it's not a straight forward answer but I think the above answers your question.

Does that help?

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

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