Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Multiple single-column SI vs Single Multi-column SI
From:   Dave Wellman


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)

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020