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

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019