Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 01 Nov 2015 @ 00:19:59 GMT


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


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

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

Thanks everyone for the response.

Hi Dave,

First I want to get my basics right. Please help me understand what the index sub-table would contain for the record [ 1001, 'John', 'Smith' ].

     -------------------------------------------------
     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 the sub-table will store -

a) the hash-value of the combination of (fname + lname)

b) the actual values of fname and lname

c) and finally the row-id(s) of the base table record(s).


I think since the hash-value is generated based on the combination of fname + lname columns, using only fname in the query will result in a different hash-value and hence can't be looked up in the sub-table. Hence the SI will not be utilized in this case. Please correct me if my rationale is correct.

Also could you please explain how covering indexes work with the help of an example with the sub-table ?

Thank you for your help.



     
  <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