Archives of the TeradataForum

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

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

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