Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Wed, 13 Jun 2002 @ 00:59:37 GMT


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


Subj:   Re: Full-Table Scans and Secondary Indexes
 
From:   Todd A. Walter

David's note is right about spooling rowids from a NUSI in order to more efficiently access base table rows when a lot of rows are expected to be qualified via the index.

Mike's note is right for older releases but this area has changed a lot in R4.0 and 4.1.

Single table join indexes can be defined with an index portion(including hashing differently than the underlying table) and a data portion creating a "covered index". This form of index can fully satisfy a query if the query requires only columns that are included in the index. It can fully satisfy a single table query or it can satisfy the retrieval for joins,... in which case it can be used directly if the hashed fields match the join fields or it can be spooled, redistributed,... . Basically it is just another table the optimizer considers in planning. As we implemented that, we extended the number of cases in which a NUSI can be used directly for satisfying queries or joins. Of course, NUSIs typically have fewer fields and are therefore less likely to be able to satisfy a large set of queries but it can and will be used as a table also. USI will also be used the same way.



     
  <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: 28 Jun 2020