Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 31 Oct 2015 @ 13:41:15 GMT


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


Subj:   Multiple single-column SI vs Single Multi-column SI
 
From:   Anomy Anom

<-- Anonymously Posted: Saturday, October 31, 2015 09:12 -->

Hi,

Its my understanding that if a secondary index is defined as having a composite set of columns, then for any query to leverage the SI, the entire column-set (that comprise the index) needs to be specified in the WHERE clause. If a subset of the column-set is used in the query then the SI is not used and results in a FTS. Consider the example below :

     -------------------------------------------------
     create table employee
     (
         empid integer,
         fname varchar(50),
         lname  varchar(50)
     )
     unique primary index (empid);
     create index (fname, lname) on employee;
     -------------------------------------------------
     select * from employee where fname = 'John';
     -------------------------------------------------

I believe that in the above example the SI will not be used since the query mentions only one of the columns that comprise the SI and will result in a FTS. Is my understanding correct ?

So is it more useful to have multiple single-column SI rather than having a single multi-column SI ?

Please share your thoughts.



     
  <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: 15 Jun 2023