Archives of the TeradataForum
Message Posted: Sat, 31 Oct 2015 @ 15:50:17 GMT
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?
Ward Analytics Ltd - Information in motion (www.ward-analytics.com)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|