|
Archives of the TeradataForumMessage Posted: Sat, 31 Oct 2015 @ 13:41:15 GMT
<-- 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.
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||