Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 06 Nov 2015 @ 13:03:00 GMT


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


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

Hi,

Firstly, my apologies for the delayed response, I've been onsite with a customer.

As other people have pointed out, if your query does not include all columns of the USI in the WHERE clause (using equality constraints and all constraints are AND'd together) then the USI cannot be used with direct lookup'. So your understanding is correct. However (again as other people have noted) the optimiser may well do an index scan which is likely to be much faster than a table scan.

Using your example from below, if the USI columns are fname + lname and your query only includes fname in the WHERE clause then you are correct. The optimiser cannot know which AMP holds the index entry. Therefore, this must generate an all-AMP query, but very possibly an all-AMP ** index ** scan and not an all-AMP ** table ** scan.

So the USI can cover a query.

We talked before about using the ORDER BY clause to sort index entries by data value rather than hash of all columns. Note that this option only applies to NUSI's and not to USI's.


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