Archives of the TeradataForum

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

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

<-- 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>>